October 23, 2011 at 9:38 am
Grant Fritchey (10/23/2011)
I just hope the vendor isn't one near & dear to my heart.
Yeah, there are lots of reasons to use a third party backup method. For example, we have better compression than native. Plus we offer compression in versions of SQL Server that don't do it natively. Add in stuff like storage compress, virtual restore... lots of reasons to use 3rd party software... assuming they work.
For the record it was not a RedGate solution 🙂
The vendor got back to me again to let me know that further testing in their labs has shown that this is a bug in thier solution. Maybe they are reading this thread? lol
October 23, 2011 at 9:40 am
kstjacques (10/23/2011)
The vendor got back to me again to let me know that further testing in their labs has shown that this is a bug in thier solution.
Gee, fancy 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
October 24, 2011 at 4:37 am
kstjacques (10/23/2011)
Grant Fritchey (10/23/2011)
I just hope the vendor isn't one near & dear to my heart.
Yeah, there are lots of reasons to use a third party backup method. For example, we have better compression than native. Plus we offer compression in versions of SQL Server that don't do it natively. Add in stuff like storage compress, virtual restore... lots of reasons to use 3rd party software... assuming they work.
For the record it was not a RedGate solution 🙂
The vendor got back to me again to let me know that further testing in their labs has shown that this is a bug in thier solution. Maybe they are reading this thread? lol
Good to hear.... then you should switch :w00t:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2011 at 1:29 pm
Good to hear.... then you should switch :w00t:
Hey, hey! No sales pitches! 😉
October 26, 2011 at 11:12 am
OK, vendor got back to me with the final fix/recomendation.... my co-woker is agreeing with the vendor on this and I am a Jr. DBA, so I need someone to set me straight. Maybe I didn't represent the story well enough in this thread so I will try again with more info.
Right now I have log backups running every 15 minutes for many DBs in my environment. This means there is a good chance it will happen at the same time as a full or diff. By default, unless otherwise specified in the script (.bch file *hint*), the vendor's software truncates the log when backed up. When this happens at the same time as a full/diff it fails. (the full/diff will also fail if the log is already backing up).
The vendor responded today saying I need to run log backups with the option to not truncate when I know there will be a full or diff occurring.
So instead of their software detecting this scenerio and handling it, it is up to me to create a script that detects if a full or diff is happening, and if so to call a different log backup script. And I guess that means I would also need to check for an active log backup before the diffs and full kick off.
When you are all saying that you run the log backup and full/diffs concurrently, are you specifying to the log backup that it shouldn't attempt to truncate? This is what I thought SQL Server handled.
Thank you again for all of your time and responses. I am at the end of my rope with this.
October 26, 2011 at 11:36 am
kstjacques (10/26/2011)
OK, vendor got back to me with the final fix/recomendation.... my co-woker is agreeing with the vendor on this and I am a Jr. DBA, so I need someone to set me straight. Maybe I didn't represent the story well enough in this thread so I will try again with more info.Right now I have log backups running every 15 minutes for many DBs in my environment. This means there is a good chance it will happen at the same time as a full or diff. By default, unless otherwise specified in the script (.bch file *hint*), the vendor's software truncates the log when backed up. When this happens at the same time as a full/diff it fails. (the full/diff will also fail if the log is already backing up).
The vendor responded today saying I need to run log backups with the option to not truncate when I know there will be a full or diff occurring.
So instead of their software detecting this scenerio and handling it, it is up to me to create a script that detects if a full or diff is happening, and if so to call a different log backup script. And I guess that means I would also need to check for an active log backup before the diffs and full kick off.
When you are all saying that you run the log backup and full/diffs concurrently, are you specifying to the log backup that it shouldn't attempt to truncate? This is what I thought SQL Server handled.
Thank you again for all of your time and responses. I am at the end of my rope with this.
Why are you running your own solution against the vendor's? I guess I had assumed that it was the vendor's software that was doing everything and creating its own conflicts. Can the vendor's software manage the tlog backups as well so that you do not have to do them?
Jared
Jared
CE - Microsoft
October 26, 2011 at 11:36 am
You don't need to truncate a log file if you back it up.
Per MSDN:
Note
After a typical log backup, some transaction log records become inactive, unless you specify WITH NO_TRUNCATE or COPY_ONLY. The log is truncated after all the records within one or more virtual log files become inactive. If the log is not being truncated after routine log backups, something might be delaying log truncation. For more information, see Managing the Transaction Log.
That's from the article on the Backup command (http://msdn.microsoft.com/en-us/library/ms186865.aspx).
The linked article, on Managing the T-Log, is at: http://msdn.microsoft.com/en-us/library/ms345382.aspx
The most important part is the link to data on Log Truncation: http://msdn.microsoft.com/en-us/library/ms189085.aspx
Which has this to say:
Log truncation under the full and bulk-logged recovery models
--------------------------------------------------------------------------------
Under the full recovery model or bulk-logged recovery model, the inactive part of the log cannot be truncated until all its log records have been captured in a log backup. This is needed to maintain the log chain—a series of log records having an unbroken sequence of log sequence numbers (LSNs). The log is truncated when you back up the transaction log, assuming the following conditions exist:
A checkpoint has occurred since the log was last backed up. A checkpoint is essential but not sufficient for truncating the log under the full recovery model or bulk-logged recovery model. After a checkpoint, the log remains intact at least until the next transaction log backup.
For more information, see Checkpoints and the Active Portion of the Log.
No other factor is preventing log transaction.
Generally, with regular backups, log space is regularly freed for future use. However, various factors, such as a long-running transaction, can temporarily prevent log truncation. For more information, see Factors That Can Delay Log Truncation.
The BACKUP LOG statement does not specify WITH COPY_ONLY.
So, normally, when you run a log backup, it truncates automatically.
In summary, turn off log truncation. You don't need it, don't want it, and it can (will) break your backup chain.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 12:34 pm
kstjacques (10/26/2011)
Right now I have log backups running every 15 minutes for many DBs in my environment. This means there is a good chance it will happen at the same time as a full or diff. By default, unless otherwise specified in the script (.bch file *hint*), the vendor's software truncates the log when backed up.
That's normal. When SQL backs a log up it truncates it as well, that's what makes the log space reusable
The vendor responded today saying I need to run log backups with the option to not truncate when I know there will be a full or diff occurring.
So they explicitly want a 'Backup Log with NOTRUNCATE' equivalent when there's a full or diff running? If so, that's garbage. SQL knows full well how to handle and manage the log around backups.
If using native backups, I can happily run normal log backups while a full backup is running with no error. If their software can't do the same, there's bug in their software that they need to fix.
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
October 26, 2011 at 12:44 pm
Gus, I don't see that, you want log truncation, otherwise the log will just continue to grow.
I think the vendor is still wrong here, their software should be able to correctly handle concurrent full and log backups, even when their log backups are not through their tool.
If the log backups you are doing are native log backups I guess at the moment you have two choices, schedule log backups to not occur during full backups, or do log backups through this tool, hopefully the tool would handle that correctly. If it cannot that is a major flaw.
If you only want the tool for compression, consider an upgrade to 2008R2, then it is available through the native backups.
edit: assuming you are not running 2008 enterprise, where it is already available
---------------------------------------------------------------------
October 26, 2011 at 12:52 pm
george sibbald (10/26/2011)
Gus, I don't see that, you want log truncation, otherwise the log will just continue to grow.
I believe this is wrong. When a log backup is created, everything before it gets marked inactive and the log can overwrite it. Logs work in a cyclic fashion where when they get to the end of the specified size they start overwriting at the beginning. They only grow if there is no inactive log to overwrite.
Jared
Jared
CE - Microsoft
October 26, 2011 at 12:57 pm
jared-709193 (10/26/2011)
george sibbald (10/26/2011)
Gus, I don't see that, you want log truncation, otherwise the log will just continue to grow.I believe this is wrong. When a log backup is created, everything before it gets marked inactive and the log can overwrite it. Logs work in a cyclic fashion where when they get to the end of the specified size they start overwriting at the beginning. They only grow if there is no inactive log to overwrite.
Jared
You just contradicted youself. thats what truncation is. Marking the inactive part for re-use. If its not truncated its not reusable so the log is likely to grow.
---------------------------------------------------------------------
October 26, 2011 at 12:59 pm
jared-709193 (10/26/2011)
george sibbald (10/26/2011)
Gus, I don't see that, you want log truncation, otherwise the log will just continue to grow.I believe this is wrong. When a log backup is created, everything before it gets marked inactive and the log can overwrite it.
And that is called Log Truncation.
p.s. Not everything before the log backup gets marked inactive. Log Truncation is the act of marking 0 or more active VLF as inactive.
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
October 26, 2011 at 1:15 pm
george sibbald (10/26/2011)
You just contradicted yourself. that's what truncation is. Marking the inactive part for re-use. If its not truncated its not reusable so the log is likely to grow.
"Log truncation frees space in the logical log by deleting inactive virtual log files form the start of the logical log." Sorry! it's been a long day and I misread your statement. I also understand what was incorrect about my statement in terms of what actually happens. Thanks for making me refresh my knowledge 🙂
http://technet.microsoft.com/en-us/library/ms189085.aspx
Jared
Jared
CE - Microsoft
October 26, 2011 at 1:19 pm
George: What you don't want is to just truncate the log file without a backup. If you do a backup, you don't need to truncate it, because the backup will do it for you. That was the point of my post (and was referenced several times in the quotes I posted).
Actually, it takes work to truncate a log file without running a backup on it, like setting the database to Simple Recovery, running DBCC ShrinkFile, and then setting it back to Full or Bulk Recovery. So I have to admit I'm curious as to what this backup package is doing that "truncates the log file when it runs a backup".
kstjacques: Most likely, if it's doing that, it's breaking your log chain anyway, and your log backups aren't doing you any good.
Have you tested point-in-time recovery with your current backup scheme?
Actually, now that I think about it, the error you're getting is probably caused by them "truncating the log file" by changing the recovery model back and forth before they run their full backup, and then you try to run a log backup and SQL Server detects the broken chain and throws an error because of it.
Is that truncate what they're asking you to turn off?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 1:25 pm
GSquared (10/26/2011)
Actually, it takes work to truncate a log file without running a backup on it, like setting the database to Simple Recovery, running DBCC ShrinkFile, and then setting it back to Full or Bulk Recovery.
Just switching to Simple recovery will break the log chain immediately and once in Simple a checkpoint will truncate the log.
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 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply