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).
Click for larger image
[Fig 1.0]
Step 1
Create
the procedure "USP_TRANLOGBAK_THRESHOLD" using the script below [Refer
Fig 1.1]:
use master
goCreate 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
endset @count =1
set @maxcount = scope_identity()
While @count <= @maxcount
begin
select @dbname =dbname, @status =status, @used=used from #dbtable where id = @countset @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
endif @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)
endif @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)
endset @count =@count+1
end
drop table #logtable
drop table #dbtable–Usage :
–USP_TRANLOGBAK_THRESHOLD ”,45,’d:\’
–USP_TRANLOGBAK_THRESHOLD ‘MyDB’,80,’d:\backup\’
WITH TRUNCATE_ONLY END’Download USP_TRANLOGBAK_THRESHOLD.sql.
[Fig 1.1]
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
ENDBEGIN
— 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\”’,
@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 = 1IF (@@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 QuitWithRollbackEND
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Download JOB_TRANLOGBAK_THRESHOLD.sql.
[Fig 1.2]
[Fig 1.3]
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 =
set quoted_identifier off BEGIN BACKUP LOG [MyDB] to disk =
set quoted_identifier off BEGIN BACKUP LOG [Employee] to disk =
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 =
set quoted_identifier off BEGIN BACKUP LOG [FCS] to disk =
set quoted_identifier off BEGIN BACKUP LOG [AgentTrader] to disk =
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\’
[Fig 1.4]
Parameters explained
Exec USP_TRANLOGBAK_THRESHOLD 'Master',75,'d:\backup\'
| USP_TRANLOGBAK_THRESHOLD | – | Procedure name |
| ‘Master’ | – | Database name. If parameter is ” then it takes all the databases into consideration. |
| 75 | – | Threshold Size |
| ‘d:\backup’ | – | Backup folder location |
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.
