Shrink your SQL 2005 transaction logfiles

This might come in handy for some of you, as it did for me. I normally don’t have problems with large transaction logfiles, but on our acceptance machine we have mirrored the production database. As it’s replicated, the databases are installed multiple times on the machine. In theory it’d be best to have them on multiple machines (to mirror production exactly), but we chose multiple SQL Server 2005 instances.

When the transaction logfiles are growing they take up a huge amount of diskspace. So in our script to restore the environment, we’ve included a few T-SQL statements to set the recovery model to simple and shrink the transaction logfiles immediately. Here’s the code, it might come in handy when you’ve got a database on your own machine as well.

USE [master]
GO
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [YourDatabase]
GO
DBCC SHRINKFILE (YourDatabase_log, 10)
GO

You don’t want to set the recovery model to simple on critical databases! If you’re not sure, just don’t change the recovery model. Transactional replication uses the transaction log and if the service agent stops, all transactions are stored in the log to be synced later.

You may also like...

11 Responses

  1. Brian Sutherland says:

    Worked a treat, thanks.

  2. I am a student intern trying to learn the world of sql database administration and space issues seem to come up frequently. This is a different method than I had been shown before to backup the log with no truncate then use dbcc shrinkfile. Very interesting, thanks!

  3. SK says:

    It really worked.. Thanks a lot

  4. Gajji says:

    Thanks a lot…it was really helpful

  5. cwilson says:

    Thank you very much! Worked perfectly.

  6. Yvonne Overduin says:

    Thank you very much! Worked perfectly.

  7. RH says:

    Thanks worked a treat 🙂

  8. Vishal says:

    Neat Trick, thanks Dennis

  9. Ehab says:

    it worked with me too, many thanks

  10. Is there a way to loop through all active databases and shrink all the log files on the server? This would be great as a part of the maintenance plan.

Click on a tab to select how you'd like to leave your comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.