SQL Server: A More Flexible xp_ReadErrorLog That Reads All Error Logs Including Archives

I am sure that all of you are already familiar with the extended stored procedure xp_ReadErrorLog and its less flexible and published equivalent sp_ReadErrorLog (which takes less parameters) for reading the SQL Server Error Log.

xp_ReadErrorLog – To read current error log:

The extended stored procedure xp_ReadErrorLog is very useful. It helps with reading the error log in a flexible way. It has parameters to limit what is returned. You can read all about the parameters here:
https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

If you noticed, SQL Server rotates the the error log so that the active one does not grow too large. If you wanted to read from the archived logs, you have to make sure that you pass in the right parameter. Let us see the list of log files avalable

EXEC sys.sp_enumerrorlogs;

Archive #   Date                  Log File Size (Byte)
----------- ------------------------------------------
0           04/20/2018  08:00     112972
1           04/13/2018  14:06     388124
2           04/07/2018  22:00     165142
3           03/31/2018  22:00     162756
4           03/24/2018  22:00     156708
5           03/17/2018  23:00     46126
6           03/16/2018  23:37     148086

(7 rows affected)

The “date” above is the “ToDate” up to which it has log information. So, it really means this:

ArchiveNumber LogFromDate LogToDate  LogSizeBytes
------------- ----------- ---------- --------------------
0             2018-04-13  2018-04-20 112972
1             2018-04-07  2018-04-13 388124
2             2018-03-31  2018-04-07 165142
3             2018-03-24  2018-03-31 162756
4             2018-03-17  2018-03-24 156708
5             2018-03-16  2018-03-17 46126
6             2000-01-01  2018-03-16 148086

So, if we wanted log data for a certain date, we have to query the appropriate “archived” log. For example if the log information you need is for 2018-03-25, ArchiveNumber 3 is the one to read as below

EXEC xp_readerrorlog 3, 1,

where 3 is the ArchiveNumber and 1 is for reading the ErrorLog (as opposed to the Agent log with value 2)

Let us look at the parameters it takes

1. FileToRead – where 0 = Current, 1 or 2 or 3 etc = Archive Number as shown above
2. Logtype – to read where 1 = SQL Error Log and 2 = SQL Agent log
3. String1 – The string to match the logs on
4. String2 – Another string to match in combination with String1 (AND)
5. StartDate – Beginning date to look from
6. EndDate – Ending date to look up to
7. ResultsOrder – ASC or DESC

xp_ReadErrorLog has some limitations

  • Reads only the specified error log whose ArchiveNumber is specified
  • Shows only the rows with matching string (not adjacent context info rows)

The first bullet is obvious in that we cannot read ALL the logs to look for something more holistic and meaningful with all the information we have. This MSSQLTips post does describe a method to loop through.

Here is an example where it returns matching rows for search string “Error” but the information returned is almost useless since there is no context

EXEC xp_readerrorlog 0, 1, N'Error'

LogDate                 ProcessInfo  Text
----------------------- ------------ -------------------------------------
2018-04-16 08:00:05.930 spid56       Error: 34052, Severity: 16, State: 1.
2018-04-17 08:00:04.370 spid70       Error: 34052, Severity: 16, State: 1.
2018-04-18 08:00:05.410 spid62       Error: 34052, Severity: 16, State: 1.
2018-04-19 08:00:04.850 spid60       Error: 34052, Severity: 16, State: 1.
2018-04-20 08:00:04.580 spid55       Error: 34052, Severity: 16, State: 1.

(7 rows affected)

It would sure be nice to see the messages that were above or below the lines that were returned (plus it is only from the current error log).

sp_ReadAllErrorLogs – Search current and archived error logs more flexibly

I could not find something readily usable which is also flexible. So, I wrote one called sp_ReadAllErrorLogs which reads ALL the logs available + does the searching based on date/string criteria.

The parameters are similar

1. StartDate – Beginning date to look from
2. EndDate – Ending date to look up to
3. String1 – The string to match the logs on
4. String2 – Another string to match in combination with String1 (AND)
5. AdjacentRowsToShow – The number of adjacent rows above and below matching row to show

Here is the code (samples embedded). I would put it on master.


CREATE PROCEDURE spReadAllErrorLogs
   @FromDate DATETIME = NULL,
   @ToDate DATETIME = NULL,
   @SearchString1 NVARCHAR(255) = NULL,
   @SearchString2 NVARCHAR(255) = NULL,
   @SearchShowAdjacentLogsPlusMinus INT = 0  --Shows this many log records before and after the line matching "SearchString"
											 --If there are multiple rows with the exact LogDate, "adjacent" becomes inaccurate ..still useful
