Shrink transaction log file

Sometimes the shrink transaction log file doesn't work.

Before we troubleshoot it, we need to know how the log file works:

Transaction Log Physical Architecture

http://msdn.microsoft.com/en-us/library/aa174526(v=sql.80).aspx

Here are some tools which can help us investigate the log file issues.

a. DBCC SQLPERF(logspace) : tell you how much transaction log is being used
b. DBCC LOGINFO: undocumented command which can tell you the VLFs (Virtual Log Files) status in the log file. For more infomation, please refer to http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/21/exploring-the-transaction-log-structure.aspx
c. DBCC OPENTRAN: determine whether an open transaction exists within the transaction log
 

Let's create a testing db first

======================================================================

--the initial log file size is 64MB, the autogrew is enabled with 64MB:

CREATE DATABASE [testlog] ON PRIMARY
( NAME = N'testlog', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\testlog.mdf' , SIZE = 1048576KB , FILEGROWTH = 524288KB )
LOG ON

( NAME = N'testlog_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\testlog_log.ldf' , SIZE = 65536KB , FILEGROWTH = 65536KB )

GO
--create a test table
use testlog
GO

select a.* into testtable from sysobjects a, sysobjects b

GO

--Make a full backup first, it is important, or else sql will reuse the log file space

BACKUP DATABASE [testlog] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\testlog.bak' WITH NOFORMAT, NOINIT, NAME = N'testlog-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

DBCC LOGINFO

GO

=======================================================================

There are 4 VLF in the log file. Status "2" means that VLF is current active VL

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	25	2	64	0
2	16711680	16719872	0	0	0	0
2	16711680	33431552	0	0	0	0
2	16965632	50143232	0	0	0	0

let's insert some data:

=======================================================================

insert into testtable
select a.* from sysobjects a, sysobjects b,sysobjects c
GO

dbcc loginfo

GO
=======================================================================
the output of the "DBCC LOGINFO" like

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	25	2	64	0
2	16711680	16719872	26	2	64	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	0	0	0	0
2	16777216	67108864	0	0	0	27000000325600317
2	16777216	83886080	0	0	0	27000000325600317
2	16777216	100663296	0	0	0	27000000325600317
2	16777216	117440512	0	0	0	27000000325600317

 
The log file grew to 128MB now, the last active VLF is FSeqNo 27, so only 48MB(16MB x 3) log space was used. looks like the log file growth bases on the estimate of the transaction size, sometime the estimate is not accurate. ok, let's start our testing.

Create a active transaction, then insert some data:

====================================================================
--open a session and run
begin tran
insert into testtable
select a.* from sysobjects a

--open another session and run
insert into testtable
select a.* from sysobjects a, sysobjects b,sysobjects c
GO

DBCC LOGINFO

GO
====================================================================
the output of "DBCC LOGINFO" is

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	25	2	64	0
2	16711680	16719872	26	2	64	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	28	2	64	0
2	16777216	67108864	29	2	64	27000000325600317
2	16777216	83886080	0	0	0	27000000325600317
2	16777216	100663296	0	0	0	27000000325600317
2	16777216	117440512	0	0	0	27000000325600317

the last active VLF is 29 now. let's backup the log file:

BACKUP LOG [testlog] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\testlog.bak' WITH NOFORMAT, NOINIT, NAME = N'testlog-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

DBCC LOGINFO

GO

the output is

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	25	0	64	0
2	16711680	16719872	26	0	64	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	28	2	64	0
2	16777216	67108864	29	2	64	27000000325600317
2	16777216	83886080	0	0	0	27000000325600317
2	16777216	100663296	0	0	0	27000000325600317
2	16777216	117440512	0	0	0	27000000325600317

because we have a active tran open in VLF FSeqNo 27, so the bakcup log only truncate the VLF prior to FSeqNo 27. if you try to shrink the log file back to 64MB,

DBCC SHRINKFILE(2, 64)

the output is

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
7	2	10240	8192	10240	8192

The currentsize is 80MB=10240(pages) * 8 / 1024, so the shrinkfile only remove the last 3 VLF, run

DBCC LOGINFO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	30	2	128	0
2	16711680	16719872	26	0	64	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	28	2	64	0
2	16777216	67108864	29	2	64	27000000325600317

is correct! In order to find out which transaction is still active, we can run :

DBCC OPENTRAN

Transaction information for database 'testlog'.

Oldest active transaction:
SPID (server process ID): 53
UID (user ID) : -1
Name : user_transaction
LSN : (27:4861:1)
Start time : Jun 17 2012 12:37:02:740PM
SID : 0x0105000000000005150000005d28f57fd53ad8354354e02a75841400

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Based on the output we can get the spid is 53, the transaction type is user_transaction, we can also get what spid 53 is running by :

DBCC INPUTBUFFER (53)

the output is like

EventType	Parameters	EventInfo
Language Event	0	begin tran insert into testtable select a.* from sysobjects a

so active transaction will stop the log file shrik working. let's continoue our testing.

Commit the transaction first, and insert more data

--run in the session which you create the transaction
commit tran

--insert more data

insert into testtable
select a.* from sysobjects a, sysobjects b,sysobjects c
GO

DBCC LOGINFO

GO

ok, the log file size grew again

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	30	2	128	0
2	16711680	16719872	31	2	128	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	28	2	64	0
2	16777216	67108864	29	2	64	27000000325600317
2	16777216	83886080	32	2	64	31000000097600316
2	16777216	100663296	0	0	0	31000000097600316
2	16777216	117440512	0	0	0	31000000097600316
2	16777216	134217728	0	0	0	31000000097600316

compare with the previouse output, the original VLF FSeqNo 26 is reused(because its status was 0), the current number of it is 31, because FSeqNo 27,28,29 haven't been backed up(status is 2), so they can not be used. let's backup the log file again:

==================================================================
BACKUP LOG [testlog] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\testlog.bak' WITH NOFORMAT, NOINIT, NAME = N'testlog-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

DBCC LOGINFO

GO
==================================================================

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	30	0	128	0
2	16711680	16719872	31	0	128	0
2	16711680	33431552	27	0	64	0
2	16965632	50143232	28	0	64	0
2	16777216	67108864	29	0	64	27000000325600317
2	16777216	83886080	32	2	64	31000000097600316
2	16777216	100663296	0	0	0	31000000097600316
2	16777216	117440512	0	0	0	31000000097600316
2	16777216	134217728	0	0	0	31000000097600316

OK, this time only VLF FSeqNo 32 is active(status = 2), that's cool!, let's check the log file usage:

wow! 98% free space, only 3MB space is used. can we shrink it to initial size 64MB now? let's try

DBCC SHRINKFILE(2, 64)

GO

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
7	2	12288	8192	12288	8192

the log file size is 96MB = 12288 * 8 / 1024.....

So DBCC SHRINKFILE can only truncate the VLF from last active VLF to the end, even if you have more free space in the log file, you can not shrink them.

if you really need to shrink the file, you need to move the active VLF to the beginning of the log file, create a dummy table and insert data into it, if the transaction is big enough to move the active VLF to the beginning of the log file, then backup the log. here is the VLF status you will get

--generate big transaction

--run the insert several times until the active VLF move to the beginning of the log file

insert into testtable

select a.* from sysobjects a, sysobjects b,sysobjects c

--backup the log file

BACKUP LOG [testlog] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\testlog.bak' WITH NOFORMAT, NOINIT, NAME = N'testlog-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

DBCC LOGINFO

GO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	40	2	128	0
2	16711680	16719872	36	0	64	0
2	16711680	33431552	38	0	64	0
2	16965632	50143232	39	0	64	0
2	16777216	67108864	37	0	128	27000000325600317
2	16777216	83886080	32	0	64	31000000097600316

cool! the active VLF has been moved to the beginning of the log file.
let's try shrink file again

DBCC SHRINKFILE(2, 64)
GO

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
7	2	8192	8192	8192	8192

DBCC LOGINFO

GO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	40	2	128	0
2	16711680	16719872	36	0	64	0
2	16711680	33431552	38	0	64	0
2	16965632	50143232	39	0	64	0

well, the log file size is 64MB now :)

