patternMinor
Shrinking a SQL Server Log File Multiple Times
Viewed 0 times
filelogsqlmultipleservertimesshrinking
Problem
Recently ran into a situation where after a log backup and shrinkfile, the LDF file remained the same size. DBCC LOGINFO clearly showed only one active VLF and SSMS said that 99% of the space in the LDF was free. The first attempt did not shrink nor did the second attempt or the third. After the fourth attempt, success, the log was reduced to the requested size. Meanwhile, DBCC LOGINFO was saying that after every SHRINKFILE, another VLF became active.
I decided to run a test of a new database.
```
CREATE DATABASE logs_test
USE logs_test
-- first look at the VLFs for the logs_test database
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 19 2 64 0
2 253952 262144 0 0 0 0
*/
-- put the database into FULL recovery before making a backup
ALTER DATABASE logs_test SET RECOVERY FULL
-- first look at the VLFs for the logs_test database
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 19 2 64 0
2 253952 262144 0 0 0 0
*/
BACKUP DATABASE logs_test TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\logs_test_250503.bak'
WITH INIT
GO
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952
I decided to run a test of a new database.
```
CREATE DATABASE logs_test
USE logs_test
-- first look at the VLFs for the logs_test database
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 19 2 64 0
2 253952 262144 0 0 0 0
*/
-- put the database into FULL recovery before making a backup
ALTER DATABASE logs_test SET RECOVERY FULL
-- first look at the VLFs for the logs_test database
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 19 2 64 0
2 253952 262144 0 0 0 0
*/
BACKUP DATABASE logs_test TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\logs_test_250503.bak'
WITH INIT
GO
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952
Solution
When you use DBCC SHRINKFILE(Logfile, size) it only truncates from the end of the log file back as far as it can go. When it reaches the highest virtual log still in use, it cannot shrink further. This is described in the SQL Server Books Online at:
http://technet.microsoft.com/en-us/library/ms189493.aspx
So, once the high end of the log is clear, it can be shrunk down in size. Again, that will depend on how much of the log is still in use. The log can be cleared by backups, but the backups will not clear incomplete transactions, so the log can remain in a high-end VLF even after repeated backups.
With regard to the increase and decrease of VLFs, how big was the log file created to be originally and what is the setting for log file growth? If it grows by only a small amount it will create more VLFs than anyone desires.
A common pattern for shrinking a log file is CHECKPOINT, BACKUP, SHRINKFILE, CHECKPOINT, BACKUP, SHRINKFILE, etc until you get results. There are many reasons that the log may not be shrinkable, including a very large rollback. (Not your current problem, of course.)
http://technet.microsoft.com/en-us/library/ms189493.aspx
So, once the high end of the log is clear, it can be shrunk down in size. Again, that will depend on how much of the log is still in use. The log can be cleared by backups, but the backups will not clear incomplete transactions, so the log can remain in a high-end VLF even after repeated backups.
With regard to the increase and decrease of VLFs, how big was the log file created to be originally and what is the setting for log file growth? If it grows by only a small amount it will create more VLFs than anyone desires.
A common pattern for shrinking a log file is CHECKPOINT, BACKUP, SHRINKFILE, CHECKPOINT, BACKUP, SHRINKFILE, etc until you get results. There are many reasons that the log may not be shrinkable, including a very large rollback. (Not your current problem, of course.)
Context
StackExchange Database Administrators Q#48525, answer score: 4
Revisions (0)
No revisions yet.