SQL server

Reading the error logs using TSQL.

Reading the error logs using SSMS is usually slow, and it is also difficult to filter search results efficiently. On the other hand, TSQL can be used to get the SQL error logs (and SQL agent logs also) in a tabular format. This output can be put into a temporary table, and then investigated. There is the xp_readerrrorlog and the undocumented sp_readerrorlog procedures that can be used. There is a nice and detailed explanation of both here. Below is a quick reference that I took from the site:


  1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
  2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for
  4. Search string 2: String two you want to search for to further refine the results
  5. Search from start time 
  6. Search to end time
  7. Sort order for results: N’asc’ = ascending, N’desc’ = descending

Sample: EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'desc'
EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'asc'


Thinking about someting? 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