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”.