AS
BEGIN

	/*
    v1.0  - Apr 19, 2018 - Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com]

	----------------------------
	Usage examples:
	----------------------------

	--Get all the available error log data
	EXEC spReadAllErrorLogs 

	--Get all the available error log data from a certain date
	EXEC spReadAllErrorLogs @FromDate = '20180320'

	--Get all the available error log data between certain dates
	EXEC spReadAllErrorLogs @FromDate = '20180320', @ToDate = '2018-03-22 23:59' 

	--Get the content with messages containing the strings 'Error' and 'Severity'
	EXEC spReadAllErrorLogs @SearchString1 = 'ERROR', @SearchString2 = 'Severity'

	--Get the content since a certain date with messages containing the strings 'Error' and 'Severity'
	EXEC spReadAllErrorLogs @FromDate = '20180301', @ToDate = NULL, @SearchString1 = 'ERROR', @SearchString2 = 'Severity'

	--Get the content between two dates with messages containing the strings 'Error' and 'Severity'
	EXEC spReadAllErrorLogs @FromDate = '20180301', @ToDate = '20180315', @SearchString1 = 'ERROR', @SearchString2 = 'Severity'

	--Get the content between two dates with messages containing the strings 'Error' and 'Severity' +
	--	show the adjacent 1 row above and below the row with matching text
	EXEC spReadAllErrorLogs @FromDate = '20180301', @ToDate = '20180315', @SearchString1 = 'Error', @SearchString2 = 'Severity', @SearchShowAdjacentLogsPlusMinus=1

	*/

	DECLARE @ArchiveNumber INT;

	IF object_id('TEMPDB.DBO.##TempLogList1') IS NOT NULL
		DROP TABLE ##TempLogList1;
	IF object_id('TEMPDB.DBO.#TempLogList2') IS NOT NULL
		DROP TABLE #TempLogList2;
	IF object_id('TEMPDB.DBO.#TempLog1') IS NOT NULL
		DROP TABLE #TempLog1;
	IF object_id('TEMPDB.DBO.#TempLog1') IS NOT NULL
		DROP TABLE #TempLog2;

	CREATE TABLE ##TempLogList1(
		ArchiveNumber INT NOT NULL,
		--LogFromDate DATE NOT NULL,
		LogToDate DATE NOT NULL,
		LogSizeBytes BIGINT NOT NULL);

	CREATE TABLE #TempLog1(
		LogDate     DATETIME,
		ProcessInfo VARCHAR(64),
		LogText     VARCHAR(MAX));

	CREATE TABLE #TempLog2(
		LogDate     DATETIME,
		ProcessInfo VARCHAR(64),
		LogText     VARCHAR(MAX));

	--Get the list of all logs available (current and archived)
	INSERT INTO ##TempLogList1
	EXEC sys.sp_enumerrorlogs;

	--LogFromDate is populated here
	SELECT
		ArchiveNumber,
		COALESCE((LEAD(LogToDate) OVER (ORDER BY ArchiveNumber)), '20000101') LogFromDate,
		LogToDate,
		LogSizeBytes
	INTO
		#TempLogList2
	FROM
		##TempLogList1
	ORDER BY
		LogFromDate, LogToDate;

	--Remove archive logs whose date criteria does not fit the parameters
	--....and No, it is not a mistake that the comparison has the two dates interchanged! Just think for a few minutes
	DELETE FROM #TempLogList2
	WHERE LogToDate < COALESCE(@FromDate, '20000101')  			OR LogFromDate > COALESCE(@ToDate, '99991231');

	--Loop through and get the list
	WHILE 1=1
	BEGIN
		SELECT @ArchiveNumber = MIN(ArchiveNumber)
		FROM #TempLogList2;

		IF @ArchiveNumber  IS NULL
		  BREAK;

		--Insert the error log data into our temp table
		--Read the errorlog data
		/*
		--https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/
		This procedure takes four parameters:

		Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
		Log file type: 1 or NULL = error log, 2 = SQL Agent log
		Search string 1: String one you want to search for
		Search string 2: String two you want to search for to further refine the results
		*/
		INSERT INTO #TempLog1
		EXEC xp_readerrorlog @ArchiveNumber, 1, @SearchString1, @SearchString2, @FromDate, @ToDate, 'ASC'

		IF (@SearchShowAdjacentLogsPlusMinus > 0)
			--This is purely to get the adjacent records
			INSERT INTO #TempLog2
			EXEC xp_readerrorlog @ArchiveNumber, 1, NULL, NULL, @FromDate, @ToDate, 'ASC';

		--Remove just processed archive number from the list
		DELETE FROM #TempLogList2
		WHERE ArchiveNumber = @ArchiveNumber;
	END;

	IF (@SearchShowAdjacentLogsPlusMinus <= 0)
		SELECT * FROM #TempLog1
		ORDER BY LogDate ASC;
	ELSE
	BEGIN

		--To give the search text some context, we include the log records adjacent to the ones
		--	that matched the search criteria. For example search string "error" would match
		--	"Error: 1101, Severity: 17, State: 12.". However, to get the context, we need
		--  the adjacent rows that show the specific error which is on another adjacent row:
		--		Could not allocate a new page for database 'MyDb' because of insufficient disk space in filegroup 'PRIMARY'.
		--		Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
		WITH t1
		AS
		(
			SELECT *
			FROM #TempLog1
		),
		t2
		AS
		(
			--Select the previous and next x'TH dates as part of the current row
			SELECT *,
				LAG(LogDate, @SearchShowAdjacentLogsPlusMinus) OVER (ORDER BY LogDate) AS LagLogDate,
				LEAD(LogDate, @SearchShowAdjacentLogsPlusMinus) OVER (ORDER BY LogDate) AS LeadLogDate
			FROM #TempLog2
		)
		SELECT DISTINCT t2.LogDate, t2.ProcessInfo, t2.LogText
		FROM t2
		INNER JOIN t1
			ON t1.LogDate BETWEEN t2.LagLogDate
				AND t2.LeadLogDate
		ORDER BY
			t2.LogDate ASC;
	END;

	DROP TABLE #TempLog1;
	DROP TABLE #TempLog2;
	DROP TABLE ##TempLogList1;
	DROP TABLE #TempLogList2;
