Read and filter SQL server error logs using TSQL
Command:
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 @profile_name='databasemailprofilename', @recipients='dl.dba@company.com', @copy_recipients ='dl.managers@company.com', @subject='Servername DBName CheckDB Alert', @body=@sw, @body_format = 'HTML' DROP TABLE #Errorlog
