The SQL Server ERROR log is the best source to understand what’s wrong with your SQL Server instance, think this log is like your flight recorder.

In case you are not too familiar with the SQL Server ERROR log, the xp_readerrorlog extended stored procedure is your friend; and as a good friend you must get to know him. In this post, you will find some examples of how to take advantage of this undocumented stored procedure and maximize your time avoiding the utilization of slow and painful tools like Event Viewer from Windows.

First thing first, let’s understand what are the parameters we need to provide to the stored procedure and the most important how we have to use them.

Archive ID:

The first parameter is is about picking the order of the log we want to query:

• Current log = 0
• Archived error log #1 = 1
• Archived error log #2 = 2

Log type:

Then, the second parameter is the SQL Server component we are focusing in:

• SQL Server Instance Log = 1
• SQL Agent Log = 0

So, combining these two parameter we can check the current log (0 in the first parameter) for a SQL Server instance (1 in the second parameter) or the SQL Server Agent (2 in the second parameter)

Here is how it looks like in T-SQL code:

-- Checking the current SQL Server instance and SQL Agent logs:
xp_readerrorlog 0,1 -- Remember 1 for SQL Server instance
xp_readerrorlog 0,2 -- Remember 2 fro SQL Server Agent

We also have an optional parameter we can include, it’s a simple search string. This parameter it’s really handy when you are looking for a specific event.

Here are some T-SQL examples how we can combine the first two parameters with a specific search string:

-- Checking for failed logins in our SQL Server instance:
xp_readerrorlog 0,1,'Login failed' --For SQL Server 2012 and below
xp_readerrorlog 0,1,"Login failed" --For SQL Server 2014 onwards
-- Checking for TempDB related entries in our SQL Server instance:
xp_readerrorlog 0,1,'TempDB' --For SQL Server 2012 and below
xp_readerrorlog 0,1,"TempDB" --For SQL Server 2014 onwards

Hopefully this tip could help you to save some time when looking at your SQL Server instance for recent errors or some events from the past that you might need to collect for further analysis.

Stay tuned for more DBA mastery tips.