Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

My log file was too big, and I made it unrestricted in growth. Should I shrink it with SQL Server Management Studio? What are the downsides, if any, of doing this?

share|improve this question

migrated from stackoverflow.com Jul 21 '13 at 7:31

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Mark Storey-Smith, Aaron Bertrand Jul 21 '13 at 14:35

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers 2

up vote 3 down vote accepted

Two thoughts come to mind when someone says "My log file is too big". First and simplest is that something is running against your database that requires that much space. Unless it happens to be a one off query someone wrote if you shrink your log file it's just going to grow right back. And slow your queries down while it's doing it. The solution here is to find out if there was a large ad-hoc query run that grew your log file. If you don't find any evidence of that then I would shrink the log to a reasonable size and see if it grows back. If it does grow back then leave it alone and add yourself some disk space. That's just how big it has to be. If you restrict it to a smaller size it's going to cause queries that need the extra space to fail.

The second and scarier thought is that your database is in FULL or BULK LOGGED recovery mode and you haven't been taking regular transaction log backups. If this is the case then your log file will just grow and grow. The solution here is to set up regular transaction log backups. Once these are in place shrink the log file to a reasonable size. If it grows again (hopefully not as large), then again that is just the size it has to be.

share|improve this answer

If at all possible cold the DB and take a backup first. Then shrink it and set it to constrained size and get regular backups of the redo log going forward.

share|improve this answer
    
What does "cold the DB" mean? –  Aaron Bertrand Jul 21 '13 at 14:37