Shrinking of transaction log file and other maintenance DB best pratices

Shrinking of transaction log file

SQL Server cannot move log records from the end of the log file toward the beginning of the log file. This means that SQL Server can only cut down the file size if the file is empty at the end of the file. The end-most log record sets the limit of how much the transaction log can be shrunk. A transaction log file is shrunk in units of Virtual Log Files (VLF). You can see the VLF layout using the undocumented DBCC LOGINFO command, which returns one row per virtual log file for the database: 

DBCC LOGINFO('myDatabase') 

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	253952	8192	11	0	128	0
2	253952	262144	13	0	128	0
2	270336	516096	12	0	128	7000000025000288
2	262144	786432	14	2	128	9000000008400246

The interesting column is "Status". 0 means that the VLF is not in use and 2 means that it is in use. In my example, I have 2 at the end of the file (read result from top to bottom) and this means that the file cannot currently be shrunk. How to handle this depends on your recovery model. You should adjust and replace options and database appropriately for below code. If you are uncertain, then check the command in the product documentation:

• Simple recovery model
  USE dbname
  CHECKPOINT--First param below is fileno for log file, often 2. Check with sys.database_files
  --Second is desired size, in MB.DBCC SHRINKFILE(2, 500)DBCC SQLPERF(LOGSPACE) --OptionalDBCC LOGINFO --Optional
  Now repeate above commands as many times as needed!

• Full or bulk_logged recovery model
  USE dbname
  BACKUP LOG dbname TO DISK = 'C:\x\dbname.trn'--First param below is fileno for log file, often 2. Check with sys.database_files
  --Second is desired size, in MB.DBCC SHRINKFILE(2, 500)DBCC SQLPERF(LOGSPACE) --OptionalDBCC LOGINFO --Optional
  Now repeate above commands as many times as needed!

What you end up doing is empty the log (CHECKPOINT or BACKUP LOG) and DBCC SHRINKFILE several times, so that SQL Server can move the head of the log to beginning of the file and also so the end of the file becomes unused. Investigate the layout of the log file using DBCC LOGINFO in between.

In:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Other references:

http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx