As a DBA, we have to know on how to read the error log when incidents happens. The default logs is full of information and not easy to read.
I use xp_readerrorlog
command to help me.
EXEC Xp_readerrorlog 0
Here 0 stands for file number where latest file has 0 number and subsequent rolled over file has incremented number. So this will display current error log file while incremented number will subsequent show rolled over file for.
EXEC Xp_readerrorlog 0, 1
Here second parameter has two values
1. error log
2. agent log
yes we can get agent log also from this proc 🙂
EXEC Xp_readerrorlog 0, 1, "dbcc"
This command will search in current error log for string has ‘dbcc’ word in it. Note from SQL 2008 and above, double quotation mark “” is used and before it was using single quotation mark ”.
EXEC Xp_readerrorlog 0, 1, "dbcc", "traceon"
This will filter result set more and give result which has both ‘dbcc’ and ‘traceon’ words in it.