can I shrink the log file to the size which is smaller than initial size? Yes, you can.
let's run

DBCC SHRINKFILE(2, 32)

GO

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
7	2	6121	6121	6120	6120

DBCC LOGINFO

GO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	40	2	128	0
2	16711680	16719872	36	0	64	0
2	16711680	33431552	38	0	64	0

We have 3 VLF, 48MB now. However, you can not shrink the log file smaller than 2 VLF, in our case, the smallest size of log file is 16MB * 2 = 32MB.

As a summary

1. Active transaction.

we can use "DBCC OPENTRAN" find out it, and use "DBCC INPUTBUFFER" to check the query. Sometimes replication will cause active tran issue.

As I remember, once I restored a database backup which was taken when the database was under transaction replication, Log Reader agent maintains pointer in the Transaction Log for tracking the changes in the database. so the log file can not be shrink, but we can use the command below to remove it

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

this command can only be used when the database is not in replication.

 2. Active VLF is not at the beginning of the log file

backup log file first, then create a dummy table, generate a big transaction to move the active VLF to the beginning of the log file.

3. if log file keeps growing, check the sys.databases table

select log_reuse_wait_desc from sys.databases where name='dbname'

4. for the optimize VLF size, create the initial log file as 8GB, then extend to 16GB, then 24 GB and so forth,

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

too many VLF will degrade the log performance.

5. Use "DBCC SQLPERF (LOGSPACE)" and "DBCC LOGINFO" to check the log file status.