SQL Server's Black Box

Traces let you analyze your system's behavior. But analyzing a specific problem can be difficult because the events that led to the problem have already happened. As insurance, you can create a trace that's always running. But besides consuming system resources, such a trace will yield too much information to analyze effectively. You can also try to reproduce the problem. But the easiest solution is to use SQL Server 7.0's Flight Recorder function, which traces the last 100 queries processed. The Flight Recorder information is especially valuable when the system shuts down abnormally.

To start the Flight Recorder, execute the extended stored procedure xp_trace_setqueryhistory 1. This procedure sets the Flight Recorder function to automatically start whenever SQL Server starts. To stop the trace, execute xp_trace_setqueryhistory 0.

You can retrieve the Flight Recorder's output in several ways. When a system-generated error with severity 17 or higher occurs, the system automatically sends the trace output to a file called Mssql7LogBlackbox.trc, which you can open with SQL Profiler. However, a power failure or a sudden server shutdown doesn't give SQL Server time to save the trace to a file. If you can start SQL Server, you can use the extended stored procedure xp_trace_flushqueryhistory 'filename' to manually save the output to a file. You can also use this procedure in normal operations if you want to manually save the last 100 queries to a file.

To test the automatic creation of the Blackbox.trc file as a result of a system-generated error, you can set a database transaction log's properties to not grow automatically, then run a loop of INSERTs to a table, causing the transaction log to fill up. This action will generate the following error:

"Server: Msg 9002, Level 17, State 2, Line 5The log file for database 'database_name' is full."

The Flight Recorder will automatically send the last 100 queries to the Blackbox.trc file.

You can also retrieve the Flight Recorder's output by using the command line utility SQLDiag.EXE, which is in the MSSQL7Binn directory. This utility collects much more information than just the last 100 queries. It also gathers all error logs, Registry information, DLL version information, output from some important system stored procedures (such as sp_configure and sp_lock), input buffer server process IDs and deadlock information, and Microsoft Diagnostics Report for the server, which contains hardware and operating system information that a technical support engineer would need to help you troubleshoot your system.

For SQLDiag to provide certain information, including the last 100 queries, SQL Server must be running. If SQL Server isn't running, SQLDiag will collect all the information it can. SQLDiag saves the Flight Recorder's output to a file called Mssql7LogSqldiag.trc and sends the remaining output to a file called Mssql7LogSqldiag.txt. Even if you aren't using the Flight Recorder, you might want to use SQLDiag as an easy way to collect all your system information in one file.

After running SQLDiag and checking the trace file, you'll see that the last commands are

use [master]declare @P1 nvarchar(50)set @P1=N'd:MSSQL7logSQLdiag.trc'exec xp_trace_flushqueryhistory @P1

SQLDiag executes these commands to send the system history information to the trace file.