Truncating Transaction Log in SQLServer 2000

Few days back I had a situation when size of my database transcation log was growing too  fast (at a point it reached 60 GB). so I had to find some way to truncate it to some minimal size. After lot of googling, I found how to truncate the transaction log.

Run these two commands in Query Analyser.

1. BACKUP LOG <<DBName>> WITH TRUNCATE_ONLY
2. DBCC SHRINKFILE(<<DBName>>_log, <<Desired transaction filesize>> )

Remarks :

• DBCC SHRINKFILE applies to the files in the current database. If you try to  run this command in a different DB then the DB you want to shrink, SQL Server will give following error

Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file ‘<<DBName>>_log’ in sysfiles.

• SQL Server uses <<Desired transaction filesize>> to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. DBCC SHRINKFILE attempts to shrink each physical log file to its target size immediately. If no part of the logical log resides in the virtual logs beyond the log file’s target size, the file is successfully truncated and DBCC SHRINKFILE completes with no messages. However, if part of the logical log resides in the virtual logs beyond the target size, SQL Server frees as much space as possible and then issues an informational message. The message tells you what actions you need to perform to move the logical log out of the virtual logs at the end of the file. (Remarks are taken from MSDN)

References…..

1. Microsoft Knowledge Base (KB27231
2. DBCC SHRINKFILE

Ha-P Querying