Answered by:
Select a set of four consecutive records based on a search string

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 AMAnswerer -
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