Reading the Error log file of SQL Server and SQL Server Agent
Key points:
New Errorlog will be created every time SQL Server is bounced.
We can explicitly archive the existing Errorlog file
Two procedures to read the Errorlog, Sp_readerrorlog and XP_readerrorlog
To capture any specific errors like deadlock we have to use spl trace flags
Read the errorlog
sp_readerrorlog 0,1,'search key word1','search key word2'
other procedure:
xp_readerrorlog 0,1,'search key word1','search key word2','date1','date2'
parameters:
0 = Current running error Log - Values (0,1,2,3..)
1 = SQL Server Error Log
2 = SQL Server Agent Error Log
Search key word1 = string to be search
Search key word2 = string to be search
date1 = start date from where errorlog should be start
date2= End date from where errorlog should stop
For date format run (select getdate() )
date1 and date2 are accepted in getdate() format.
Hope above information helps
Thanks
