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:

I'm trying to shrink the file size of a massive log file and was wondering how I estimate the size of the log file and what the dangers in running this are

share|improve this question

migrated from stackoverflow.com Feb 16 '12 at 10:23

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

marked as duplicate by Max Vernon, Paul White, Mark Storey-Smith, bluefeet, RolandoMySQLDBA Oct 31 '13 at 13:32

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.

3  
The first and most important thing is to find out why your log file is so large and fix that. If you are not doing transactionlog backups (not just db backups) this will occur repeately. If you are doing large batch transactions that are logged, you may want to run in batches to make the transaction log stay smaller, etc. In this case if you shrink too small and you can't use batches, you could have performance issues regrowing the log to the size it needs to be. –  HLGEM Feb 15 '12 at 22:15

3 Answers 3

If you use the full or bulk logged recovery model, I advise that you take a look at this post for shrinking the log file. First perform a full database backup as switching the recovery models will break the log chain. Also chose a reasonable initial size for the log file, and generally speaking I use an increment in MB rather than percents.

share|improve this answer

I had this same issue with one of our larger production databases. As the others have said, shrinking your database without knowing the root cause of the log file growth is problematic. Once of the things you want to do is get an idea of how much your log is growing between backups. I created a table and an Agent job to capture this information every 15 minutes so I could see what time of day I was seeing growth, etc.

My job executes the following SQL:


INSERT INTO Temp_db.dbo.LogSpace ([Database Name], [Log Size (MB)], [Log Space Used (%)], [Status])
    EXEC ('DBCC SQLPERF (LOGSPACE)')

Clearly, you'll need a table with the [Database Name], [Log Size (MB)], [Log Space Used (%)], [Status] fields.

Once you have identified the typical maximum size of your log file, I would shrink the log file as small as you can, then set the growth of the log to an amount of MB's that would be reasonable for your particular log size, then set the size of the log above the typical maximum size you got from the logging job.

For instance, our log is now 125GB, and I've set it to grow in 8GB chunks to keep the virtual log file size down.

share|improve this answer

What is your database's recovery model? If FULL you can Shrink(Back up with no log). if recovery model is simple, SQL manage the transaction log file.

You can shrink log file successfully once the transaction log was commit(Full recovery model). As far as i know, i have concern once your transaction log file get auto grow again, it might takes time to reserve space. You might get problem IO overhead.

share|improve this answer
3  
-3 (a) Shrinking the log is not the same as backing up with NO_LOG; (b) BACKUP LOG ... WITH NO_LOG was removed in SQL 2008; (c) dropping the log on the floor is rarely a good idea anyway (using the methods to do it that remain). –  Jon Seigel Jul 17 '12 at 1:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.