Sometimes the shrink transaction log file doesn't work.

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

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

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

GO

--create a test table

use testlog

GO

GO

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

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

insert into testtable

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

GO

GO

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

the output of the "DBCC LOGINFO" like

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.

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

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

GO

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

the output of "DBCC LOGINFO" is

GO

the output is

DBCC SHRINKFILE(2, 64)

the output is

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

DBCC LOGINFO

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

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

--run in the session which you create the transaction

commit tran

insert into testtable

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

GO

GO

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

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

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

GO

insert into testtable

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

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

GO

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

let's run

GO

GO

1. Active transaction.

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

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.

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

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