1

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

Improve this question
1
  • 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
    Commented Feb 15, 2012 at 22:15

2 Answers 2

Reset to default
1

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.

Improve this answer
-2

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.

Improve this answer
1
  • 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
    Commented Jul 17, 2012 at 1:11

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