SQL transaction log growth during maintenance plan
I have a reasonably large database (25GB) with a backup plan:
Full database backup daily at 10pm
Transaction log backup every 30minutes between 8am - 9.30pm
Additionally there is a maintenance plan which runs at midnight.
When the maintenance plan runs the transaction log grows to about 6GB.
Why is this? Could it/should it be avoided?
When the transaction log backups start in the morning, I "think" the log is cleared down (when I checked at 9am it was 98% free space) but after several hours (a few more transaction log backups) it eventually shrinks back to 10MB (and will remain roughly this size throughout the day).
Why is this? Could it/should it be avoided?
Thanks
Lee
Full database backup daily at 10pm
Transaction log backup every 30minutes between 8am - 9.30pm
Additionally there is a maintenance plan which runs at midnight.
When the maintenance plan runs the transaction log grows to about 6GB.
Why is this? Could it/should it be avoided?
When the transaction log backups start in the morning, I "think" the log is cleared down (when I checked at 9am it was 98% free space) but after several hours (a few more transaction log backups) it eventually shrinks back to 10MB (and will remain roughly this size throughout the day).
Why is this? Could it/should it be avoided?
Thanks
Lee
ASKER
Check Database Integrity
Reorganize Indexes
Update Statistics
Shrink Database
Clean Up History
And the option is set to "Return freed space to the operating system".
Reorganize Indexes
Update Statistics
Shrink Database
Clean Up History
And the option is set to "Return freed space to the operating system".
As you backup your transaction log every half an hour, it's probably at very low size all over the day, as it get shrinked after the backup.
However, when you run your maintenance plan, lots of operations get made and are pushed to the log.
I would try to run the maintenance plan operations separately seeing the growth of the transaction log between each one of them. If there is any of the operations specially hard with operations on transaction log I would try to make a log backup after that operation.
However, when you run your maintenance plan, lots of operations get made and are pushed to the log.
I would try to run the maintenance plan operations separately seeing the growth of the transaction log between each one of them. If there is any of the operations specially hard with operations on transaction log I would try to make a log backup after that operation.
ASKER
I think I'm more looking for a best practice solution? It almost makes sense why it is happening (although it definitely doesn't seem right- surely SQL is designed better than that!?). This would be a common issue and there must be a common solution/best practice approach?
L
L
Sincerely I don't know, I'm not a dba, only have some knowledge of it.
You might rethink the reorg indices nightly. They might be happy with a weekly index maintenance depending on the database volatility.
And regardless, don't worry about shrinking the database each night. You need sufficient disk space for the max size each day anyway, so you might as well let it stabilize at the larger size.
And regardless, don't worry about shrinking the database each night. You need sufficient disk space for the max size each day anyway, so you might as well let it stabilize at the larger size.
ASKER
Thanks Bardobrave
mastoo: can you state a source? i think what you're saying is probably right but was hoping for more reasoning/documented practice.
I have found scripts in the past which identify certain indexes based on the number of pages and fragmentation- so it might be that i need to drop the maintenance plan and actually run my own custom script to only process the tables/indexes which need to be improved.
L
mastoo: can you state a source? i think what you're saying is probably right but was hoping for more reasoning/documented practice.
I have found scripts in the past which identify certain indexes based on the number of pages and fragmentation- so it might be that i need to drop the maintenance plan and actually run my own custom script to only process the tables/indexes which need to be improved.
L
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is SHRINK step - it decreases LOG SIZE.