Failing Log Backups
in [Server Configuration]
|
From: Rubens on 23 Nov 2009 09:34 I have a rather concerning issue on our OLTP system for our largest database (1.7 TB's). On Friday (when I was on vacation of course), we had a power outage and the cluster failed. Everything was brought back online from the Network guys, and then our team of DBA's took over with making sure SQL was ok using the documentation we have. Since then, the log backups for the above database began to fail, but it still appears to be writing the log files. We do weekly full backups, followed by daily differentials. My hope was that after the full backup ran, everything would be fine with the log backups. Nope, they are still failing with the error message below... Processed 127550 pages for database 'Focus', file 'Focus2000_Log' on file 1. [SQLSTATE 01000] (Message 4035) Processed 0 pages for database 'Focus', file 'Focus2000_Log3' on file 1. [SQLSTATE 01000] (Message 4035) Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. [SQLSTATE 23000] (Error 2601) Failed to flush the commit table to disk in dbid 7 due to error 2601. Check the errorlog for more information. [SQLSTATE 42000] (Error 3999) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed. I was hoping that the weekend full backups would reset the database and then the log backups would run fine after. NOPE. Still having the same issue. And interestingly, or index rebuild job failed with the same error message for this database. SQL 2008 SP1, Enterprise Edition, active-active-active cluster on Windows 2003 R2 x64 SP2 Can someone offer any advice? Thanks, Rubens
From: Mark Broadbent on 23 Nov 2009 10:28 Not sure if this will help given the error message, but I do tend to find that logical disk corruption does cause odd behaviour sometimes. Therefore if you havent checked the disk why dont you try doing a:- chkdsk driverletter: ...from the command line for each drive letter on this server instance. This will give you a readonly checkdisk and should you get any errors reported then you need to offline the clustered resources apart from the physical drives and their dependancies and do a chkdsk driverletter: /f on each applicable. -- "Rubens" <rubensrose(a)hotmail.com> wrote in message news:0D1604E3-BB25-488E-8251-4BFF84C5A257(a)microsoft.com... >I have a rather concerning issue on our OLTP system for our largest >database (1.7 TB's). On Friday (when I was on vacation of course), we had >a power outage and the cluster failed. Everything was brought back online >from the Network guys, and then our team of DBA's took over with making >sure SQL was ok using the documentation we have. Since then, the log >backups for the above database began to fail, but it still appears to be >writing the log files. We do weekly full backups, followed by daily >differentials. My hope was that after the full backup ran, everything >would be fine with the log backups. Nope, they are still failing with the >error message below... > > Processed 127550 pages for database 'Focus', file 'Focus2000_Log' on file > 1. [SQLSTATE 01000] (Message 4035) Processed 0 pages for database > 'Focus', file 'Focus2000_Log3' on file 1. [SQLSTATE 01000] (Message 4035) > Cannot insert duplicate key row in object 'sys.syscommittab' with unique > index 'si_xdes_id'. [SQLSTATE 23000] (Error 2601) Failed to flush the > commit table to disk in dbid 7 due to error 2601. Check the errorlog for > more information. [SQLSTATE 42000] (Error 3999) The statement has been > terminated. [SQLSTATE 01000] (Error 3621). The step failed. > > I was hoping that the weekend full backups would reset the database and > then the log backups would run fine after. NOPE. Still having the same > issue. And interestingly, or index rebuild job failed with the same error > message for this database. > > SQL 2008 SP1, Enterprise Edition, active-active-active cluster on Windows > 2003 R2 x64 SP2 > > Can someone offer any advice? > > Thanks, > Rubens
From: Rubens on 23 Nov 2009 11:12 Unfortunately this option requires of the following to be dismounted, which I can't do right now. I can try that during our next reboot. We are also using the new ChangeTracking feature on the database in question. I am seeing articles online related to it... "Mark Broadbent" <nospam(a)nospam.com> wrote in message news:uD#GTFFbKHA.1652(a)TK2MSFTNGP05.phx.gbl... > Not sure if this will help given the error message, but I do tend to find > that logical disk corruption does cause odd behaviour sometimes. Therefore > if you havent checked the disk why dont you try doing a:- > > chkdsk driverletter: > > ..from the command line for each drive letter on this server instance. > This will give you a readonly checkdisk and should you get any errors > reported then you need to offline the clustered resources apart from the > physical drives and their dependancies and do a > > chkdsk driverletter: /f > on each applicable. > -- > > "Rubens" <rubensrose(a)hotmail.com> wrote in message > news:0D1604E3-BB25-488E-8251-4BFF84C5A257(a)microsoft.com... >>I have a rather concerning issue on our OLTP system for our largest >>database (1.7 TB's). On Friday (when I was on vacation of course), we had >>a power outage and the cluster failed. Everything was brought back online >>from the Network guys, and then our team of DBA's took over with making >>sure SQL was ok using the documentation we have. Since then, the log >>backups for the above database began to fail, but it still appears to be >>writing the log files. We do weekly full backups, followed by daily >>differentials. My hope was that after the full backup ran, everything >>would be fine with the log backups. Nope, they are still failing with the >>error message below... >> >> Processed 127550 pages for database 'Focus', file 'Focus2000_Log' on file >> 1. [SQLSTATE 01000] (Message 4035) Processed 0 pages for database >> 'Focus', file 'Focus2000_Log3' on file 1. [SQLSTATE 01000] (Message 4035) >> Cannot insert duplicate key row in object 'sys.syscommittab' with unique >> index 'si_xdes_id'. [SQLSTATE 23000] (Error 2601) Failed to flush the >> commit table to disk in dbid 7 due to error 2601. Check the errorlog for >> more information. [SQLSTATE 42000] (Error 3999) The statement has been >> terminated. [SQLSTATE 01000] (Error 3621). The step failed. >> >> I was hoping that the weekend full backups would reset the database and >> then the log backups would run fine after. NOPE. Still having the same >> issue. And interestingly, or index rebuild job failed with the same error >> message for this database. >> >> SQL 2008 SP1, Enterprise Edition, active-active-active cluster on Windows >> 2003 R2 x64 SP2 >> >> Can someone offer any advice? >> >> Thanks, >> Rubens > >
From: TheSQLGuru on 23 Nov 2009 23:49 Gotta say I am pretty stunned that you have failing backups and a potentially corrupted system on a 1.7TB OLTP system and you are hunting and pecking here on a forum for help? This should be zooming up the food chain at Microsoft customer support... -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Rubens" <rubensrose(a)hotmail.com> wrote in message news:0D1604E3-BB25-488E-8251-4BFF84C5A257(a)microsoft.com... >I have a rather concerning issue on our OLTP system for our largest >database (1.7 TB's). On Friday (when I was on vacation of course), we had >a power outage and the cluster failed. Everything was brought back online >from the Network guys, and then our team of DBA's took over with making >sure SQL was ok using the documentation we have. Since then, the log >backups for the above database began to fail, but it still appears to be >writing the log files. We do weekly full backups, followed by daily >differentials. My hope was that after the full backup ran, everything >would be fine with the log backups. Nope, they are still failing with the >error message below... > > Processed 127550 pages for database 'Focus', file 'Focus2000_Log' on file > 1. [SQLSTATE 01000] (Message 4035) Processed 0 pages for database > 'Focus', file 'Focus2000_Log3' on file 1. [SQLSTATE 01000] (Message 4035) > Cannot insert duplicate key row in object 'sys.syscommittab' with unique > index 'si_xdes_id'. [SQLSTATE 23000] (Error 2601) Failed to flush the > commit table to disk in dbid 7 due to error 2601. Check the errorlog for > more information. [SQLSTATE 42000] (Error 3999) The statement has been > terminated. [SQLSTATE 01000] (Error 3621). The step failed. > > I was hoping that the weekend full backups would reset the database and > then the log backups would run fine after. NOPE. Still having the same > issue. And interestingly, or index rebuild job failed with the same error > message for this database. > > SQL 2008 SP1, Enterprise Edition, active-active-active cluster on Windows > 2003 R2 x64 SP2 > > Can someone offer any advice? > > Thanks, > Rubens
From: Rubens on 27 Nov 2009 11:15
I'm a little stunned that you'd make the assumption that this was all I was doing. I already had a call into Microsoft when I posted this and was waiting for a call back, so I was looking to this group to provide me a little direction as I was researching the problem on my own. Thanks for your help Kevin. Rubens "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:4ISdnWvcFrOt-JbWnZ2dnUVZ_uednZ2d(a)earthlink.com... > Gotta say I am pretty stunned that you have failing backups and a > potentially corrupted system on a 1.7TB OLTP system and you are hunting > and pecking here on a forum for help? This should be zooming up the food > chain at Microsoft customer support... > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "Rubens" <rubensrose(a)hotmail.com> wrote in message > news:0D1604E3-BB25-488E-8251-4BFF84C5A257(a)microsoft.com... >>I have a rather concerning issue on our OLTP system for our largest >>database (1.7 TB's). On Friday (when I was on vacation of course), we had >>a power outage and the cluster failed. Everything was brought back online >>from the Network guys, and then our team of DBA's took over with making >>sure SQL was ok using the documentation we have. Since then, the log >>backups for the above database began to fail, but it still appears to be >>writing the log files. We do weekly full backups, followed by daily >>differentials. My hope was that after the full backup ran, everything >>would be fine with the log backups. Nope, they are still failing with the >>error message below... >> >> Processed 127550 pages for database 'Focus', file 'Focus2000_Log' on file >> 1. [SQLSTATE 01000] (Message 4035) Processed 0 pages for database >> 'Focus', file 'Focus2000_Log3' on file 1. [SQLSTATE 01000] (Message 4035) >> Cannot insert duplicate key row in object 'sys.syscommittab' with unique >> index 'si_xdes_id'. [SQLSTATE 23000] (Error 2601) Failed to flush the >> commit table to disk in dbid 7 due to error 2601. Check the errorlog for >> more information. [SQLSTATE 42000] (Error 3999) The statement has been >> terminated. [SQLSTATE 01000] (Error 3621). The step failed. >> >> I was hoping that the weekend full backups would reset the database and >> then the log backups would run fine after. NOPE. Still having the same >> issue. And interestingly, or index rebuild job failed with the same error >> message for this database. >> >> SQL 2008 SP1, Enterprise Edition, active-active-active cluster on Windows >> 2003 R2 x64 SP2 >> >> Can someone offer any advice? >> >> Thanks, >> Rubens > > |