I wrote a script to allow me to look at the last few days of errors in the SQL error logs. It was rather useful at a previous site, and I wrote them a little note about it, which I then carefully failed to copy for here. Oh well.
Why Script This? What’s Wrong With SSMS’s GUI?
Well, although SSMS does allow you to look at the error logs, it’s not very helpful for filtering – you can only filter for items that match, rather than exclude items. There are a few other filters as well – I guess the whole thing is just a wrapper around xp_readerrorlog below…
What’s wrong with just using sp_readerrorlog or xp_readerrorlog?
The undocumented procedures? Again, limited filtering functionality – you can put in at most two strings to filter match the logs.
What are these procedures anyway?
There are two system stored procedures and two extended stored procedures – all undocumented – that be used to help reading error logs. These are very briefly discussed below.
sp_enumerrorlog & xp_enumerrorlog
These take a single optional parameter, to say which error logs you wish to examine. Values are 1 (default) for SQL Server error log, and 2 for the SQL Agent logs
sp_readerrorlog
Four parameters:
- Number of the log file to look at – see output from sp_enumerrorlog
- Whether it’s a SQL Server error log or a SQL Agent log (see above)
- two filter parameters – error messages containing these strings will be returned
xp_readerrorlog
All the parameters for sp_readerrorlog, and these three:
- Begin date/time
- End date/times
- sort order asc/desc
T-SQL Script to read SQL Server Error Logs
Here’s the script I’ve found useful to read just a few days’ worth of error logs. Other scripts are out there.
I’ve filtered out some of the more boring error messages. You might want to fiddle with those, add your own, whatever.
DECLARE @daysback INT = 0; -- number of days to go back in the logs. 0 = today only
-- table variable for holding the details of the error logs.
-- Yes, I know, table variables are evil. This one is unlikely to hold more than a few dozen rather narrow lines
DECLARE @ErrorLogs TABLE
(
Archive INT NOT NULL,
LogDate DATETIME NOT NULL,
LogFileSizeBytes BIGINT NOT NULL,
ReadThis TINYINT NULL
);
-- useful trick if you don't know it: INSERT INTO a table the results of EXECing a SP.
INSERT INTO @ErrorLogs (Archive, LogDate, LogFileSizeBytes)
EXEC sys.sp_enumerrorlogs;
DECLARE @lognum INT = 0;
DECLARE @logdate DATETIME;
--figure out which logfiles we need.
WITH
NextLog
AS
(
SELECT Archive,
LogDate,
LogFileSizeBytes,
ReadThis,
ISNULL(LAG(LogDate) OVER (ORDER BY Archive), LogDate) AS nextlogdate
FROM @ErrorLogs
)
UPDATE @ErrorLogs
SET ReadThis = CASE
WHEN e.Archive = 1 THEN 1 -- always read the first file; doesn't always get identified by the next line
WHEN n.nextlogdate >= DATEADD(DAY, DATEDIFF(DAY, '20100101', GETDATE()) - ABS(@daysback), '20100101') THEN
1
ELSE
0
END
FROM NextLog AS n
INNER JOIN @ErrorLogs AS e ON e.Archive = n.Archive;
--just checking which files we're looking at... Probably comment this line out for production use
SELECT *
FROM @ErrorLogs
ORDER BY Archive;
IF OBJECT_ID('tempdb.dbo.#spErrorLog', 'U') IS NOT NULL BEGIN
DROP TABLE #spErrorLog;
END;
CREATE TABLE #spErrorLog (logdate DATETIME NOT NULL, ProcessInfo VARCHAR(20) NULL, Text VARCHAR(MAX) NOT NULL);
WHILE @lognum <= (SELECT MAX(Archive)FROM @ErrorLogs WHERE ReadThis = 1)
BEGIN
INSERT INTO #spErrorLog
EXEC sys.sp_readerrorlog @p1 = @lognum;
SELECT @lognum = @lognum + 1;
SELECT @logdate = LogDate
FROM @ErrorLogs
WHERE Archive = @lognum;
END;
SELECT *
FROM #spErrorLog
WHERE
1 = 1
AND
(
ProcessInfo = 'Server' -- we like server-related boot messages.
OR
( -- filter out noisy messages that we don't really need to see here
Text NOT LIKE '%Backup Log%'
AND Text NOT LIKE 'Log was backed up%'
AND Text NOT LIKE 'CHECKDB for database % finished without errors on %This is an informational message only; no user action is required.'
AND Text NOT LIKE 'DBCC CHECKDB % WITH all_errormsgs, no_infomsgs, data_purity executed by % found 0 errors and repaired 0 errors.%'
AND Text NOT LIKE 'BACKUP DATABASE WITH DIFFERENTIAL successfully processed % pages%'
AND Text NOT LIKE 'Database differential changes were backed up. Database: % This is an informational message. No user action is required.'
AND Text NOT LIKE 'BACKUP DATABASE successfully processed % pages %'
AND Text NOT LIKE 'Database backed up. Database: % This is an informational message only. No user action is required.'
AND logdate >= DATEADD(DAY, DATEDIFF(DAY, '20100101', GETDATE()) - ABS(@daysback), '20100101')
)
)
ORDER BY logdate;
