Sunday, September 19, 2010

When your SQL log (ldf) file gets Huge...

If you're having problems with large SQL log files, then this was the most useful advice I found.

For my purposes I was a "no" - my data wasn't money sensitive... so I switched to "simple" and could then shrink my database using the Tasks right-click menu in SSMS


Do you make transaction backups?
Yes -> Do not shrink the log unless you have done some exceptional operation (such as a massive delete) as the "cost" of SQL Server re-growing the Log file is significant, and will lead to fragmentation and thus worse performance.
No and I don't want to -> Change the Recovery Model to "Simple" - Enterprise Manager : Right click database : Properties @ [Options]
Don't know -> Transaction log backups allow you to recover to a "point in time" - you restore the last Full Backup (and possibly a subsequently Differential backup) and then every log backup, in sequence, until the "point in time" that you want to roll-forward to.


No comments:

Post a Comment