Error Log Analysis Script

SQL Server Error Logs show various aspects regarding the state of the SQL Server Instance. Error log analysis helps ensure nothing unexpected happens, and is useful for diagnosing existing problems. The error log is stored in text files on the filesystem, and is visible using SQL Server Management Studio through the Object Explorer. To see the logs, click the “Management” node in the object explorer. Then open the “SQL Server Logs” node to see the log files. The Object Explorer looks like this when you drill-down to the SQL Server Logs node:

SQL Server Error Logs

                       SQL Server Error Logs

Manually searching for a specific message in set of large error logs can be a time-consuming process. To mitigate that pain, I’ve created a script that automates gathering the content of the logs.

The script programmatically gathers the log entries into a temporary table. It then shows results in reverse-chronological order. “Noise” entries are filtered out of the resulting display, allowing you to easily focus on the important messages. Customize the list of “noise” messages to suit your needs, via the contents of the #exclusions table.

The first column shows which error log the message resides in. 0 indicates the current error log, with higher numbers representing older log files. The code currently limits the number of log files inspected to 9. Modify that in the code if you need to look at more than the 9 most recent log files.

Once you run the code above, you can run any query you like against the #mverrlog temporary table. Perhaps so you can see a specific date-range, or messages concerning a specific database.

Let me know if you found this code useful; and check out the rest of our SQL Server Tools.

Microsoft’s Error Log documentation is a bit sparse!