Ask the Expert

Is there a way to shrink the transaction log?

I am having a problem with backing up transaction logs on MS SQL 2000. I have a maintenance plan running that backs up the database, but not the transaction log. I hadn't noticed the size before, but now the transaction log has filled up the hard drive. I am unable to backup and truncate, as there is no room to back up to. I have tried to use "DBCC SHRINKFILE," but it only shrinks it a few hundred megabytes. Is there any other way to shrink the transaction log?

    Requires Free Membership to View

    Login

If you don't require the transaction log for recovery purposes then you need to change the recovery model that the database is using to SIMPLE so this doesn't happen again. You can do this by executing

 ALTER DATABASE databasename SET RECOVERY SIMPLE
DBCC SHRINKFILE should then be able to reduce the log file to a reasonable size. Alternatively you can clear the transaction log of committed transactions by executing 
 BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE should now also be more effective, however if you don't change the recovery model to SIMPLE the log file will just fill up over time again. Search on "Selecting a Recovery Model" in SQL Server Books online for more information.

 

For More Information

This was first published in June 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top