T-SQL – Reading SQL Error Logs

T-SQL – Reading SQL Error Logs

sp_readerrorlog or xp_readerrorlog is a very handy system stored procedure to get database error log details for SQL Server. Following is the simple syntax to use it:

sp_readerrorlog

OR

Xp_readerrorlog

Note:

  • The maximum length of the message is 255 chars wide, anything wider will be truncated.
  • You can use sp_readerrorlog to look at the previous logs, not just the current one, by adding a number 1 or 2 or 3…
  • sp_readerrorlog 1 is the previous error log and sp_readerrorlog 2 is the second previous error log, and so on… Below is one script to pull previous log details

PRINT
‘Previous errorlog’;

PRINT
‘*****************’;

EXEC
xp_readerrorlog 1;

go

PRINT
‘ ‘

PRINT
‘Previous errorlog -2’;

PRINT
‘********************’;

EXEC
xp_readerrorlog 2;

go

PRINT
‘ ‘

PRINT
‘Previous errorlog -3’;

PRINT
‘********************’;

EXEC
xp_readerrorlog 3;

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s