SQL Server Agent Job – Failing with Event ID 208

Backup

Noticed that one of our scheduled SQL Server Agent jobs occasionally fail.

Error

Image

sqlserveragent-warning-cropped

 

Grid

Date Details
 2015-09-25 12:00 AM SQL Server Scheduled Job ‘DatabaseBackup – USER_DATABASES – LOG’ (0x75A296FCB336D04881A08C496B48C19D) – Status: Failed – Invoked on: 2016-09-25 00:00:00 – Message: The job failed.  The Job was invoked by Schedule 24 (Step 1).  The last step to run was step 1 (DatabaseBackup – USER_DATABASES – LOG).
2015-09-18 12:00 AM SQL Server Scheduled Job ‘DatabaseBackup – USER_DATABASES – LOG’ (0x75A296FCB336D04881A08C496B48C19D) – Status: Failed – Invoked on: 2016-09-18 00:00:00 – Message: The job failed. The Job was invoked by Schedule 24 (Step 1). The last step to run was step 1 (DatabaseBackup – USER_DATABASES – LOG).
2016-09-11 12:00 AM SQL Server Scheduled Job ‘DatabaseBackup – USER_DATABASES – LOG’ (0x75A296FCB336D04881A08C496B48C19D) – Status: Failed – Invoked on: 2016-09-11 00:00:00 – Message: The job failed. The Job was invoked by Schedule 24 (Step 1). The last step to run was step 1 (DatabaseBackup – USER_DATABASES – LOG).

 

 

Scheduled Job

Reviewed the scheduled job

Job

Here is the Job

job

Job Details

jobstep-userdatabaselog

 

Job Details – SQL Statement


sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d AdminDB -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'Z:\sqlbackups', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y',@Compress='Y',  @LogToTable = 'Y'" -b

Interpretation

  1. For all User Databases, there transaction logs are being backed up
  2. Good Settings
    • Verify=Y
      • Backup files are verified
    • CleanupTime=72
      • Log backup files greater than 3 days are shed
    • CheckSum = Y
      • Checksum is validated
    • Compression=Y
      • Compression is on
    • LogToTable=Y
      • Commands and Results are logged to table

 

Audit Job Logs

Aforementioned, Ola hallengren’s beautiful Database Backup scripts has been setup to journal its steps and result of each step into the dbo.CommandLog table.

Let us review the result of that script.

 

Query


SELECT top 1000 
		  [src] = 'Errors'
		, tblCL.DatabaseName
		, tblCL.Command
		, tblCL.CommandType
		, tblCL.StartTime
		, tblCL.ErrorNumber
		, tblCL.ErrorMessage
  
FROM [dbo].[CommandLog] tblCL

where  tblCL.ErrorNumber != 0

order by tblCL.StartTime desc


 

Output

commandlogentries-20160930-0102pm

Interpretation

  1. We can see that on 2016-09-25, 2016-09-18, 2016-09-11, we are failing at midnight with an error code of 3009

 

What is SQL Server Error Code 3009?

Error Number & Details

From Jeremy Kadlec – SQL Server Database Backup and Restore Failure Notifications ( Link ), here is a brief listing of pertinent Backup Failures.

Date Error Number Details
Backup Failure 3009 Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
Backup Failure  18204  %1: Backup device ‘%2’ failed to %3. Operating system error = %4.
Backup Failure  18210  %1: %2 failure on backup device ‘%3’. Operating system error %4.

 

 

And, so thanks to Jeremy Kadlec, we know that 3009 means we have a problem writing data to related MSDB tables.

 

MSDB Database Problems?

More digging around we found that another job runs at the same time.

sp_delete_backuphistory

Here is the log of our sp_delete_backup history job

sp_delete_backuphistory-message

At midnight every Sunday, we are scheduled to prune the backup history tables.

 

SQL Server Errors – Levels of Severity

Backups succeed, but the logging to the MSDB tables sometimes fails.

SQL Server nicely eats up the error and assigns it a severity of 16.

Database Engine Error Severity

Link

errornumber-levelsofseverity

We see it that severity of 16 translates to … “Indicates general errors that can be corrected by the user“.

Dedicated

Dedicated to Ola Hallengren.

There are commercial products out there that run into the same cockroach.

But,  in some cases, the error is not properly logged well enough to fully trace and diagnose it.

Summary

There are a couple of ways to get around this problem.

I will have you try out options on your own.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s