Read and filter SQL server error logs using TSQL


xp_readerrorlog parameter 1, parameter 2, parameter 3, parameter 4

Parameter 1: To specify the errorlog number. Starts from 0. 0 = current, 1 = Archive #1, 2 = Archive #2

Parameter 2: Log file type. 1 or NULL for Error log and 2 for SQL Agent Log

Parameter 3: Search string

Parameter 4: Second search string further refine your search.

Example1: If you want to get the logs for DBCC CHECKDB, run the command

xp_readerrorlog 0,1,'DBCC CHECKDB'

Application: Send email with the CHECKDB output details. Suppose you have a job to run CHECKDB every week, you can add below query as a next step in that job right after the CHECKDB query.

CREATE table #Errorlog
    id int identity (1,1),
    LogDate datetime,
    ProcessInfo varchar(30),
    Text varchar(Max)
insert #Errorlog (LogDate,ProcessInfo,Text)
exec xp_readerrorlog 0,1,'DBCC CHECKDB'

DECLARE @datevalue nvarchar(100),@textvalue nvarchar(3000),@sw nvarchar(4000)

select @datevalue=LogDate,@textvalue=Text from #Errorlog
where CONVERT(varchar(50),LogDate,101) = CONVERT(varchar(50),GETDATE(),101)

select @sw=@datevalue+' - '+@textvalue

select @sw

exec msdb..sp_send_dbmail
    @copy_recipients ='',
    @subject='Servername DBName CheckDB Alert',
    @body_format = 'HTML'

DROP TABLE #Errorlog

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.