Shrink Database Log file

There are some misunderstandings about shrinking database log file. One mysterious is that sometimes you cannot shrink a log file though it is barely used.
 
We can use DBCC SHRINKFILE or Tasks–>Shink–>Files menu in SSMS to shrink database log file if it has logts of space
unused. You can check Log usage by DBCC SQLPERF(‘LogSpace’)
DBCC SQLPERF(‘LogSpace’)
DatabaseName LogSize(MB) LogSpaceused(%)  Status
tempdb  31.17188 60.10025  0
YourDB  505.0547 2.92821   0
 
However, sometimes it does not shrink the log file even though the disk usage report says that only 10% of the space
is being used. This is becuase database log files are managed by Virtual Log File (VLF). Run DBCC LOGINFO, it gives
details of the VLF:
DBCC LOGINFO
FileId  FileSize        StartOffset     FSeqNo  Status  Parity  CreateLSN
2 11993088 481427456 264804 2 64 264803000002030600330
2 11993088 493420544 264805 2 64 264803000002030600330
2 11993088 505413632 264806 2 64 264803000002030600330
2 12189696 517406720 264807 2 64 264803000002030600330
Status=2 means the VLF is being used. Backup the log:
BACKUP LOG YourDB TO DISK=’C:\DBBackup\Logs\YourDBLogBackup_20090430_1005.Bak’
 
DBCC LOGINFO
FileId  FileSize        StartOffset     FSeqNo  Status  Parity  CreateLSN
2 11993088 481427456 264804 0 64 264803000002030600330
2 11993088 493420544 264805 0 64 264803000002030600330
2 11993088 505413632 264806 0 64 264803000002030600330
2 12189696 517406720 264807 2 64 264803000002030600330
 
A transaction log file is shrunk in units of Virtual Log Files (VLF). SQL Server can ONLY shrink or remove inactive
(status=0) VLFs from the end of the file. It cannot move log records from the end of the log file toward the beginning
of the log file. So if you try to shink the log, it still does not work even most of the log is not used:
DBCC SHRINKFILE(‘YourDB_LOG’,300)
Cannot shrink log file 2 (YourDB_LOG) because all logical log files are in use.
 
The transaction log is emptied (set status to 0) ONLY when you backup the log. Backup database does not empty any VLF
There are two ways to empty the last VLF:
1) fill up the last VLF by small transactions and make SQL reverse back to use the first VLF. Then Backup log to files, e.g.:
CREATE TABLE dbo.TmpTable (RID int identity, C1 int not null, C2 int not null PRIMARY KEY CLUSTERED(RID))
DECLARE @i int
SET @i=0
WHILE @i<100
BEGIN
 BEGIN TRAN
 INSERT dbo.TmpTable(C1,C2) VALUES (@i,@i+1)
 COMMIT TRAN
 SET @i=@i+1
END
Then run DBCC LOGINFO to check VLF status. Check the VLFs are reversed back (the first VLF status becomes 2) or not,
DBCC LOGINFO
FileId  FileSize        StartOffset     FSeqNo  Status  Parity  CreateLSN
2 11993088 481427456 264804 2 64 264803000002030600330
2 11993088 493420544 264805 0 64 264803000002030600330
2 11993088 505413632 264806 0 64 264803000002030600330
2 12189696 517406720 264807 2 64 264803000002030600330
 
Now you can backup the log and check the log status again:
DBCC LOGINFO
FileId  FileSize        StartOffset     FSeqNo  Status  Parity  CreateLSN
2 11993088 481427456 264804 2 64 264803000002030600330
2 11993088 493420544 264805 0 64 264803000002030600330
2 11993088 505413632 264806 0 64 264803000002030600330
2 12189696 517406720 264807 0 64 264803000002030600330
 
The unused VLFs are all in the end of the log file, now you can shrink it.
DBCC SHRINKFILE(‘YourDB_LOG’,300)
DBCC LOGINFO
FileId  FileSize        StartOffset     FSeqNo  Status  Parity  CreateLSN
2 11993088 481427456 264804 2 64 264803000002030600330
2) Change recovery model to "Simple". If you do not have open transactions, the last VLF will become inactive. Then
you can shrink the log file.
Check open transactions:
DBCC OPENTRAN.
or SELECT session_id,open_transaction_count FROM sys.dm_exec_requests
If you have open transactions, you can roll them back by killing the connection:
KILL session_id
Remember to change back the recovery model to full. You need to take a full backup to make the new recovery model to
take effect.
 
 
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

One Response to Shrink Database Log file

  1. penny stocks says:

    It’s going to be end of mine day, however before end I am reading this wonderful piece of writing to increase my experience.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s