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:
Other references:
