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.
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
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.
I’m a Microsoft Data Platform MVP also a very experienced multi platform DBA (MySQL, Oracle, SQL Server, SQL Azure) with over 10 years of experience in the database field.
I work in database support as primary consultant and DBA manager for larger US based companies in the healthcare, insurance, retail, food and energy industries.
I have a few certifications under my belt MCSE, MCSA, MCP, MCTS, ITIL v3, Docker, Kubernetes and RedHat openshift.
International speaker, blogger, Guatemala SQL community group leader, mentor.
Love everything related to technology, sci-fi and video games.