3

Reading SQL Server Error Logs | The Lone DBA

 2 years ago
source link: https://thelonedba.wordpress.com/2019/08/16/reading-sql-server-error-logs/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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)  NULL);
WHILE @lognum = DATEADD(DAY, DATEDIFF(DAY, '20100101', GETDATE()) - ABS(0), '20100101')
)
)
--AND text like 'Microsoft SQL Server 2017%'
ORDER BY    logdate;

Related

Non-Production Servers – SIMPLE recovery?December 22, 2011In "SQLServerPedia Syndication"

SQL Jobs – On a Calendar?April 30, 2016In "SQLServerPedia Syndication"

Querying SQL Server Event Logs to search for DBCC CheckDB outputSeptember 12, 2012In "SQLServerPedia Syndication"


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK