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

marked as duplicate by Max Vernon, Paul White, Mark Storey-Smith, Taryn, 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.

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

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

  • 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

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.


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.

  • 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.