Getting Help from Support | Professional SQL Server 2005 Administration (Wrox Professional Guides)

Whenever you get stuck on a SQL Server issue, generally you'll call the next layer of support. Whether that next layer is Microsoft or a vendor, there are a number of new tools that you have available to communicate with that next layer of support. The SQLDumper.exe and SQLDiag.exe programs can be used to help you better communicate with support to give them an excellent picture of your environment and problem while you reproduce the error.

SQLDumper.exe

Beginning in SQL Server 2000 SP3, SQLDumper.exe was included to help your SQL Server perform a dump of its environment after an exception occurs. A support organization like Microsoft's Product Support Services (PSS) may also request that you execute the program on demand while you have a problem like a hung server.

If you wish to create a dump file on demand, you'll need the Windows process ID for the SQL Server instance. There are a few ways you can obtain this ID. You can go to Task Manager, look in the SQL Server log, or go to SQL Server Configuration Manager, explained earlier in this chapter. On the SQL Server 2005 Services page of Configuration Manager, you can see each of the SQL Server services and the process ID.

By default, SQLDumper.exe can be found in the C:\Program Files\Microsoft SQL Server\90\Shared directory, as it's shared across all the SQL Server instances installed on a server. This directory may change, though, based on where you installed the SQL Server tools. To create a dump file for support, go to a command prompt and go to the C:\Program Files\Microsoft SQL Server\90\Shared directory. Once there, you can create a full or a minidump. A full dump is much larger than a minidump. If a minidump is less than a megabyte, a full dump may run 110MB on your system. To create a full dump, use the following command:

 Sqldumper.exe <ProcessID> 0 0x01100

This will output the full dump to the same directory that you're in. The filename will be called SQLDmpr0001.mdmp if this is the first time you've run the SQLDumper.exe program. The filename would be sequentially named after each execution. You won't be able to open the dump file in a text editor like Notepad. Instead, you'll need advanced troubleshooting tools like Visual Studio or one of the PSS tools. A more practical dump would be a minidump, which contains most of the essential information the support will need. To create a minidump, use the following command:

 Sqldumper.exe <ProcessID> 0 0x0120

You can view the SQLDUMPER_ERRORLOG.log file to determine if there were any errors when you created the dump file or if a dump has occurred. You will need to be a local Windows administrator to run SQLDumper.exe or be logged in with the same account that starts the SQL Server service.

SQLDiag.exe

A tool that's slightly less of a black box than SQLDumper.exe is SQLDiag.exe. If you're experienced with SQL Server 2000, you may be familiar with a tool called PSSDiag.exe, which produced SQL and Windows trace files as well as system configuration logs. SQLDiag.exe has replaced this tool and has added many new features to the old PSS tool. The tool consolidates and collects information about your system from:

Windows System Monitor (sysmon)
Windows Event Logs
SQL Server Profile traces
SQL Server error logs
Information about SQL Server blocking
SQL Server configuration information

Because SQLDiag.exe gathers so much diagnostic information, you should only run it when you're requested to or when you're trying to prepare for a call with support. The SQL Server Profiler trace files alone can grow large quickly, so prepare to output these files to a drive that has lots of space. The process also uses a sizable amount of processing power as it runs. You can execute the tool from a command prompt or as a service. You can use the /? switch to be shown what switches are available to you.

As you can see from the preceding switches, SQLDiag.exe can take a configuration file as input. By default, this file is called SQLDiag.Xml if one is not specified. If a configuration XML file does not exist, one will be created called ##SQLDiag.XML. This file can be altered to your liking and then later distributed as SQLDiag.XML.

Now that you know what SQLDiag.exe can do, follow this example to use the tool against a local development server. If you cannot get in front of the server, you will have to use a support tool like Terminal Services to remote into a server, since you can't point SQLDiag.exe at a remote instance. To run the tool, go to a command prompt. Because the tool is referenced in the environment variables, you won't have to go to the individual directory where the file is located. Instead, for the purpose of this example, go to the C:\Temp directory or something similar to that on a drive that has more than 100MB available.

The default location for the files is C:\Program Files\microsoft sql server\90\tools\Binn\SQLDIAG, but you can alter that to a new location with the /O switch. In this example, type the following command (note the lack of spaces after the + sign):

 sqldiag /B +00:03:00 /E +00:02:00 /OC:\temp /C1

This command will instruct SQLDiag.exe to begin capturing trace information in three minutes from when you start and run for two minutes. This is done with the /B and /E switches. These two switches can also be used to start and stop the diagnostic at a given 24-hour clock time. The command also tells SQLDiag.exe to output the results of the traces and logs to the C:\temp directory, and the /C switch instructs the tool to compress the files using Windows compression. If you were running this in your environment, you would wait until you were instructed by SQLDiag.exe (in green text on your console) to attempt to reproduce the problem. The results will look something like Figure 4-13.

image from book
Figure 4-13

With the SQLDiag.exe now complete, you can go to the C:\temp directory to zip the contents up and send them to Microsoft. In the directory, you'll find a treasure chest of information for a support individual. Some of the items you'll find there will include:

##files.txt - A list of files in the C:\Program Files\Microsoft SQL Server\90\Tools\binn directory with their creation date. This can be used to see if you're not running a patch that support has asked to be installed.
##envvars.txt - A list of all the environment variables for the server
SERVERNAME__sp_sqldiag_Shutdown.OUT - A consolidation of the instance's SQL logs and the results from a number of queries
log_XX.trc - A series of Profiler trace files of very granular SQL Server activities being performed
SERVERNAME_MSINFO32.TXT - A myriad of details about the server system and hardware

These files may not only be useful for support individuals. You may want to consider running this on a regular basis to establish a baseline of your server during key times (before patches, monthly, or whatever your metric is). If you'd like to do this, you wouldn't want the Profiler part of SQLDiag.exe to run for more than a few seconds. You can gather useful baseline information if the tool is run in snapshot mode periodically. This mode performs the same functions as before but will exit immediately after it gathers the necessary information. The following command uses the /X switch to run SQLDiag.exe in snapshot mode and the /N switch (with 2 as the option) to create a new directory for each run of SQLDiag.exe:

 sqldiag /OC:\temp\baseline /X /N 2

Each directory will be called baseline_0000 and then named sequentially from there. Many corporations choose to run this through SQL Agent or Task Manager on the first of the month or before key changes to have an automatic baseline of their server and instance.