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.
Carlos Robles is a Solutions Architect at AWS, a former Microsoft Data Platform MVP, a Friend of Redgate, but more than anything a technology lover. He has worked in the database management field on multiple platforms for over ten years in various industries.
He has diverse experience as a Consultant, DBA and DBA Manager. He is currently working as a Solution Architect, helping customers to solve software/infrastructure problems in their on-premise or cloud environments.
Speaker, author, blogger, mentor, Guatemala SQL User group leader. If you don’t find him chatting with friends about geek stuff, he will be enjoying life with his family.