Transaction Log Backups Based on Log Usage Threshold

January 5, 2005

Automatic file growth in SQL Server data and log files made the SQL Server Database Administrator's life easier, since the transaction log is not filled; it expands automatically based on the percentage settings [refer Fig 1.0]. However, this feature introduced the crisis of the hard disk running out of space, since the transaction log expands automatically.

In this article, I am going to discuss how to avoid this crisis by taking a transaction log backup on time, i.e. before the log file is expanded. So let us go through the process of taking a transaction log backup of a database when the log file usage reaches more than 75% (threshold).

Step 1

Create the procedure "USP_TRANLOGBAK_THRESHOLD" using the script below [Refer Fig 1.1]:

use master
go

Create procedure USP_TRANLOGBAK_THRESHOLD 
@dbname sysname,
@threshold int,
@backuppath varchar(1000)
as
set nocount on
declare @maxcount int
declare @count int 
declare @truncate_only bit 
declare @backupname varchar(800)
declare @status int
declare @query varchar(600)
declare @used int
set @query =''
Create table #logtable (dbname varchar(256), size bigint, Used int, statusx int)
set @query = 'dbcc sqlperf(logspace)'
insert into #logtable exec (@query)

create table #dbtable (id int identity(1,1), dbname varchar(256), status int, used int)
if @dbname = '' 
begin
insert into #dbtable (dbname, status, used) select name,status, used from master..sysdatabases a
join #logtable b on a.name=b.dbname
where   name not in('tempdb','model')  and status & 32 != 32
and status & 64 != 64 and status & 128 != 128
and status & 256 != 256   and status & 512 != 512
and status & 1024 != 1024   and status & 4096 != 4096
and status & 32768 !=32768  
end
else
begin
insert into #dbtable (dbname, status, used) select name,status, used from master..sysdatabases a
join #logtable b on a.name=b.dbname
where   name not in('tempdb','model')  and status & 32 != 32
and status & 64 != 64 and status & 128 != 128
and status & 256 != 256   and status & 512 != 512
and status & 1024 != 1024   and status & 4096 != 4096
and status & 32768 !=32768  
and name = @dbname
end


set @count =1
set @maxcount =  scope_identity()
While @count <= @maxcount
begin
select @dbname =dbname, @status =status, @used=used  from #dbtable where id = @count

set @backupname =@dbname +'_'+convert(varchar(25),getdate(),112)+'_'+replace(convert(varchar(25),
  getdate(),108),':','_')+'.TRN'
if @status & 8 = 8 
begin
select @truncate_only = 1
end
else
begin
select @truncate_only = 0
end

if @truncate_only = 1 and @used >@threshold
	begin
	set @query = 'set quoted_identifier off BEGIN BACKUP LOG [' +@dbname +'] 
	  WITH TRUNCATE_ONLY END'
	print @query
	exec (@query)
	end

if @truncate_only = 0 and @used >@threshold
	begin
	set @query = 'set quoted_identifier off BEGIN BACKUP LOG [' + @dbname + '] to disk = 
	  "'+ @backuppath +@backupname+'"  END'
	print @query
	exec (@query)
	end

set @count =@count+1
end
drop table #logtable 
drop table #dbtable

--Usage :
--USP_TRANLOGBAK_THRESHOLD '',45,'d:\'
--USP_TRANLOGBAK_THRESHOLD 'MyDB',80,'d:\backup\'

Download USP_TRANLOGBAK_THRESHOLD.sql.

Step 2

Create a job to execute the above procedure using the script below. [Refer Fig 1.2]

This job will be executed every 15 minutes from 7 am to 7 pm.

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id
  FROM   msdb.dbo.sysjobs
  WHERE (name = N'JOB_TRANLOGBAK_THRESHOLD')
  IF (@JobID IS NOT NULL)
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''JOB_TRANLOGBAK_THRESHOLD''
	  since there is already a multi-server job with this name.', 16, 1)
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'JOB_TRANLOGBAK_THRESHOLD'
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
    @job_name = N'JOB_TRANLOGBAK_THRESHOLD', @owner_login_name = N'sa',
	@description = N'No description available.', @category_name = N'[Uncategorized (Local)]',
	@enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0,
	@notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1,
    @step_name = N'JOB_TRANLOGBAK_THRESHOLD', 
	@command = N'exec USP_TRANLOGBAK_THRESHOLD '''',75,''d:\backup\''', 
	  @database_name = N'master',
	@server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0,
	@flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', 
	@on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, 
    @name = N'JOB_TRANLOGBAK_THRESHOLD', @enabled = 1, @freq_type = 8, @active_start_date = 20041203,
	@active_start_time = 70000, @freq_interval = 62, @freq_subday_type = 4, 
	@freq_subday_interval = 15, 	@freq_relative_interval = 0, @freq_recurrence_factor = 1,
	@active_end_date = 99991231, 	@active_end_time = 190000
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

Download JOB_TRANLOGBAK_THRESHOLD.sql.

Step 3

Execute the above create job, "JOB_TRANLOGBAK_THRESHOLD."

When the job runs, it finds the log space usage on every database on that server, generates the "BACKUP LOG" statement for a database that exceeds the threshold of 75% and executes that "BACKUP LOG" statement as shown below.

Note that it does "BACKUP LOG with TRUNCATE_ONLY" on databases that are in simple mode.

set quoted_identifier off BEGIN BACKUP LOG [master] WITH TRUNCATE_ONLY END
set quoted_identifier off BEGIN BACKUP LOG [msdb] WITH TRUNCATE_ONLY END
set quoted_identifier off BEGIN BACKUP LOG [pubs] WITH TRUNCATE_ONLY END
set quoted_identifier off BEGIN BACKUP LOG [Northwind] to disk = 
  "d:\backup\Northwind_20041203_15_20_36.TRN"  END
set quoted_identifier off BEGIN BACKUP LOG [MyDB] to disk = 
  "d:\backup\MyDB_20041203_15_20_37.TRN"  END
set quoted_identifier off BEGIN BACKUP LOG [Employee] to disk = 
  "d:\backup\Employee_20041203_15_20_38.TRN"  END
set quoted_identifier off BEGIN BACKUP LOG [x] to disk = "d:\backup\x_20041203_15_20_38.TRN"  END
set quoted_identifier off BEGIN BACKUP LOG [ABC] to disk = 
  "d:\backup\ABC_20041203_15_20_39.TRN"  END
set quoted_identifier off BEGIN BACKUP LOG [FCS] to disk = 
  "d:\backup\FCS_20041203_15_20_40.TRN"  END
set quoted_identifier off BEGIN BACKUP LOG [AgentTrader] to disk = 
  "d:\backup\AgentTrader_20041203_15_20_40.TRN"  END

Step 4

If you would like to have a different threshold for different database, then you can modify the job by passing parameters. [refer Fig 1.4]

exec USP_TRANLOGBAK_THRESHOLD 'Master',75,'d:\backup\'
exec USP_TRANLOGBAK_THRESHOLD 'MyDB',95,'d:\backup\'
exec USP_TRANLOGBAK_THRESHOLD 'Solo',85,'d:\backup\'
exec USP_TRANLOGBAK_THRESHOLD 'Agent',80,'d:\backup\'

Parameters explained

Exec USP_TRANLOGBAK_THRESHOLD 'Master',75,'d:\backup\'

Conclusion

This article has described how to do transaction log backups before the log file is expanded, based on the threshold that you set, for example, 85% or 75%, etc.

» See All Articles by Columnist MAK