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
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 |
insert into testtable
select a.* from sysobjects a, sysobjects b,sysobjects c
GO
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.
====================================================================
--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
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 |
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 |
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 :
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
EventType | Parameters | EventInfo |
Language Event | 0 | begin tran insert into testtable select a.* from sysobjects a |
--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
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 |
==================================================================
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
==================================================================
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 |
GO
DbId | FileId | CurrentSize | MinimumSize | UsedPages | EstimatedPages |
7 | 2 | 12288 | 8192 | 12288 | 8192 |
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
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 |
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 |
can I shrink the log file to the size which is smaller than initial size? Yes, you can.
let's run
GO
DbId | FileId | CurrentSize | MinimumSize | UsedPages | EstimatedPages |
7 | 2 | 6121 | 6121 | 6120 | 6120 |
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 |
1. Active transaction.
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.
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