SSMS Error Logs

How to Load SQL Server Error Log into Table for Analysis

A while ago I wrote a post about the SQL Server error log. Today I will show how to load the SQL error log into a table for analysis via T-SQL.

Read on to see a couple of ways to traverse all the error logs and load into a table for analysis.

I encourage you to read this article first: How to Analyze the SQL Server Error Log

It talks about the SQL Server error log in general.

Why Not Just Use the File System?

When possible I prefer to go into the file system and open the SQL Server error logs in a text editor e.g. Notepad++ or UltraEdit. However, there are sometimes you may have access to a SQL Server instance but not be able to RDP or otherwise scan the file system.

That’s where this comes in – straight T-SQL.

The Key Players: xp_readerrorlog and sp_readerrorlog

The 2 key system procedures at play here are “xp_readerrorlog” and “sp_readerrorlog”. Basically, xp_readerrorlog is a wrapper for sp_readerrorlog – granting it more parameters to call.

From calling exec sp_helptext ‘sp_readerrorlog’ you can see the underlying definition:

Stage and Load

The process is simple – just create a temp table (or physical table if you prefer) and load it from sp_readerrorlog

Now you can query for things in the SQL Server error log from your table. Some quick examples of things you can check here (from Glenn Berry’s popular DMVs): <Mine below some examples querying from table>

Iterate SQL Server Error Logs – Results in the Same Table

Part 1 is to get a list of the SQL Server error log files we want to peruse:

Next we create a results table and then loop through each error log file loading it into the results table:

Now we have all of the error logs available stored in a table for analysis!

Iterate SQL Server Error Logs – Results in Separate Tables

The above example shows how to load all of the SQL Server error logs into the same table. But what if we want to load them to individual tables? The technique is similar:

Now we have a table populated for each individual SQL Server error log:

So there you have it. Simple but sometimes very useful way to analyze the SQL Server error log files without access to the file system.

Thanks for reading!


If you liked this post then you might also like my post about How to Analyze the SQL Server Error Log

Do you care about InfoSec and Privacy? Then YOU need to use a VPN.

Did you find this helpful? Please subscribe!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

One thought on “How to Load SQL Server Error Log into Table for Analysis

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.