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!

Answer 1

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

Answer 2

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 [e-mail].