Binaryworld - Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ... [ SQL Server -> Server Operations ]

Today I faced a strange problem. I have a database called NiteLink and logfile grew upto 25GB but actual data is only 2GB in datafile. So I tried to run the following command to shrink log file to 1.5 GB

Click here to copy the following block

DBCC SHRINKFILE (nitelink_log, 1500)

But this command did not do anything. I found more info about this issue from KB article Q272318.

Shrinking the log in SQL Server 2000 is no longer a deferred operation. A shrink operation attempts to shrink the file immediately. However, in some circumstances it may be necessary to perform additional actions before the log file is shrunk to the desired size.

When DBCC SHRINKFILE is run, SQL Server 2000 shrinks the log file by removing as many virtual log files as it can to attempt to reach the target size. If the target file size is not reached, SQL Server places dummy log entries in the last virtual log file until the virtual log is filled and moves the head of the log to the beginning of the file. The following actions are then required to complete the shrinking of the transaction log:

You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log.
You must run DBCC SHRINKFILE again with the desired target size until the log file shrinks to the target size.

The following example demonstrates this with the pubs database and attempts to shrink the pubs_log file to 2 MB:

1. Run this code:

Click here to copy the following block

DBCC SHRINKFILE(pubs_log, 2)

NOTE: If the target size is not reached, proceed to the next step.

2. Run this code if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only:

Click here to copy the following block

BACKUP LOG pubs WITH TRUNCATE_ONLY

3. Run this code:

Click here to copy the following block

DBCC SHRINKFILE(pubs_log,2)

The transaction log has now been shrunk to the target size.

If you face the same issue with SQL Server 7.0 then visit the following KB Article

http://www.support.microsoft.com/kb/256650/EN-US/