Hi everyone! It's great to be back on Database Trends and Applications magazine. While I dropped out of the regular columnist position for quite a while, I was still hard at it promoting strong database administration practices. I'm glad to be back discussing issues and trends that are on my mind, and on the minds of my customers wherever I meet them. And, since I've been traveling nonstop for most of this year, I've been meeting a lot of them.
One of the things I repeatedly encounter when speaking to database professionals working with Microsoft SQL Server is that many of them simply don't know about some of the most elementary and fundamental means of investigating SQL Server performance. For example, I recently created a popular poster for Quest Software that shows all of the most meaningful and useful Windows Performance Monitor (PerfMon) counters. Now friends, PerfMon has been with us since Windows NT Server, and yet, PerfMon counters are a mystery to at least half of the DBAs I meet. Half!
Another feature of SQL Server that is poorly understood is the black box recorder. In effect, SQL Server 2005 and later keeps two types of background traces running all the time. The first trace is called the default trace. It tracks just a few events inside SQL Server, as they happen, in case you ever have to call Microsoft Support for help.
The black box recorder is the second background trace you should be aware of. It's built into SQL Server but not enabled by default. Although it's in SQL Server, its intended purpose is just like that of the black box recorder in an airplane, to determine what happened after a crash occurs. Like the default trace, the black box recorder is an auto-starting trace. However, it keeps track of a lot more information, which can be even more useful in the event of a crash.
One quick note to mention, as well, is that the black box recorder only records 5MB of rolling data. It begins recording when the server restarts, and then, when it hits 5MB worth of data, it actually wraps and begins to write from the beginning again. One of the things you will want to do is actually change the configuration setting of the black box recorder so it will record, say, 25MB or 50MB worth of data, thereby providing you with a good amount of information in the event you need recovery and/or deep troubleshooting. That's not to say you can't run your own additional traces; but, since these are always up and running, why not take advantage of them?
On my blog (http://kevinekline.com), as a corollary to this post, I'm posting sample code to show you how to increase the size of the black box recorder trace file from 5MB to 25MB or 50MB, as well as a couple of queries you might want to use if you ever want to look at the information in the trace, since it's always running and available to you.
So, when all else fails and you're scrambling to find information about what might've caused your SQL Server to crash, be sure to look at the SQL Server default trace and, if it's available, the black box trace too. I look forward to your questions and comments!