http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/06/17/shrink-transaction-log-file/

Printed 2014/07/27 10:16PM

Shrink transaction log file

2012/06/17

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.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.