January 20, 2012 at 8:46 am
We got an email alert that our database drive only had 3 GB free space on it at 01:19 AM this morning and then another that all was well at 04:52 AM and when I checked this morning there was almost 19 GB free space.
Looking at the job log I found the nightly re-index was running during that time span so I suspected it was bloating the tran logs.
We have the database files on one drive, the log files on another drive and the backups going to a third drive.
Each half hour a maintenance job runs to backup the tran logs to the backup drive.
At the time the database drive dipped so low the tran log backups were bigger than any other time of the day, probably because of the re-indexing, although not big enough to account for 15+ GB.
The question is - when backing up the tran log to a drive other than where the database is does the DBMS first do it to the drive the database is on and then move the file to the other drive?
January 20, 2012 at 8:55 am
No it does it straight to the destination path.
January 20, 2012 at 9:04 am
Backup only takes place on the specified location which is mentioned in the backup query.
January 20, 2012 at 9:16 am
Thanks, I would have guessed so but I'm grasping at straws at this point so it seemed worth asking.
Back to the drawing board . . .
January 22, 2012 at 5:14 am
Do you have auto-shrink turned on with any of your databases?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 22, 2012 at 6:33 am
Grant Fritchey (1/22/2012)
Do you have auto-shrink turned on with any of your databases?
Likelier a shrink in the maintenance plan.
January 23, 2012 at 6:09 am
Good call, I checked and there were two that were set to auto-shrink.
I set the auto-shrink flag to false on those so now I'll wait and see if one of those two balloons up so I can see what is going on.
There are 12 vendor installed databases and two were set to auto-shrink and I know it wasn't done by myself or one of our other DBA's.
The model database has the auto-shrink flag set to false so the vendor must have this set in their script that creates the database.
January 23, 2012 at 6:15 am
I checked the maintenance plans and didn't see a shrink on any of them.
January 24, 2012 at 8:15 am
I think I solved this mystery after using the tip of checking the auto shrink setting.
The vendor had a 'smart' re-index script that did a rebuild of the fragmented indexes each night.
From my research I found that the auto shrink actually undid the de-frag and re-fragmented the indexes!
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"...say you rebuild your indexes (which will requires free space in the database), and then shrink the database. The shrink will essentially undo the index rebuild..."
The next night with the database shrunk again it did a rebuild which caused the database to grow again:
"...Index rebuild works by re-creating the index internally again and when that has been achieved, it drops the existing index"
To fix I think we need to use a reorg vs a rebuild but I'll have to work with the vendor on that because it is their code and they'll have to fix it.
Thanks for tip that helped me figure this out!
January 24, 2012 at 8:23 am
Generally speaking a shrink of the database should not be performed under usual circumstances for this reason. Find the route cause and go from there vs a shrink of the file.
Shrinking a databases or shrinking a file (exlcuding a shrink with the truncate option) will reorganise your pages and this will cause fragmentation and reduce concurrency as locks will be used to do this.
Also, each transaction that requires extra space not available in a data or log file will suffer degraded performance whilst the file expands, not as fast as you might think and depends on your autogrowth settings.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply