INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
- 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.
- Run this code:NOTE: If the target size is not reached, proceed to the next step.
DBCC SHRINKFILE(pubs_log, 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:
BACKUP LOG pubs WITH TRUNCATE_ONLY-or-Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information:
BACKUP LOG pubs TO pubslogbackup
- Run this code:The transaction log has now been shrunk to the target size.
REFERENCESFor more information, see the Shrinking the Transaction Log and DBCC SHRINKFILE topics in SQL Server 2000 Books Online.
For additional information about shrinking the transaction log in SQL Server 7.0, click the article number below to view the article in the Microsoft Knowledge Base:
Article ID: 272318 - Last Review: 09/17/2011 09:02:00 - Revision: 5.0
- kbsqlsetup kbinfo KB272318