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.
SQL Jana 
One thought on “SQL Server: A More Flexible xp_ReadErrorLog That Reads All Error Logs Including Archives”