By: Andy Novick | Comments (17) | Related: More > Database Administration
Problem
There are times when a SQL Server transaction log file grows so large that it has to be shrunk back to a more reasonable size. Before shrinking the log file it is necessary to remove the recent transaction log entries from the log. That use to be done with the BACKUP LOG.... WITH TRUNCATE_ONLY command. The TRUNCATE_ONLY option was removed in SQL Server 2008. How do you remove the log entries so the transaction log file can be shrunk?
Solution
In a recent article, Issues with running DBCC SHRINKFILE on your data files wrote about why not to shrink a data file with DBCC SHRINKFILE. The issues with shrinking data files that I described do not apply to log files, which have a much different structure. Inside the transaction log are one of SQL Server's internal structures: Virtual Log Files (VLF). The log is divided into VLF's so that space can be allocated and re-used once the transaction log entries in the VLF have been backed up. There's a detailed description of Virtual Log files in the article How to determine SQL Server database transaction log usage
To see how many VLF's are contained in a database go to the database run the DBCC LOGINFO command as seen here:
FileId FileSize artOffset SeqNo Status Parity CreateLSN
------ -------- --------- ----- ------ ------ -------------------
2 253952 8192 31239 0 128 0
2 262144 262144 31243 0 128 0
2 262144 524288 31242 0 128 96000000012800004
.
. rows omitted
.
2 524288 22675456 31229 0 128 6733000000065600011
2 524288 23199744 31230 0 128 6733000000065600011
2 524288 23724032 31231 0 128 6733000000065600011
2 720896 24248320 31232 0 128 6733000000065600011
(71 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator. |
Issuing a BACKUP LOG with TRUNCATE_ONLY or BACKUP LOG with NO_LOG use to be a common solution to clear out the transaction log so that it could be shrunk. It was never a great idea and the article Issues with running backup log with no_log or truncate_only in SQL Server documents why. In short the right thing to do is to make a real transaction log backup. That's what the procedure ns_shrink_db_log does. It makes a transaction log backup and then runs DBCC SHRINKFILE on the log.
The reason that it's a procedure at all is that sometimes it's necessary to make multiple transaction logs and run SHRINKFILE each time before the log shrinks to the desired size. To accommodate this reality, ns_shrink_db_log runs in a loop. There are several parameters this stored procedure takes:
- @db_name - database name that you want to shrink log file
- @target_size_mb - the desired size of the tranascaion log
- @backup_location - location of the backup files
- @backup_file_name - name for the backup files. As each attempt is made the attempt number is added to the back of the file name along with the standard extension ".trn".
- @maximum_attempts - governs how many times it tries.
Here's the code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[ns_shrink_db_log]
@db_name SYSNAME = NULL
, @target_size_mb INT = 2
, @backup_location NVARCHAR(200) = NULL
, @backup_file_name NVARCHAR(200) = NULL
, @maximum_attempts INT = 10
/* Shrinks the log file of @db_name to the @target_size_mb
*
exec [dbo].[ns_shrink_db_log] 'scratch', 2, 'c:\temp\'
, 'scratch_shirnk_backup', 4
************************************************************/
AS
SET NOCOUNT ON
SELECT @db_name = COALESCE(@db_name, DB_NAME())
DECLARE @logical_log_file_name SYSNAME,
@backup_log_sql NVARCHAR(MAX),
@shrink_sql NVARCHAR(MAX),
@checkpoint_sql NVARCHAR(MAX),
@db_id INT = DB_ID (@db_name),
@start_size_mb INT,
@final_size_mb INT,
@attempts INT = 0,
@recovery_model INT,
@recovery_model_desc SYSNAME,
@rc INT = 0 -- return code
SELECT @logical_log_file_name = name,
@start_size_mb = size / 128
FROM MASTER..sysaltfiles
WHERE dbid=@db_id AND fileid=2
SELECT @recovery_model = recovery_model
, @recovery_model_desc = recovery_model_desc
FROM sys.databases
WHERE database_id=@db_id
PRINT 'Starting size of [' + @db_name + '].['
+ @logical_log_file_name
+ '] is '
+ CONVERT(VARCHAR(20), @start_size_mb) + ' MB '
+ ' recovery model = ' + @recovery_model_desc
IF @start_size_mb <= @target_size_mb BEGIN
PRINT '['+@db_name+'] does not need shrinking'
END
ELSE BEGIN
IF @recovery_model != 3
AND (@backup_file_name IS NULL OR @backup_location IS NULL) BEGIN
RAISERROR ('Null backup file location or name. aborting.', 16, 1)
SET @rc = 50000
GOTO get_out
END
WHILE @attempts < @maximum_attempts
AND @target_size_mb < (SELECT CONVERT(INT, size/128) FROM MASTER..sysaltfiles
WHERE dbid = @db_id AND
name = @logical_log_file_name) -- not target
BEGIN
SET @attempts = @attempts + 1
IF @recovery_model= 3 BEGIN
SET @checkpoint_sql = 'use ['+@db_name+']; '
+ 'checkpoint'
PRINT @checkpoint_sql
EXEC (@checkpoint_sql)
END
ELSE BEGIN
SET @backup_log_sql = 'BACKUP LOG ['+ @db_name + '] '
+ ' to disk = ''' + @backup_location
+ CASE WHEN RIGHT(RTRIM(@backup_location), 1)='\'
THEN '' ELSE '\' END
+ @backup_file_name
+ CONVERT(VARCHAR(10), @attempts)
+ '.trn'''
PRINT @backup_log_sql
EXEC (@backup_log_sql) -- See if a trunc of the log shrinks it.
END
SET @shrink_sql = 'use ['+@db_name+'];'
+ 'dbcc shrinkfile (['+@logical_log_file_name+'], '
+ CONVERT(VARCHAR(20), @target_size_mb) + ')'
EXEC (@shrink_sql)
END
END
SELECT @final_size_mb = size/128
FROM MASTER..sysaltfiles
WHERE dbid = @db_id AND name = @logical_log_file_name
PRINT 'Final size of [' + @db_name + '].['
+ @logical_log_file_name
+ '] is ' +
CONVERT(VARCHAR(20),@final_size_mb)
+ ' MB'
get_out:
RETURN @rcThe procedure is easy to execute.
EXEC [dbo].[ns_shrink_db_log] 'scratch', 2, 'c:\temp\', 'scratch_shrink_backup', 4Starting size of [scratch].[scratch_log] is 16 MB recovery model = FULL
BACKUP LOG [scratch] to disk = 'c:\temp\scratch_shirnk_backup1.trn'
Processed 1 pages for database 'scratch', file 'scratch_log' on file 5.
BACKUP LOG successfully processed 1 pages in 0.014 seconds (0.139 MB/sec).
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
7 2 256 128 256 128
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Final size of [scratch].[scratch_log] is 2 MB |
One thing to remember once you've run ns_shrink_db_log: those backup files are for real. They're on disk and they should be included in your normal backup procedure. Most of my clients copy the .trn files off to another server for safe keeping. If possible the other server should be in a different location to allow for disaster recovery.
Before going out and shrinking the logs in all your databases because it's so easy now, step back and reconsider why you'd ever shrink a log file. The log file grew for a reason. Logs grow because transactions modify the database and there's more transaction activity then transaction log backups. If the logs grew to that size once, isn't it pretty likely that they'll grow again? In most cases the answer is yes. If there was something unusual that caused the logs to grow, you might have reason to shrink the log. However, it's often the case that the logs will just grow back to their previous size.
One of the events that you'll want to avoid is autogrowth on the log file during the hours that users are using the database. Log growth is slow and when it's necessary transactions must wait for the file to grow and be initialized. Log files are not subject to "Instant File Initialization" the way data files are. You can read about that in the article Configuring Windows Instant File Initialization for SQL Server 2005.
Next Steps
- Reconsider even shrinking logs. It's usually not a great idea.
- When it's absolutely necessary to shrink a log, Use the procedure in this article.
- I noticed that I referred to four other articles on this site in this one article! MSSQLTips is full of solid information about SQL Server so be sure to at least look at the tip every day and read the ones that seem interesting in more depth.
About the author
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
