Report on failure of the transaction log backup

• MarvinTheAndriod

  SSCarpal Tunnel

  Points: 4972

  More actions

  December 23, 2009 at 4:46 am

  #143699

  Is it possible to report on transaction log backups failures (not the failure of the job, but the failure of the transaction log backup) in T-SQL ?

• Abhijit More

  SSCertifiable

  Points: 6897

  More actions

  December 23, 2009 at 4:53 am

  #1095866

  I assume that you have different job for Transaction log backup, if so then you can set the Notifications / Alerts on Job failed.

  Abhijit - http://abhijitmore.wordpress.com

• GRE (Gethyn Ellis)

  SSCrazy Eights

  Points: 9602

  More actions

  December 23, 2009 at 4:57 am

  #1095868

  A log backup failure will get logged in the error log... You can query the log to get this info with the following code:

  */

  CREATE TABLE #ErrorLog (

  LogDate DATETIME,

  ProcessInfo NVARCHAR(255),

  LogText NVARCHAR(MAX)

  );

  GO

  INSERT INTO #ErrorLog (

  [LogDate],

  [ProcessInfo],

  [LogText]

  )

  EXEC xp_readerrorlog 0, 1, 'Backup Failed';

  SELECT * FROM #ErrorLog

  --WHERE LOGDATE > GETDATE() -1

  DROP TABLE #ErrorLog

  Not sure if that helps you at all

  Gethyn Elliswww.gethynellis.com

• mforbes

  Say Hey Kid

  Points: 695

  More actions

  December 24, 2009 at 6:04 am

  #1096298

  You could set up an alert using error number 3041. That one is for when a backup fails.