END;

The usage examples are shown here

Get all the available error log data (current + archived)

EXEC spReadAllErrorLogs

Get all the available error log data from a certain date

EXEC spReadAllErrorLogs
   @FromDate = '20180320'

Get all the available error log data between certain dates

EXEC spReadAllErrorLogs
   @FromDate = '20180320',
   @ToDate = '2018-03-22 23:59'

Get the content with messages containing the strings ‘Error’ and ‘Severity’

EXEC spReadAllErrorLogs
   @SearchString1 = 'ERROR',
   @SearchString2 = 'Severity'

Get the content since a certain date with messages containing the strings ‘Error’ and ‘Severity’

EXEC spReadAllErrorLogs
   @FromDate = '20180301',
   @ToDate = NULL,
   @SearchString1 = 'ERROR',
   @SearchString2 = 'Severity'

Get the content between two dates with messages containing the strings ‘Error’ and ‘Severity’

EXEC spReadAllErrorLogs
   @FromDate = '20180301',
   @ToDate = '20180315',
   @SearchString1 = 'ERROR',
   @SearchString2 = 'Severity'

Get the content between two dates with messages containing the strings ‘Error’ and ‘Severity’ + show the adjacent 1 row above and below the row with matching text

EXEC spReadAllErrorLogs
   @FromDate = '20180301',
   @ToDate = '20180315',
   @SearchString1 = 'Error',
   @SearchString2 = 'Severity',
   @SearchShowAdjacentLogsPlusMinus=1

Creating/Using as temporary stored procedure:

You could easily convert the stored procedure to be a Temporary Stored procedure by altering just a few things:

IF object_id('TEMPDB.DBO.#spReadAllErrorLogs') IS NOT NULL
	DROP PROCEDURE #spReadAllErrorLogs;
GO

CREATE PROCEDURE #spReadAllErrorLogs
   @FromDate DATETIME = NULL,
   @ToDate DATETIME = NULL,
   @SearchString1 NVARCHAR(255) = NULL,
   @SearchString2 NVARCHAR(255) = NULL,
   @SearchShowAdjacentLogsPlusMinus INT = 0

The calls to the stored procedure would also be quite simple as

--Get all the available error log data from a certain date
EXEC #spReadAllErrorLogs @FromDate = '20180320'

Notice the “#” prefix in the stored procedure definition and the calls. Now, you don’t have to worry about a permanent stored procedure being created somewhere you do not want to or have the permissions to!

I hope you will find this useful. Please post your comments/suggestions.

Advertisements

One thought on “SQL Server: A More Flexible xp_ReadErrorLog That Reads All Error Logs Including Archives

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 )

w

Connecting to %s