Blog Post

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating