Internet

Linux

MAC

PC General

Windows

NOTE! Before using any of those informations, be sure you read the Disclaimer! Thank you.

Home » SQL

How to shrink the transaction log file in SQL Server 2008

Submitted by No Comment

n 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:

dbcc loginfo('msdb')
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 @rc

The procedure is easy to execute.

EXEC [dbo].[ns_shrink_db_log] 'scratch', 2, 'c:temp', 'scratch_shrink_backup', 4

Starting size of [scratch].[scratch_log] is 16 MB  recovery model = FULL
BACKUP LOG [scratch]  to disk = 'c:tempscratch_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.

Related posts:

  1. How to determine SQL Server database transaction log usage
  2. Problem with running DBCC SHRINKFILE on your SQL Server data files
  3. SQL Server Transaction Isolation Models
  4. Restoring SQL Server 2008 R2 Backup file to SQL Server 2008
  5. How to solve Error 8993 In Corrupted Sql Server 2008 Database

Tips and tutorials references:

cisco dbcc shrink for sql server 8 , sql server 10 50 shrink transaction log , sql server 2008 shrink vlf filesize , why dbcc shinkfile does not shrink log file much

Leave a Reply