May 11, 2012 at 1:04 pm
My understanding was that the transaction log will be cleared on a backup.
I see that some transaction logs are cunsuming way too much of our available storage.
Is my understanding incorrect?
Is there a setting that I am missing?
We do daily full backups.
Thanks..
May 11, 2012 at 1:06 pm
Your understanding is not correct. Full backups do nothing to the transaction log.
Please read through this - Managing Transaction Logs[/url] and this: 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 11, 2012 at 1:06 pm
Full backups and differential backups do nothing to clear the transaction logs. To do that you need to run scheduled transaction log backups.
Please read the last article I reference below in my signature block for more information.
May 11, 2012 at 1:48 pm
Okay, part of what I read states:
"In full recovery model transaction log entries cannot be overwritten until they have been backed up by a transaction log backup....
"Without any log backups running (an all-too common occurrence seeing that new databases will default to full recovery model unless the recovery model of the Model database has been changed), the transaction log will grow until it reaches its configured maximum file size (2TB unless otherwise specified) or until it fills the disk. No amount of full or differential backups will allow the log space to be reused as neither marks log space as reusable."
Other parts state:
"The frequency that log backups should be done is dependent on two considerations:
-The maximum amount of data that can be lost in the case of a disaster
-The size to which the log can grow. "
"In general, the transaction log should not be shrunk. It certainly should never be shrunk on a regular basis in a job or maintenance plan."
So, I'm confused. I have a 6 GB database with a 25GB transaction log. I do nightly, full backups of the database. Shoudn't I only need a transaction log big enough to hold one day's transactions??
What can be done about the current large trasaction logs?
What should be done to prevent them from growing so large?
Thanks for your help.
May 11, 2012 at 1:50 pm
Lynn Pettis (5/11/2012)
Full backups and differential backups do nothing to clear the transaction logs. To do that you need to run scheduled transaction log backups.Please read the last article I reference below in my signature block for more information.
Thanks, I'll read the link.
We do nightly scheduled transaction logs backups .
May 11, 2012 at 1:52 pm
What's confusing?
What should be done to prevent them from growing so large?
That's covered in the articles, read the first one again.
Shoudn't I only need a transaction log big enough to hold one day's transactions??
No. You said you're only doing full database backups. From the article:
Without any log backups running (an all-too common occurrence seeing that new databases will default to full recovery model unless the recovery model of the Model database has been changed), the transaction log will grow until it reaches its configured maximum file size (2TB unless otherwise specified) or until it fills the disk. No amount of full or differential backups will allow the log space to be reused as neither marks log space as reusable."
Full backups do not mark the log as reusable. Only log backups do that.
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 11, 2012 at 1:59 pm
inevercheckthis2002 (5/11/2012)
Lynn Pettis (5/11/2012)
Full backups and differential backups do nothing to clear the transaction logs. To do that you need to run scheduled transaction log backups.Please read the last article I reference below in my signature block for more information.
Thanks, I'll read the link.
It's the same as the one I gave you
We do nightly scheduled transaction logs backups .
Log backups or full backups? You stated earlier:
We do daily full backups.
and
I do nightly, full backups of the database.
So, which is it, a nightly full database backup or nightly log backups?
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 11, 2012 at 2:07 pm
GilaMonster (5/11/2012)
So, which is it, a nightly full database backup or nightly log backups?
Both.
So, since we do nightly log backups, if I look at backups of the server should I see the transaction log size remain fairly constant?
Thanks...
May 11, 2012 at 2:15 pm
In a production environment, I do a FULL or DIFF every day, and Transaction log backups every 20 minutes. That gives pretty good disaster recovery, and prevents the transaction logs from growing too big.
If you only backup your transaction log once a day, then you are at risk for data loss, and it may grow very big depending on activity.
May 11, 2012 at 2:16 pm
Log backups once a day are not very useful. The main point of log backups is to allow point-in-time recovery, taking one a day means you could lose up to 24 hours of data. If that's acceptable, might as well switch to simple recovery and just take full backups.
How much data loss is acceptable?
If you are backing the log up once a day, then the transaction log should stabilise, but far bigger than likely needed.
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 11, 2012 at 2:31 pm
GilaMonster (5/11/2012)
Log backups once a day are not very useful. The main point of log backups is to allow point-in-time recovery, taking one a day means you could lose up to 24 hours of data. If that's acceptable, might as well switch to simple recovery and just take full backups.How much data loss is acceptable?
If you are backing the log up once a day, then the transaction log should stabilise, but far bigger than likely needed.
Thanks - Yes, I quickly realized that our backup strategy is not right.
Also, the topic of this thread "transaction logs that grow.." was based on my boss coming in and asking me why are transaction logs are growing. I should have checked, first:
[font="Courier New"]date--------size
01-Apr-----9244508160
08-Apr-----9244508160
15-Apr-----9244508160
22-Apr-----9244508160
29-Apr-----9244508160
01-May-----9244508160
02-May----26376011776
06-May----26376011776[/font]
I'll correct the backups, but I still need to figure out what to do to reclaim the disk space.
Thanks for the discussion, it's been an eye opener. Any further advice is quite welcome.
May 11, 2012 at 2:46 pm
If the log is mostly empty, then a once-off shrink is fine.
You should probaby investigate and see if you can find out what happened at the time that the log grew so large. Large loads, large deletes, index rebuilds, etc.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply