How To Shrink Transaction Log in MSSQL 2000

Suppose our MSSQL 2000 database transaction log has grown very big so we need to cut it manually during maintenance in order to save some disk space. Here are the steps to truncate database transaction log (the example uses Pubs sample database and truncates it to 100 Mb):

1. DBCC SHRINKFILE(Pubs_log, 100)

2. BACKUP log Pubs WITH TRUNCATE_ONLY

3. DBCC SHRINKFILE(Pubs_log, 100)

Transaction log will be truncated to the desired size.

Update: WITH TRUNCATE_ONLY is deprecated in MSSQL 2005.

Related Posts:

• T-SQL: Output Parameter in sp_executesql
• MSSQL: Change tables owner to dbo with sp_changeobjectowner
• MSSQL: Recompile Stored Procedures, Views, Functions
• MSSQL Tip: Rebuild Clustered Indexes
• T-SQL: Aggregate Values Multiplication Like SUM Aggregates Values Sum
• SQL Tip: HowTo Convert Delimited String to Table