July 29, 2004 at 8:46 am
In our database server, we follow the below backup policy.
SQL Server 7.0 on Windows NT
DB options:
No trunc. log on chkpt enabled and there is no auto shrink option enabled for the database.
After Database reindexing, there is a nightly backup of the database.
During the day, transaction log backup is run every 2 hours.
All these are created using the Database Maintenance Wizard.
Here is the problem, the transaction log has grown, the log did not get truncated as it should have. We have checked for long running active transactions. but none.
So we have truncated the log file manually using the dbcc shrikfile command. And it has shrunk from 4 GB to 187 MB.
187 MB being too low we have increased the size of the transaction log manually to 500 mb. But every time the transaction log backup happens the file is brought to 187MB again.
Even now all the auto shrink options are disabled for the database.
Is there anything we are missing here. Help is highly appreciated.
July 29, 2004 at 2:59 pm
The culprit is the nightly reindexing job. Reindexing is a logged operation and will cause your t-log to grow.
Also don't confuse log truncation with file shrinking operations. They are not the same things. Truncating the log only happens during a backup of the log (assuming you don't have trunc log on checkpoint turned on) but it does not attempt to shrink the file size.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 30, 2004 at 1:40 am
Truncating log is in no way related to shrinking of log file.
Here the question is, even if the auto shrink options are disabled for the database, how is file getting shrunk every time the scheduled backup job for transaction log is executed.
help would be highly appreciated.
Thanks
guru
July 30, 2004 at 8:32 am
Truncating and shrinking the log file ARE related, but they are not the same thing.
I thought the problem was that the log was getting too large after the nightly reindexing etc... But your last post makes it sound as if the problem is that the log file shrinks inexplicably (presumably as part of the nightly maintenance)?
If that is the case, check to be sure that the maintenance plan on the Optimizations tab to see if the "Remove unused space from database files" option is checked.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
August 1, 2004 at 8:03 pm
No I have checked that option of "Removed Unused space from database file" and it is unchecked.
Here is the scenario:
Nightly backup job(all days except sundays) - Database backup full.
Hourly transaction log backup job(2 hours) from 8:00 to 18:00 PM EST.
It is during the hourly trascation log backup job that the log file gets shrunk.
Even after manually increasing the size of the file to 500MB it comes back to 187MB. Becuse of that log is filling up.
To avoid that right now we have reduced time between Transaction log backup from 2 hours to 1 hour.
Even then % space occupied is high 60%.
I know this is not the correct way to do it. We want to do that with out adding secondary log file.
Hope I got it right this time.
August 2, 2004 at 8:46 am
That sounds very strange, the only thing I can think of that might cause that is the job definition. Open up the job that runs the transaction log backups and look at the job step(s) very carefully. If, at some point someone manually modified a jobstep or added one, making changes to the maint plan will not necessarily negate those manual changes. In particular look to see if there is a step that does a DBCC SHRINKFILE, or calls the xp_sqlmaint and uses the -RmUnusedSpace parameter.
Also, check all of your other jobs to be sure that it wasn't done as part of a seperate job.
That's about all I can think of...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply