info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
SQL Server Database Transaction Log Backups - Activity-based (vs Time-based)
by BF (Principal Consultant; Architecture; Engineering)
2017-08-18
With SQL Server 2017 CTP 2.1, a new DMF has been introduced called sys.dm_db_log_stats. With this DMV we can get the amount of Transaction Log growth since the last Transaction Log backup. This information enables us to only take Transaction Log backups based on activity, and thus when needed. For example, would it be more efficient to take Transaction Log backups after activity/transactions has occurred and log growth of 50MB? Or take Transaction Log backups at every 5min time interval and numerous auto-grow events have occurred and the Transaction Log file having a huge number of VLF's? Activity-based backups has many benefits.
Advantages taking Transaction Log backups based on activity/transactions occurring on a User Databases:
- No unnecessary/wasted Transaction Log backups files created in the file system
- No unnecessary Transaction Log auto-grow events occurring / No unnecessary # of VLF's existing in the Transaction Log File Structure
- No inconsistency of Transaction Log Backup files sizes
You can also setup alerts based on this data made available in sys.dm_db_log_stats.
T-SQL - Take SQL Server Database Transaction Log Backups - Activity-based (default):
Create Procedure dbo.spa_Admin_Database_TransactionLog_Backups
as
--Select * from sys.dm_db_log_stats(5)
--Create Table dbo.Log_Backups (LogID int Identity(1,1), Type varchar(150), Frequency varchar(25), total_vlf_count int, active_vlf_count int, total_log_size_mb decimal(18,1), active_log_size_mb decimal(18,1), log_since_last_log_backup_mb int, inserteddate datetime)
SET NOCOUNT ON
Declare @timebased int = 0
Declare @loggrowththreshold int = 50 --mb
Declare @log_since_last_log_backup_mb int
Declare @frequency varchar(25)
If @timebased = 0 Begin Set @frequency = 'Activity-based' End Else Begin Set @frequency = 'Time-based' End;
Select @timebased, @frequency
While(1=1)
Begin
If @timebased = 0
Begin
--Take Log Backup based on Activity/Transactions
Select @log_since_last_log_backup_mb = log_since_last_log_backup_mb from sys.dm_db_log_stats(5)
--Print @log_since_last_log_backup_mb
If @log_since_last_log_backup_mb > @loggrowththreshold
Begin
Begin Transaction
Insert into dbo.LOG_BACKUPS ([Type], Frequency, total_vlf_count, active_vlf_count, total_log_size_mb, active_log_size_mb, log_since_last_log_backup_mb, inserteddate)
Select 'Pre Log Backup', @frequency, total_vlf_count, active_vlf_count, total_log_size_mb, active_log_size_mb, log_since_last_log_backup_mb, getdate() from sys.dm_db_log_stats(5)
Commit
Declare @filename varchar(100)
Declare @time varchar(100)
Set @time = CONVERT(VARCHAR(8), GETDATE(), 108)
Set @time = REPLACE(@time, ':', '')
Set @filename = 'F:\Backups\test' + CONVERT(VARCHAR(35),getdate(),112) + '_' + @time + '.trn'
Print 'Transaction Log Backup started due too activity-based threshold passed - ' + convert(varchar(100),@log_since_last_log_backup_mb) + 'MB log growth since last log backup. (' + @filename + ')' + char(13)
Backup Log [Test] To Disk = @filename WITH FORMAT
Print char(13)
Begin Transaction
Insert into dbo.LOG_BACKUPS ([Type], Frequency, total_vlf_count, active_vlf_count, total_log_size_mb, active_log_size_mb, log_since_last_log_backup_mb, inserteddate)
Select 'Post Log Backup', @frequency, total_vlf_count, active_vlf_count, total_log_size_mb, active_log_size_mb, log_since_last_log_backup_mb, getdate() from sys.dm_db_log_stats(5)
Commit
WAITFOR DELAY '00:00:01'
--Print 'Loop...'
End
End
If @timebased = 1
Begin --Take Log Backup based on a set Time Interval. T-Log autogrows(# VLF increased due to activity/transactions).
Begin Transaction
Insert into dbo.LOG_BACKUPS ([Type], Frequency, total_vlf_count, active_vlf_count, total_log_size_mb, active_log_size_mb, log_since_last_log_backup_mb, inserteddate)
Select 'Pre Log Backup', @frequency, total_vlf_count, active_vlf_count, total_log_size_mb, active_log_size_mb, log_since_last_log_backup_mb, getdate() from sys.dm_db_log_stats(5)
Commit
Set @time = CONVERT(VARCHAR(8), GETDATE(), 108)
Set @time = REPLACE(@time, ':', '')
Set @filename = 'F:\Backups\test' + CONVERT(VARCHAR(35),getdate(),112) + '_' + @time + '.trn'
Print 'Transaction Log Backup started due too time-based backup. ' + '(' + @filename + ')' + char(13)
Backup Log [Test] To Disk = @filename WITH FORMAT
Begin Transaction
Insert into dbo.LOG_BACKUPS ([Type], Frequency, total_vlf_count, active_vlf_count, total_log_size_mb, active_log_size_mb, log_since_last_log_backup_mb, inserteddate)
Select 'Post Log Backup', @frequency, total_vlf_count, active_vlf_count, total_log_size_mb, active_log_size_mb, log_since_last_log_backup_mb, getdate() from sys.dm_db_log_stats(5)
Commit
WAITFOR DELAY '00:05:00'
End
End
go
In this demo the procedure is taking Transaction Log backups after 50MB of log growth has occurred in the User Database. It keeps consistent log backup sizes and avoids the log having a high number of auto-grow events. It also avoids any log backups being taken when the database has zero transactional activity.
Generate Use Database Transaction Log Activity for demonstration purposes:
CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NOT NULL,
[OrderDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
[OrderNotes] [varchar](1000) NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Create Procedure dbo.spa_Insert
as
/*
While (1=1)
Begin
Exec dbo.spa_Insert
End
*/
Declare @randomstring varchar(1000) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
Set @randomstring = @randomstring + @randomstring + @randomstring + @randomstring + @randomstring
Declare @cnt int = 1000
Begin Transaction
While @cnt > 0
Begin
Insert into Orders (ClientID, OrderDate, ModifiedDate, OrderNotes) Select 7000, Getdate(), GetDate(), @randomstring
Set @cnt = @cnt - 1
End
Commit Transaction
WAITFOR DELAY '00:00:01'
go
Monitor T-SQL #1: Activity-Based
Logging table showing the activity for VLF and log file growth. It logs the before and after Log Backups results from sys.dm_db_log_stats. As stated above, the number of VLF's stays low and the backups are being taken in a consistent, predictable matter. Assuming you don't have huge, long-running transactions in your database, you should see similar consistency as below.
Select getdate() as 'Time', log_since_last_log_backup_mb, active_log_size_mb, total_log_size_mb, log_truncation_holdup_reason, * from sys.dm_db_log_stats(5)
Select * from dbo.LOG_BACKUPS order by logid desc
Monitor T-SQL #2: Return Trx Information
select
d.Name as 'Database',
sessiontrx.session_id as [SPID],
enlist_count as [Active Requests],
activetrx.transaction_id as [Trx ID],
activetrx.name as [Trx Name],
activetrx.transaction_begin_time as [Trx Start Time],
case transaction_type when 1 then 'Read/Write' when 2 then 'Read-Only' when 3 then 'System' when 4 then 'Distributed' else 'Unknown - ' + convert(varchar(20), transaction_type) end as [Transaction Type],
case transaction_state when 0 then 'Uninitialized' when 1 then 'Not Yet Started' when 2 then 'Active' when 3 then 'Ended (Read-Only)' when 4 then 'Committing' when 5 then 'Prepared' when 6 then 'Committed'
when 7 then 'Rolling Back' when 8 then 'Rolled Back' else 'Unknown - ' + convert(varchar(20), transaction_state) end as 'Transaction State',
case database_transaction_state when 1 then 'Uninitialized' when 3 then 'No Log Records' when 4 then 'Log Records' when 5 then 'Prepared' when 10 then 'Committed' when 11 then 'Rolled Back'
when 12 then 'Committing' else 'Unknown - ' + convert(varchar(20), database_transaction_state) end as 'DB Trx State',
database_transaction_log_record_count as [Log Records Total],
database_transaction_log_bytes_used as [Log Bytes Used],
database_transaction_log_bytes_reserved as [Log Bytes Reserved],
database_transaction_replicate_record_count as [Replication Records]
from
sys.dm_tran_active_transactions activetrx (nolock)
inner join sys.dm_tran_database_transactions dbtrx (nolock) on dbtrx.transaction_id = activetrx.transaction_id
inner join sys.databases d (nolock) on d.database_id = dbtrx.database_id
left join sys.dm_tran_session_transactions sessiontrx (nolock) on sessiontrx.transaction_id = activetrx.transaction_id
left join sys.dm_exec_requests req (nolock) on req.session_id = sessiontrx.session_id and req.transaction_id = sessiontrx.transaction_id
where sessiontrx.session_id is not null
and d.name not in ('Master', 'MSDB', 'Model')
|
|
|
|
|
|
|
|
|
|