SQL Server Backup failed... did NOT record failure in sql server log?

  • We DO have TraceFlag 3226 enabled.  However, as I understand, it only suppresses successful backup events?

    We are using Commvault agent to run backups on our SQL 2008 R2 box.

    A backup failed early in the morning, but no error was recorded in the SQL server error log itself.

    This is most perplexing to me.

    The only explanation is that perhaps the backup completed from SQL Server's point of view, but failed during Commvault's process of writing the actual .bak file to it's storage location?

    Scratching my head on any other way that would make sense?

    Thanks!

  • Maxer - Wednesday, May 9, 2018 1:50 PM

    We DO have TraceFlag 3226 enabled.  However, as I understand, it only suppresses successful backup events?

    We are using Commvault agent to run backups on our SQL 2008 R2 box.

    A backup failed early in the morning, but no error was recorded in the SQL server error log itself.

    This is most perplexing to me.

    The only explanation is that perhaps the backup completed from SQL Server's point of view, but failed during Commvault's process of writing the actual .bak file to it's storage location?

    Scratching my head on any other way that would make sense?

    Thanks!

    You may want to start by checking the backup history in msdb to see what SQL Server says about the backup. If it exists in msdb, then the backup succeeded. If it doesn't exist, it didn't succeed. Check the backupset table.

    Sue

  • Sue_H - Wednesday, May 9, 2018 1:55 PM

    Maxer - Wednesday, May 9, 2018 1:50 PM

    We DO have TraceFlag 3226 enabled.  However, as I understand, it only suppresses successful backup events?

    We are using Commvault agent to run backups on our SQL 2008 R2 box.

    A backup failed early in the morning, but no error was recorded in the SQL server error log itself.

    This is most perplexing to me.

    The only explanation is that perhaps the backup completed from SQL Server's point of view, but failed during Commvault's process of writing the actual .bak file to it's storage location?

    Scratching my head on any other way that would make sense?

    Thanks!

    You may want to start by checking the backup history in msdb to see what SQL Server says about the backup.

    Sue

    Good call, thanks!

    I dug through that, in this case there were no entries for the time period when the backups seemed to have failed.

    No backups recorded at all that night (supposed to be nightly done, just no entries for that date at all).

    I assume that means the backup jobs were never either:
    1. started AT ALL?
    OR
    2. started SUCCESSFULLY?  *command was issued, but failed for unknown reason??? 

    If it was case 2 though...the sql log would have recorded it... which is did not so?

    That tells us it was #1 - the backup command was never issued to begin with?

    That seem logical?

    Am I missing anything else obvious here?

    Thanks!

  • Maxer - Wednesday, May 9, 2018 2:11 PM

    Sue_H - Wednesday, May 9, 2018 1:55 PM

    Maxer - Wednesday, May 9, 2018 1:50 PM

    We DO have TraceFlag 3226 enabled.  However, as I understand, it only suppresses successful backup events?

    We are using Commvault agent to run backups on our SQL 2008 R2 box.

    A backup failed early in the morning, but no error was recorded in the SQL server error log itself.

    This is most perplexing to me.

    The only explanation is that perhaps the backup completed from SQL Server's point of view, but failed during Commvault's process of writing the actual .bak file to it's storage location?

    Scratching my head on any other way that would make sense?

    Thanks!

    You may want to start by checking the backup history in msdb to see what SQL Server says about the backup.

    Sue

    Good call, thanks!

    I dug through that, in this case there were no entries for the time period when the backups seemed to have failed.

    No backups recorded at all that night (supposed to be nightly done, just no entries for that date at all).

    I assume that means the backup jobs were never either:
    1. started AT ALL?
    OR
    2. started SUCCESSFULLY?  *command was issued, but failed for unknown reason??? 

    If it was case 2 though...the sql log would have recorded it... which is did not so?

    That tells us it was #1 - the backup command was never issued to begin with?

    That seem logical?

    Am I missing anything else obvious here?

    Thanks!

    No....sorry. I hit send before I finished and then added the rest to my post. My bad.
    If it's not in there, it didn't succeed. The tables in msdb only record successful backups. So the backups didn't happen for some reason. The error log will show failures if it gets as far as trying to do the backup. That's about as close as you can get to finding the failures. 

    Sue

  • Sue_H - Wednesday, May 9, 2018 2:17 PM

    Maxer - Wednesday, May 9, 2018 2:11 PM

    Sue_H - Wednesday, May 9, 2018 1:55 PM

    Maxer - Wednesday, May 9, 2018 1:50 PM

    We DO have TraceFlag 3226 enabled.  However, as I understand, it only suppresses successful backup events?

    We are using Commvault agent to run backups on our SQL 2008 R2 box.

    A backup failed early in the morning, but no error was recorded in the SQL server error log itself.

    This is most perplexing to me.

    The only explanation is that perhaps the backup completed from SQL Server's point of view, but failed during Commvault's process of writing the actual .bak file to it's storage location?

    Scratching my head on any other way that would make sense?

    Thanks!

    You may want to start by checking the backup history in msdb to see what SQL Server says about the backup.

    Sue

    Good call, thanks!

    I dug through that, in this case there were no entries for the time period when the backups seemed to have failed.

    No backups recorded at all that night (supposed to be nightly done, just no entries for that date at all).

    I assume that means the backup jobs were never either:
    1. started AT ALL?
    OR
    2. started SUCCESSFULLY?  *command was issued, but failed for unknown reason??? 

    If it was case 2 though...the sql log would have recorded it... which is did not so?

    That tells us it was #1 - the backup command was never issued to begin with?

    That seem logical?

    Am I missing anything else obvious here?

    Thanks!

    No....sorry. I hit send before I finished and then added the rest to my post. My bad.
    If it's not in there, it didn't succeed. The tables in msdb only record successful backups. So the backups didn't happen for some reason. The error log will show failures if it gets as far as trying to do the backup. That's about as close as you can get to finding the failures. 

    Sue

    Just to qualify....

    That's about as close as you can get when using the agents anyway. You can possibly find backup failures if they are executed with a SQL Server Agent job - by parsing through the job history. But that doesn't always work too well.

    Sue

  • Where I work, we also use Commvault for our SQL backups, and despite backups failing I've never seen a "backup failed" in the SQL logs.  Lots of "backup completed" but never a failed.

    What you need to do to find out what may have happened with your failed backup is work with the Comvvault admin to go through the Commvault logs to try to find the cause of the failure.  It could be anything from CV losing connection to the backup agent on the SQL server, a problem with the storage CV is using for the backups, or just CV being a P.I.T.A.  (As you might guess, I'm not a fan of CV for backing up my databases.)

    As Sue pointed out, the information in MSDB will only ever be successful backups.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply