locked
Select a set of four consecutive records based on a search string RRS feed

  • Question

  • Hi.  I want to use xp_readerrorlog (or sp_readerrorlog) to read the SQL Server error log to find any failed backup jobs in the last 24 hours.  I know that xp_readerrorlog can search for a string or strings like "failed backup".  I want to grab any related log entries including the message that contains why the backup failed, and send them via email.  For testing, I trigger a backup to fail by setting the destination to a non-existent drive letter.  A failed backup generates a set of four consecutive records in the error log, all with the same date/timestamp.  For example:

    2018-10-30 20:03:30.850 Backup BACKUP failed to complete the command BACKUP DATABASE xxx. Check the backup application log for detailed messages.
    2018-10-30 20:03:30.850 Backup Error: 3041, Severity: 16, State: 1.
    2018-10-30 20:03:30.820 spid65 BackupDiskFile::CreateMedia: Backup device 'X:\yyyyyyyy\ZZZ_backup_2018_10_30_200330_6728283.bak' failed to create. Operating system error 3(The system cannot find the path specified.).
    2018-10-30 20:03:30.820 spid65 Error: 18204, Severity: 16, State: 1.

    How can I grab the four consecutive records, or multiple sets of four if there have been multiple backup failures in the last 24 hours?  I think it calls for a CTE or windowing function, but I don't know how to put it together.  (Once I identify the records, I know how to send them as an HTML table in an email message.)

    Thanks!



    • Edited by District9 Wednesday, October 31, 2018 7:20 AM
    Wednesday, October 31, 2018 7:16 AM

Answers

  • How can I grab the four consecutive records, or multiple sets of four if there have been multiple backup failures in the last 24 hours?  I think it calls for a CTE or windowing function, but I don't know how to put it together.  (Once I identify the records, I know how to send them as an HTML table in an email message.)

    Thanks!



    Hi District9,

    You mean to need this?

    create table errorLog
    (
    LogDate datetime,
    ProcessInfo varchar(64),
    [Text] varchar(max)
    )
    
    insert into errorLog values
    ('2018-10-30 20:03:30.850','Backup','BACKUP failed to complete the command BACKUP DATABASE xxx. Check the backup application log for detailed messages.'),
    ('2018-10-30 20:03:30.850','Backup','Error: 3041, Severity: 16, State: 1.'),
    ('2018-10-30 20:03:30.820','spid65','BackupDiskFile::CreateMedia: Backup device ''X:\yyyyyyyy\ZZZ_backup_2018_10_30_200330_6728283.bak'' failed to create. Operating system error 3(The system cannot find the path specified.).'),
    ('2018-10-30 20:03:30.820','spid65','Error: 18204, Severity: 16, State: 1.')
    
    --SQL 2016 or below
    ;WITH CTE AS
    (
    select CONVERT(VARCHAR(20),LogDate,120) as logDates,logDate,ProcessInfo AS ProcessInfo,[Text]  FROM 
    errorLog 
    )
    SELECT 
    T.logDates,MAX(T.ProcessInfo) AS ProcessInfo,STUFF((SELECT ','+[Text] FROM CTE WHERE logDates=T.logDates order by logDate FOR XML PATH('')),1,1,'')  AS Erro_message
    FROM CTE T
    GROUP BY T.logDates
    
    --SQL 2017 or above
    ;WITH CTE AS
    (
    select CONVERT(VARCHAR(20),LogDate,120) as logDates,logDate,ProcessInfo AS ProcessInfo,[Text]  FROM 
    errorLog 
    )
    SELECT 
    T.logDates,MAX(T.ProcessInfo) AS ProcessInfo,STRING_AGG([Text],',')  WITHIN GROUP (ORDER BY logDate ASC)  AS Erro_message
    FROM CTE T
    GROUP BY T.logDates
    
    --Output
    /*
    logDates             ProcessInfo                                                      Erro_message
    -------------------- ---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    2018-10-30 20:03:30  spid65                                                           BackupDiskFile::CreateMedia: Backup device 'X:\yyyyyyyy\ZZZ_backup_2018_10_30_200330_6728283.bak' failed to create. Operating system error 3(The system cannot find the path specified.).,Error: 18204, Severity: 16, State: 1.,BACKUP failed to complete the co
    */

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by District9 Thursday, November 1, 2018 2:22 PM
    Thursday, November 1, 2018 6:49 AM

All replies

  • See if this helps you

    https://www.datanumen.com/blogs/read-analyze-backup-failure-logs-sql-server/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 31, 2018 7:28 AM
    Answerer
  • How can I grab the four consecutive records, or multiple sets of four if there have been multiple backup failures in the last 24 hours?  I think it calls for a CTE or windowing function, but I don't know how to put it together.  (Once I identify the records, I know how to send them as an HTML table in an email message.)

    Thanks!



    Hi District9,

    You mean to need this?

    create table errorLog
    (
    LogDate datetime,
    ProcessInfo varchar(64),
    [Text] varchar(max)
    )
    
    insert into errorLog values
    ('2018-10-30 20:03:30.850','Backup','BACKUP failed to complete the command BACKUP DATABASE xxx. Check the backup application log for detailed messages.'),
    ('2018-10-30 20:03:30.850','Backup','Error: 3041, Severity: 16, State: 1.'),
    ('2018-10-30 20:03:30.820','spid65','BackupDiskFile::CreateMedia: Backup device ''X:\yyyyyyyy\ZZZ_backup_2018_10_30_200330_6728283.bak'' failed to create. Operating system error 3(The system cannot find the path specified.).'),
    ('2018-10-30 20:03:30.820','spid65','Error: 18204, Severity: 16, State: 1.')
    
    --SQL 2016 or below
    ;WITH CTE AS
    (
    select CONVERT(VARCHAR(20),LogDate,120) as logDates,logDate,ProcessInfo AS ProcessInfo,[Text]  FROM 
    errorLog 
    )
    SELECT 
    T.logDates,MAX(T.ProcessInfo) AS ProcessInfo,STUFF((SELECT ','+[Text] FROM CTE WHERE logDates=T.logDates order by logDate FOR XML PATH('')),1,1,'')  AS Erro_message
    FROM CTE T
    GROUP BY T.logDates
    
    --SQL 2017 or above
    ;WITH CTE AS
    (
    select CONVERT(VARCHAR(20),LogDate,120) as logDates,logDate,ProcessInfo AS ProcessInfo,[Text]  FROM 
    errorLog 
    )
    SELECT 
    T.logDates,MAX(T.ProcessInfo) AS ProcessInfo,STRING_AGG([Text],',')  WITHIN GROUP (ORDER BY logDate ASC)  AS Erro_message
    FROM CTE T
    GROUP BY T.logDates
    
    --Output
    /*
    logDates             ProcessInfo                                                      Erro_message
    -------------------- ---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    2018-10-30 20:03:30  spid65                                                           BackupDiskFile::CreateMedia: Backup device 'X:\yyyyyyyy\ZZZ_backup_2018_10_30_200330_6728283.bak' failed to create. Operating system error 3(The system cannot find the path specified.).,Error: 18204, Severity: 16, State: 1.,BACKUP failed to complete the co
    */

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by District9 Thursday, November 1, 2018 2:22 PM
    Thursday, November 1, 2018 6:49 AM