Look for Server “Ouches” – By Collecting SQL Server Logs (Part 2)

In my last post, I detailed how you could look for server “ouches” by collecting the server’s event logs using PowerShell (https://sqlbadboy.wordpress.com/2015/12/07/look-for-server-ouches-by-collecting-server-logs/). However, at times you may want more detailed information concerning the SQL Server which may need to be obtained from the SQL Server logs.

And although I normally show code in PowerShell, this time I will simply show the T-SQL code. I am assuming it will be easy for someone to embed this T-SQL code in a larger PowerShell script or even an SSIS package for an automated collection.

How to read a SQL Server’s logs?

The key to reading the SQL Server logs will be using the undocumented stored procedure, xp_readerrorlog. This extended stored procedure has the following parameters:

  • Parameter 1 – Used to get a specified error log file. SQL Server keeps a number of historical error log files, so you can use this parameter if you want to read an older error log file. The default is 0, which is the current error log file.
  • Parameter 2 – Used to specify if you want to read from the SQL Server error log (default, value = 1) or SQL Agent log (value = 2).
  • Parameter 3 – Used to specify a string as a search parameter to filter the results. This needs to be specified to use Unicode, with syntax like N’search string’.
  • Parameter 4 – Used to specify an additional search parameter.
  • Parameter 5 – Used to filter out rows older than this datetime value.
  • Parameter 6 – Used to filter out rows newer than this datetime value.
  • Parameter 7 – Used to specify a sort order for the result. Valid values include either N’ASC’ or N’DESC’.

Another helpful undocumented stored procedure is xp_enumerrorlogs. This extended stored procedure lists all the error logs with their last change date. The default for the number of logs is 6, but it can be configured to use up to 99.

Here is a sample of code that will look for the last 3 days’ worth of entries in the error log.


DECLARE @StartDatetime DATETIME

SELECT @StartDatetime = GETDATE() - 3

EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, @StartDatetime

***NOTE: If you recycle the error logs daily, you will have to read multiple log files to obtain the entries for the last 3 days, as in our example. The error log can be recycled using sp_cycle_errorlog (https://msdn.microsoft.com/en-us/library/ms182512.aspx). It is recommended to recycle the error logs on a periodic basis or based on size so that they are easier to work with.

 So, here is a simple way to read the SQL Server error logs and look for “ouches”.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

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