May 21, 2014 at 10:59 am
In a SQL Server 2008 R2 standard edition instance, a nightly backup has failed twice this week. Reviewing the error message it was blowing out on space. Subsequent review showed that the database log file had grown significantly. So much so it was using at least 8-10 GB. Initially I manually released the space - that worked fine. Now its happened a second time.
The recovery model is Simple. I thought with this option changes would be committed immediately and not save them to the log. BTW - full backups are done nightly.
I am considering building a script to check the log file space allocation and available free space. If both are large, shrink the log file. That would be run right before the full backup. Any thoughts on this?
Comments / URLs are appreciated...
May 21, 2014 at 11:54 am
If the database is in Simple Recovery model and the log is growing out of control that means there is a long-running transaction that doesn't allow the a checkpoint to mark VLF's as available to be re-used. I'd spend my time figuring out what is causing the log file to grow and then either batch that into smaller transactions. Often times an index rebuild is what causes the log to grow.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 22, 2014 at 3:26 am
The recovery model is Simple. I thought with this option changes would be committed immediately and not save them to the log. BTW - full backups are done nightly.
This is not how the log works. Everything is written to the log file first, regardless of recovery model.
I agree with Jack Corbett. Find out what is causing the log file to grow. If it keeps growing it may be that it needs to be that big.
May 22, 2014 at 5:45 am
Totally agreed with everybody. Though db is in SIMPLE recovery reindex will surely affect the log size. If not check for any long open transaction.
By the way what is the data and log file size?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 22, 2014 at 5:50 am
a large log file in itself will not lead to a larger backup as only enough of the log is backed up to allow for recovery during a restore. Therefore you must have a large active transaction in play at the time the backup runs.
Do not attempt to shrink the log, it will not remove space anyway as it will be in use by the active transaction.
Look for the offending query and fix that or reschedule it, do a one off log shrink if you have to, but do not repeatedly shrink it if it is just going to grow again through normal use.
---------------------------------------------------------------------
May 22, 2014 at 6:24 am
george sibbald (5/22/2014)
a large log file in itself will not lead to a larger backup as only enough of the log is backed up to allow for recovery during a restore. Therefore you must have a large active transaction in play at the time the backup runs.Do not attempt to shrink the log, it will not remove space anyway as it will be in use by the active transaction.
Look for the offending query and fix that or reschedule it, do a one off log shrink if you have to, but do not repeatedly shrink it if it is just going to grow again through normal use.
George,
Good point about the full backup not necessarily being affected by the size of the log file.
I understood the OP to have said that the shrink worked, so odds are the long running transaction causing the log to grow had completed so there was a lot of available space in the log file that could be reclaimed.
Definitely need to find out the root cause of the growth in order to limit it, or just leave the log file that big since it'll just continue to grow,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 22, 2014 at 6:26 am
Hmmm... guessing.... The log file and the backup files are on the same drive?
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 22, 2014 at 6:28 am
Jack Corbett (5/22/2014)
george sibbald (5/22/2014)
a large log file in itself will not lead to a larger backup as only enough of the log is backed up to allow for recovery during a restore. Therefore you must have a large active transaction in play at the time the backup runs.Do not attempt to shrink the log, it will not remove space anyway as it will be in use by the active transaction.
Look for the offending query and fix that or reschedule it, do a one off log shrink if you have to, but do not repeatedly shrink it if it is just going to grow again through normal use.
George,
Good point about the full backup not necessarily being affected by the size of the log file.
I understood the OP to have said that the shrink worked, so odds are the long running transaction causing the log to grow had completed so there was a lot of available space in the log file that could be reclaimed.
Definitely need to find out the root cause of the growth in order to limit it, or just leave the log file that big since it'll just continue to grow,
yes the long running transaction must have completed by the time the OP manually shrunk the log. Placing a shrink into the backup job will likely not work though if thats when the transaction is running, and is counter productive anyway for all the reasons covered thousands of times before.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply