201711 – MS SQL – Read SQL Log

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s