Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.
In the last episode I showed how I tweaked some in-house scripts to provide more user-friendly output. This time around I want to revisit those scripts and give my fellow beginner DBAs some insight on some very basic troubleshooting.
Failure is Always An Option
I’m a big Mythbusters fan, and was saddened when the show eventually went off the air. There’s so much I learned about how the world around me works by watching the antics of Adam Savage, Jamie Hyneman, the rest of their crew. I still follow both of them on Twitter (links above), and also regularly watch Adam Savage’s Tested on YouTube.
Several pithy phrases were said over the many seasons of Mythbusters episodes, including gems like, “I reject your reality and substitute my own,” and “Jamie likes big boom.” My favorite line from the show is “Failure is always an option.”
My family has been rewatching some of the show’s episodes, and in one of their final shows Adam mentions how they wouldn’t have been able to accomplish that particular episode’s goals without their previous years of experience. If you were to go and watch every episode, you’d quickly realize that the Mythbusters failed more often than succeeded. It’s through repeated trial and error that they learned the most beneficial lessons.
Learning about SQL Server’s capabilities has been a similar sort of journey. It’s still early days for me, but I’m sure that most Senior DBA’s out there will tell you that the knowledge they’ve gained over the years has been full of “that didn’t work” moments. For fun, check out this video from Bert Wagner (b|t) about SQL Fails.
No matter how much I try to remember all of this, I’m still the sort of person who gets that flip-flopping stomach feeling when I mess up or can’t figure something out right away. It’s in moments like these that I have to take a breath and remind myself that I’m still learning. SQL Server is a very complex piece of software—so complex that I doubt there is any one person who knows EVERYTHING about it, not even the people who’ve worked to develop it over the years.
Failure is always an option. As a SQL Server DBA, you’ll soon learn that your client’s servers will fail—no matter how good you are at your job. How will you identify those failures? Here’s one way.
There’s Your Problem
SQL Server has a bunch of built-in tools and resources that help identify failures and errors. Having said that, there’s a learning curve involved that’s sort of like baking a loaf of bread. The components are all there at your fingertips, but you need someone to show you how to use them in the correct properly.
One of the scripts I run daily, the Read Errorlog script, has a bit of code that looks like this:
--Dump all the things into the table insert into #Errorlog EXEC sys.xp_readerrorlog 0 -- Current ERRORLOG ,1 -- SQL ERRORLOG (not Agent)
What’s relevant here is understanding WHAT is being read when this script is run. I’m not all that concerned today with showing how we massage the output, just where the information is coming from.
The line EXEC sys.xp_readerrorlog is executing a widely-known but undocumented Extended Stored Procedure. This is why you see “xp” in the scriptlet. If a regular Stored Procedure were being executed you’d see “sp” instead.
NOTE: You’ll see the following message at the top of the MS Docs related to Extended Stored Procedures: “This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CLR Integration instead.” We will probably need to rewrite our in-house scripts some time in the future to stay current.
Error logs are not stored in the database, but rather in text files on the host server. So, this Extended Stored Procedure looks outside of SQL Server to where the error log text files are stored within the hardware environment.
What is the procedure reading? xp_readerrorlog is pulling information from the files you can find in the Object Explorer under Management–>SQL Server Logs (highlighted in green). The Extended Stored Procedure helps make our lives as DBAs just a little bit more efficient by pulling the relevant information from the text files for us rather than forcing us to view each individual log file and scroll through hundreds of lines of results.
What’s also important to note here is what our version of the Read Errorlog script is NOT reading. Notice the Error Logs folder highlighted in red in the Object Explorer. You can find it under SQL Server Agent–>Error Logs. We don’t care about those error logs for this particular task.
xp_readerrorlogs accepts several parameters. The two we use most often are the Log Number and Log Type parameters.
The Log Number parameter we pass is “0”, which tells SSMS to read the current log. The Log Type parameter we pass is “1”, which tells SSMS to read from SQL Server Logs (green) and NOT from the SQL Server Agent Error Logs (red).
Beyond this, we are then able to use our script to tell SSMS what data we’d like displayed from the logs, rather than having it output every single line. For instance, if we’re specifically looking for deadlocks, our SELECT statement can be written to only look for LogText like ‘%deadlock encountered%’.
That’s all for this week. Join me next time for the next episode in The Ironic DBA Files.
The Ironic DBA Files
- Prequel: The Ironic DBA—Starting a New and Unexpected Career
- Episode 1: You Back That Up?
- Episode 2: Attack of the Corruption
- Episode 3: Revenge of the Index
- Episode 4: A New Primary Key
- Episode 5: The Maintenance Plan Strikes Back
- Episode 6: Return of the TSQL
- Episode 7: The Backup Awakens
- Episode 8: The Last Rebuild
- Episode 9: Rise of the Clients
- Review One: A SQL Story
- It’s Hip to Be Square
- Rock Around the Clock