sQL: SQL 2000 truncate logs using DBCC

Published by

Posted on January 22, 2010

DBCC statememnts act as Database Console commands. You can use the below listed queries, sprcifically the DBCC ones to shrink your transaction log.

— To GET the name of the log and data files
select name from dbo.sysfiles

— SEE the active transactions as well as the File ID
dbcc loginfo(‘db_Name’)

— Mark transactions inactive so that they can be truncated later
— Suppose file ID is 2

DBCC SHRINKFILE (2)
— Backup the log file and truncate the inactive entries
BACKUP LOG [db_name] WITH TRUNCATE_ONLY
— Run Shrinkfile again for maintainence.
DBCC SHRINKFILE (2)

You can create a stored procedure out of the last 3 statements and schedule it as a job on your server than can run overnight and ensure that the transaction log file size does not get out of hand.

Categories: SQL