May 8, 2011 at 11:20 pm
Hi There,
I've noticed that,, after the weekend maintenance job (Index rebuild) is completed for a database, the T-Log file become fully occupied. Even though we have a followed by Database Shrink job and T-Log backup job the T-Log committed part doesn't free up space to the Operating system.
After a day or so, when I manually it automatically shrinks the database log file.
Why is this so? I've noticed that there is NO Open Transactions in the database.
Thanks.
May 8, 2011 at 11:35 pm
The possibilities of not shrinking the log file is open transactions into the log. Have you checked for any open transactions?
To overcome this problem just delay shrink job by 1 or 2 hrs if possible.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 8, 2011 at 11:35 pm
Two steps back...
You have an index rebuild followed by a database shrink? What a waste of time.
The shrink will fragment the indexes more than they were before the rebuild happened. It's a complete waste of the time and resources that went into the rebuild.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Database or file shrinks should never be run on a regular basis.
Now, as for the log... http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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 8, 2011 at 11:41 pm
Database Shrink job doesn't run during the time of Rebuild Of the Indexes. The strategy is:
1. First the Database Bkp job runs.
2. Second the T-Log bkp job is kept stopped during the time maintenance window runs.
3. Index Reorg job runs.
4. Regular T-Log bkp job runs.
---------------------------
After the 4th step also, the Log file was not able to release space to Os. So, an additional Shrink 'LDF' job has been introduced which has been put in place to shrink the Log file.
Can't understand through this, how come it is a waste of time and how come it can fragment the rebuild index??? It is not being performed on Data file!!!!!
Thanks.
May 9, 2011 at 1:57 am
Sourav,
Gila said, "Total west of time and resource" means:
1. When next time you will run the maintenance task log will gain expand.
2. Expansion and Shrink operation of the data/log file is resource consuming process.
Hence suggested not to shrink the log file.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 9, 2011 at 2:02 am
In your first post you said 'database shrink', not log file shrink. Hence the comments on fragmentation
Shrinking the log is still a bad idea, for different reasons. Growing the log takes time, it cannot be instant initialised. If the autogrow settings are too low, you get lots and lots of VLFs, slowing down log backups, recovery, rollbacks and anything else that reads the log.
Why do you need to repeatedly shrink it? Why not just leave the space in the log for the next maintenance run.
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 9, 2011 at 4:39 am
Sourav-657741 (5/8/2011)
Database Shrink job doesn't run during the time of Rebuild Of the Indexes. The strategy is:1. First the Database Bkp job runs.
2. Second the T-Log bkp job is kept stopped during the time maintenance window runs.
3. Index Reorg job runs.
4. Regular T-Log bkp job runs.
---------------------------
I don't understand why did you stop your log backup job during the maintenance window ?
May be you can help me to understand it better.
[Quote]
After the 4th step also, the Log file was not able to release space to Os.
[/Quote]
Log backup never releases the disk space to OS. It just truncates the inactive portion of log as a part of process and internally marks the VLFs as unused\inactive sothat database engine could use them again because of the cyclic nature of transaction log file.
[Quote]
So, an additional Shrink 'LDF' job has been introduced which has been put in place to shrink the Log file.
[/quote]
I don;t understand the rationale behind shrinking the log files unless you have some huge disk space crunch for which i personally believe that you must look for additional disk space rather than shrinking the log files.
Also, as gail pointed out shrinking of any mean(Data or log) degrades performance so we must not just continue with it as a part of our routine maintainence rather we must have some very justifiable reason behind it.
Regards,
Sachin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply