By: Mike Eastland | Updated: 2014-01-10 | Comments (5) | Monitoring
I am troubleshooting a specific error that recurs in my SQL Server error log. I want to identify all of the occurrences of this error from a certain point in time, but my error log has been cycled multiple times since then. How can I retrieve this information without manually opening and reviewing each error log?
The code in this tip will show how to use two system stored procedures to identify all iterations of a given string in the available SQL Server error logs since a specific point in time.
Each section of code was designed to be executed as a combined, single unit. It is logically broken down for clarification. In addition, the code has been tested against the following versions: SQL Server 2005, SQL Server 2008R2, and SQL Server 2012.
T-SQL Code to Read All SQL Server Error Logs
Declare variables used in the script.
DECLARE @maxLog INT,
DECLARE @errorLogs TABLE (
LogSize BIGINT );
DECLARE @logData TABLE (
LogText VARCHAR(MAX) );
Initialize the variables. Whenever a SQL Server instance starts, the server process id is written to the instance error log. In this example, we will search all available SQL Server error logs for entries containing the string Server process ID is that have occurred since 10/01/2013 at 8am. In effect, we are searching for all instance start-ups that have occurred since @startDate.
@startDate = '2013-10-01 08:00';
Use the system stored procedure sp_enumerrorlogs to populate the first table variable with the list of available error logs. Note that the procedure returns the log (archive) number, date it was created, and the size. For our purposes, we are concerned with the log number and create date.
Determine the oldest log that will be included in the search.
WHERE [LogDate] <= @startDate
ORDER BY [LogDate] DESC;
Loop through the available error logs and use the system stored procedure sp_readerrorlog to populate the second table variable with error entries containing the search string from each log fitting the date criteria. For more details on sp_readerrorlog and xp_readerrorlog, review Tip 1476.
INSERT INTO @logData
EXEC sys.sp_readerrorlog @maxLog, 1, @searchStr;
SET @maxLog = @maxLog - 1;
Finally, return the error log entries matching the date and search-string criteria.
WHERE [LogDate] >= @startDate
ORDER BY [LogDate];
Sample results from a test server are listed below.
|2013-10-01 10:44:13.290||Server process ID is 5884.|
|2013-10-29 11:42:30.520||Server process ID is 11228.|
|2013-10-30 09:16:52.410||Server process ID is 11636.|
|2013-11-01 14:59:51.780||Server process ID is 8428.|
|2013-11-13 13:55:36.070||Server process ID is 4012.|
|2013-12-03 10:10:19.660||Server process ID is 10360.|
|2013-12-04 15:48:07.830||Server process ID is 7600.|
The code in this tip can be used to programmatically identify SQL Server errors across multiple available log files. This can beneficial when the exact time an error occurred is not known, or when comparing error trends before and after making system changes to address the error(s) in question.
Complete Code Listing
Here is a complete listing of the code. Just copy and paste and change the two parameters @searchStr and @searchDate.
SET NOCOUNT ON DECLARE @maxLog INT, @searchStr VARCHAR(256), @startDate DATETIME; DECLARE @errorLogs TABLE ( LogID INT, LogDate DATETIME, LogSize BIGINT ); DECLARE @logData TABLE ( LogDate DATETIME, ProcInfo VARCHAR(64), LogText VARCHAR(MAX) ); SELECT @searchStr = 'Server process ID is', @startDate = '2013-10-01 08:00'; INSERT INTO @errorLogs EXEC sys.sp_enumerrorlogs; SELECT TOP 1 @maxLog = LogID FROM @errorLogs WHERE [LogDate] <= @startDate ORDER BY [LogDate] DESC; WHILE @maxLog >= 0 BEGIN INSERT INTO @logData EXEC sys.sp_readerrorlog @maxLog, 1, @searchStr; SET @maxLog = @maxLog - 1; END SELECT [LogDate], [LogText] FROM @logData WHERE [LogDate] >= @startDate ORDER BY [LogDate];
- Update the retention and periodically cycle your error logs using Tip 1155 as a reference.
- If your troubleshooting efforts involve more than one server, use the method shown in Tip 2086 to search the error logs for multiple SQL Server instances simultaneously.
- For information on how to convert this code into a stored procedure, review the SQL Server Stored Procedure Tutorial.
- Check out other MSSQLTips related to error logs.
Last Updated: 2014-01-10
About the author
View all my tips