Friday, December 27, 2013

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(2500)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(2500)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:


Other references:


No comments: