sQL: SQL 2000 truncate logs using DBCC
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.
