January 28, 2013 at 10:32 pm
Hi All,
I am aware that shrinking leads to fragmentation and doing it often is a very bad practice. Shrinking mdf / ndf files leads to fragmentation of data, but what about shrinking the ldf file? Is it a bad practice as well? If yes, please let me know what are bad effects that we get because of shrinking ldf files often
We do it atleast once a week to make space available in the drive (atleast after the rebuild job completes)
Thanks.
January 29, 2013 at 12:59 am
Keep transaction log large enough to fit regular operations without any growth. That includes index rebuilds. You can use bulk logged mode during rebuild to minimize log size. Log growth stops all write activity until new area is zeroed-out. That's one reason not to shrink, because it will grow again and stop write activity.
January 29, 2013 at 1:38 am
Yes, it is a bad practice. What are you gaining by shrinking the log each week? You know it's just going to grow back, so it's not as if you can use the reclaimed space for anything else. Why not just leave it in the log file?
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2013 at 11:38 pm
The reason for shrinking the log is
"a drive could have log files of multiple databases. If, one log file has over grown due to a big transaction , the other log files may face space issue. Hence, I think it makes sense, whenever we get an alert stating that "F drive is 90% full", to shrink the over grown log files, so that other log files has space to grow, when needed."
That being said, i also agree that if a database log file keep growing frequently, it makes sense to keep the log file in a separate drive so that we don't frequently do shrinking..
January 30, 2013 at 12:16 am
As Gail has said yes, i would caveat that with the in an emergancy on a critical system then you may have no choice but to shrink in order to get some breathing space until you can get more diskspace assigned on a SAN or move the logs to a didicated drive.
I would ask question about what the Log backup strategy on the databases are, as it sounds like you could do with setting up more regular Transactional Log backups, maybe starting at 1/hour intervals on the higher throughput DBs and 3 hours on the lower ones, monitor and fine tune as required.
The reason for this is that, if I remember correctly, the Log Backup process will flush any old checkpoints and free up the space they used in the log file thus preventing too much growth unless someone issues a large transaction.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 30, 2013 at 12:31 am
Trasaction log in its nature can take-u space it needs rapidly, eg. 20gb in few minutes. It needs to be on a drive with enough free space for such sudden growths, not on drives with low free space. And better size it properly initially, not to fool yourself you have a 1gb log when you know it regularly grows to 10gb.
January 30, 2013 at 12:44 am
karthik.catchme (1/29/2013)
The reason for shrinking the log is"a drive could have log files of multiple databases. If, one log file has over grown due to a big transaction
If the log grows to a particular size every week, that's not over growing due to some rogue operation, that's the size it needs to be for regular operation. Better to leave it that size, then you know what your drive space requirements really are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2013 at 10:07 am
Yes, shrinking log file routinely or unnecessarily is bad.
But A log shrink after an unusually extreme amount of activity would not be.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 31, 2013 at 3:58 pm
I also found the below lines from "http://support.microsoft.com/kb/315512"
•If you combine the autogrow and autoshrink options, you might create unnecessary overhead. Make sure that the thresholds that trigger the grow and shrink operations will not cause frequent up and down size changes. For example, you may run a transaction that causes the transaction log to grow by 100 MB by the time it commits. Some time after that the autoshrink starts and shrinks the transaction log by 100 MB. Then, you run the same transaction and it causes the transaction log to grow by 100 MB again. In that example, you are creating unnecessary overhead and potentially creating fragmentation of the log file, either of which can negatively affect performance.
The above underlined states that log file could get fragmented. Does log files get fragmented? We say MDF files get fragmented because the pages are not sequentially ordered. (page 56 and 57 does not sit beside). But how about the log files? does the same scenario apply to log files as well..
January 31, 2013 at 4:36 pm
karthik.catchme (1/31/2013)
I also found the below lines from "http://support.microsoft.com/kb/315512"•If you combine the autogrow and autoshrink options, you might create unnecessary overhead. Make sure that the thresholds that trigger the grow and shrink operations will not cause frequent up and down size changes. For example, you may run a transaction that causes the transaction log to grow by 100 MB by the time it commits. Some time after that the autoshrink starts and shrinks the transaction log by 100 MB. Then, you run the same transaction and it causes the transaction log to grow by 100 MB again. In that example, you are creating unnecessary overhead and potentially creating fragmentation of the log file, either of which can negatively affect performance.
The above underlined states that log file could get fragmented. Does log files get fragmented? We say MDF files get fragmented because the pages are not sequentially ordered. (page 56 and 57 does not sit beside). But how about the log files? does the same scenario apply to log files as well..
The log file allocations by the disk subsystem could be fragmented (i.e. not be contiguous), as with any other disk file which is increased later in size.
For example, say you initially allocate 100MB to the log file. Typically many disk subsytems will make that space contiguous, that is, all 100MB will be one single "chunk" of disk space. That is best for a given file's performance, and the disk system "knows" that, so it will try to make the first allocation contiguous (not always possible, and some disk systems may not do this, or with SAN it may be less applicable).
Now let's say you shrink the 100M back to 10M. Still good so far: all contig. Then you allocate 2M more. That new space is very likely not going to be contiguous / next to the original disk space, so you've fragmented the file. By the time the log again reaches 100M, it's completely fragmented across the disk.
Log files also have their own logical pieces, called "virtual logical files" (VLFs for short). Too many VLFs can cause performance overhead and/or "fragment" (I guess you could call it) by having too many very small VFLs.
Say the log grows by 2M. SQL divides each new such log allocation into 4 VLFs (the # of VLFs can vary depending on how much space is added). So each is 500K. If the log gets to, say, 500M -- by adding 2M at a time, with 4 VLFs each time -- you've got a 1000+ VLFs, which will hurt performance as well.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 1, 2013 at 3:39 am
Thanks a lot for detailed explanation
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply