May 24, 2011 at 7:28 am
After seeing a 40GB Log file on my instance I figured nobody had ever set up maintenance plans however after looking it appears the log file is being backed up every 4 hours.
My question is this... In SQL Server 2000 does a scheduled backup also run a DBCC Shrink?
What interesting is that 6 transaction log backups from yesterday were all a few hundred MB in size. However when I browsed over to the data folder I saw a 40 GB Log file sitting on the server. I then waited until 0800 to see what would happen after the scheduled log backup and sure enough the LDF file went from 40 GB down to 250 MB! So am I right in thinking that the log file is getting filled to 40 GB in a matter of 4 hours or am I missing something?
May 24, 2011 at 7:32 am
Vertigo44 (5/24/2011)
My question is this... In SQL Server 2000 does a scheduled backup also run a DBCC Shrink?
No.
If the log file is reducing in size you either have autoshrink on, or there's a shrink task. Both are very bad things to have. The log should not be shrunk regularly.
If the ldf is 40GB then it needs to be that size for database operations and the current frequency of log backups. My guess is that you're rebuilding all indexes at some point, that's a logged operation (fully logged in full recovery) and hence will result in a lot of log activity.
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
May 24, 2011 at 7:41 am
Thanks Gail!
I am not as familiar with Enterprise Manager but I was able to find the Database Maintenance Plan and there is a tab called optimizations that does have 'reorganize data and index pages' checked and change free space per page % to 10%. There is also 'Remove unused space from database files and shrink db with it grows beyond 50 MB?? (Does that sound right? 50 MB?)... Amount of free space to remain after shrink 10% of the data space. The integrity tab has checked 'check db integrity and include indexes'.
Does anything stand out as the culprit?
GilaMonster (5/24/2011)
Vertigo44 (5/24/2011)
My question is this... In SQL Server 2000 does a scheduled backup also run a DBCC Shrink?No.
If the log file is reducing in size you either have autoshrink on, or there's a shrink task. Both are very bad things to have. The log should not be shrunk regularly.
If the ldf is 40GB then it needs to be that size for database operations and the current frequency of log backups. My guess is that you're rebuilding all indexes at some point, that's a logged operation (fully logged in full recovery) and hence will result in a lot of log activity.
May 24, 2011 at 7:46 am
Vertigo44 (5/24/2011)
There is also 'Remove unused space from database files and shrink db with it grows beyond 50 MB?? (Does that sound right? 50 MB?)...
There's your database shrink. Turn that option off, it's doing serious nasty things (massive fragmentation of all indexes, file level fragmentation, causing immediate file grows, etc)
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
May 24, 2011 at 8:33 am
GilaMonster (5/24/2011)
Vertigo44 (5/24/2011)
There is also 'Remove unused space from database files and shrink db with it grows beyond 50 MB?? (Does that sound right? 50 MB?)...There's your database shrink. Turn that option off, it's doing serious nasty things (massive fragmentation of all indexes, file level fragmentation, causing immediate file grows, etc)
Ok I turned that off! Now will the Reorganize data and index pages undo the fragmentation or is this an issue I need to jump into and address manually?
Also, I've been watching the log file on the server since 8am and it has not grown 1 kb!?! I'm still trying to figure out how it was reaching 40GB.
May 24, 2011 at 8:40 am
reindex operations are logged. Figure 1.3 to 1.5X the base data and that's how big the log needs to be (on top have whatever else is going on).
May 24, 2011 at 8:41 am
Gail is correct, the shrink is bad, and the reorganize will rebuild indexes. You can read here:
http://msdn.microsoft.com/en-us/library/ms188964.aspx
When that runs, it will require space in the database, and it will log all the activity.
I'd just watch it for a few days and see to what extent the log grows. Note that the log isn't fragmented, just the data.
May 24, 2011 at 8:55 am
Steve Jones - SSC Editor (5/24/2011)
Gail is correct, the shrink is bad, and the reorganize will rebuild indexes. You can read here:http://msdn.microsoft.com/en-us/library/ms188964.aspx
When that runs, it will require space in the database, and it will log all the activity.
I'd just watch it for a few days and see to what extent the log grows. Note that the log isn't fragmented, just the data.
Sounds good Steve. I am running perfmon on it now watching the avg disk read,write,xfer also b/c I found in the sql error logs "SQL Server has encountered 5 occurrences of IO requests taking longer than 15 secs to complete on the same database that had the large log file. The databse is about 110 GB in size right now btw.
So are you all saying that the LOG file should be generally 40 GB in size? I thought the log keeps a record of all transactions until it is backed up which then resets its size back down to a few MB and then builds back up again until the next log backup? Is this not correct?
May 24, 2011 at 9:02 am
No the size of the log on the file system should basically never change unless you see a problem down the line and decide to grow it.
The free space in the log should be at ±100% after a log backup, but the filesize won't change.
May 24, 2011 at 9:31 am
Ninja's_RGR'us (5/24/2011)
No the size of the log on the file system should basically never change unless you see a problem down the line and decide to grow it.The free space in the log should be at ±100% after a log backup, but the filesize won't change.
Ah yes now I remember that important piece. I am running the SP_SDS from
and can now see the log file slowly growing. Thanks Ninja!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply