Monday, April 11, 2022

Using xp_readerrorlog to read the SQL Server Error Log

The SQL Server Error Log contains a lot of information -- some of which can be very useful.  :)  But, how do you find what you're looking for in the Error Log quickly?  How might you trace an event over time to see when it was recorded in the Error Log?  

In this post I will show you how to use xp_readerrorlog to quickly find what you may be looking for in the Error Log.  In this example we are looking for all incidents of 'Login Failed':


       IF OBJECT_ID('tempdb..#ErrorLog') IS NOT NULL
       DROP TABLE #ErrorLog
 
       CREATE TABLE #ErrorLog (
         LogDate DATETIME ,
         ProcessInfo VARCHAR(1000) ,
         LogMessage TEXT
       );
 
       INSERT #ErrorLog  -- see below for xp_readerrorlog parms
       EXEC sys.xp_readerrorlog 0, 1, "Login failed"

-- now take a look at any occurrences that were recorded
SELECT * FROM #ErrorLog
ORDER BY LogDate DESC;

This is the output:







The above example was just login failures, but you can use this to track down just about anything that is recorded in the Error Log.  I used it recently to track down the cause of database timeout exceptions.  The end user could only say when the timeouts were occurring, so I looked into the error log at the given time and found this:

I/O is frozen on database XXXX.  No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

Then I used the above method to find the message in the Error Log every day at the same time that the full server backups were occurring.  And there you have it.  That was the cause of the timeouts they were seeing.  Not uncommon at all to see this w/the 3rd party full server backups, which they were then able to workaround.  

Anyway... this is just something you can use to quickly read the SQL Server Error Log to identify whenever a particular event is occurring.  Hope you find it useful.

These are the xp_readerrorlog parameters:



Stored procedure created & last modified date

To go alongside the last executed date of the stored procedure, you can use this to tell you when the procedure(s) were created and last moddified.  

       SELECT

             [DatabaseName] = DB_NAME(),
             [ProcedureName] = SCHEMA_NAME([schema_id]) +'.'+ [name],
             [Created] = create_date,
             [LastModified] = modify_date
       FROM
             sys.objects
       WHERE
             [type] = 'P'


Should return a little something like this:













You can narrow it down to a single procedure by adding this into your WHERE clause:


    AND [name] = 'usp_BackupAudit'





Stored Procedure - Last Executed Date