Microsoft KB Archive/917825

= How to use the Sqldumper.exe utility to generate a dump file in SQL Server 2005 =

Article ID: 917825

Article Last Modified on 11/30/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Express Edition

-





SUMMARY
The Sqldumper.exe utility is included with Microsoft SQL Server 2005. This article describes how to use the Sqldumper.exe utility to generate a dump file for Watson error reporting or for debugging tasks.



MORE INFORMATION
You can use the Sqldumper.exe utility to generate a dump file on demand for any Microsoft Windows application. For example, you can generate a dump file for debugging an application problem when a computer that is running SQL Server 2005 is not responding to user requests. A dump file can be a mini-dump file, a full dump file, or a filtered dump file.

However, you cannot use the Sqldumper.exe utility for general purpose debugging. For more information about general purpose debugging, visit the following Microsoft Web site:

http://www.microsoft.com/whdc/devtools/debugging/default.mspx

The SQL Server process calls the Sqldumper.exe utility internally to generate a dump file when the process experiences any exceptions. SQL Server passes flags to the Sqldumper.exe utility. You can use trace flags to change the flags that SQL Server passes to the utility in the context of an exception or in the context an assertion. These trace flags are in the range from 2540 to 2559. You can use these trace flags to generate certain types of dump files.

How to obtain a Microsoft Windows application process identifier
To generate a dump file by using the Sqldumper.exe utility, you must have the process identifier of the Windows application for which you want to generate a dump file. To obtain the process identifier, follow these steps:
 * 1) Press CTRL+ALT+DELETE, and then click Task Manager.
 * 2) In the Windows Task Manager dialog box, click the Processes tab.
 * 3) On the View menu, click Select Columns.
 * 4) In the Select Columns dialog box, click to select the PID (Process Identifier) check box, and then click OK.
 * 5) Notice the process identifier of the Windows application for which you want to generate a dump file. For the SQL Server application, notice the process identifier of the Sqlservr.exe process.
 * 6) Close Task Manager.

You can also obtain the process identifier of the SQL Server application that is running on your computer by using the SQL Server error log file. For example, part of the SQL Server error log file resembles the following:

2006-04-18 09:53:03.94 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

Oct 14 2005 00:33:37

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2006-04-18 09:53:03.94 Server (c) 2005 Microsoft Corporation.

2006-04-18 09:53:03.94 Server All rights reserved.

2006-04-18 09:53:03.94 Server Server process ID is 3716.

The number that appears after Server process ID is the process identifier for the Sqlservr.exe process.

How to run the Sqldumper.exe utility
Run the Sqldumper.exe utility under the context of the folder where SQL Server originally installed the utility. By default, the installation path of the Sqldumper.exe utility is as follows:



Note  is a placeholder for the drive where you installed SQL Server 2005.

To generate a dump file by using the Sqldumper.exe utility, follow these steps:  Open the following folder:



 Make sure that the Dbghelp.dll file is in this folder. Click Start, click Run, type cmd, and then click OK. At the command prompt, type the following command, and then press ENTER:

cd :\Program Files\Microsoft SQL Server\90\Shared

 To generate a specific kind of dump file, type the corresponding command at the command prompt, and then press ENTER:  Full dump file

Sqldumper.exe  0 0x01100

 Mini-dump file

Sqldumper.exe  0 0x0120

</li> Mini-dump file that includes indirectly referenced memory

Sqldumper.exe  0 0x0120:40

</li> Filtered dump file

Sqldumper.exe  0 0x8100

</li></ul>

Note  is a placeholder for the process identifier of the Windows application for which you want to generate a dump file.</li></ol>

If the Sqldumper.exe utility runs successfully, the utility generates a dump file in the folder where the utility is installed.

The dump file that the Sqldumper.exe utility generates has a file name pattern that resembles the following:

SQLDmpr .mdmp

In this pattern,  is an increasing number that is determined based on other files that have a similar file name in the same folder. If you already have files in the folder that have file names in the specified pattern, you may have to compare the date and the time that the file was created to identify the file that you want.

<div class="moreinformation_section">

Additional information and considerations
When the Sqldumper.exe utility generates a dump file for a Windows application, the dump file may be as large as the memory that the Windows application is currently using. Make sure that sufficient disk space is available on the drive to which the Sqldumper.exe utility is writing the dump file.

You can specify the directory where you want the Sqldumper.exe utility to write the dump file. The directory must already exist before you run the Sqldumper.exe utility. Otherwise, the Sqldumper.exe utility will fail. Do not use a UNC path as a location for the dump file. The following is an example of how to specify the dump file location of the mini-dump file: <ol> Click Start, click Run, type cmd, and then click OK.</li> At the command prompt, type the following command, and then press ENTER:

cd :\Program Files\Microsoft SQL Server\90\Shared

</li> Type the following command at the command prompt, and then press ENTER:

Sqldumper.exe ProcessID 0 0x0120 0

Note  is a placeholder for the directory where you want the Sqldumper.exe utility to write the dump file.</li></ol>

If you specify a full dump file or a filtered dump file to be generated, the Sqldumper.exe utility may take several minutes to generate the dump file. The time depends on the following variables:
 * The amount of memory that the Sqldumper.exe utility is currently using
 * The speed of the drive to which the utility is writing the dump file

During this time, the Sqldumper.exe utility will not process commands. You will notice that the server has stopped responding. Additionally, a cluster failover may occur.

To run the Sqldumper.exe utility, you must log on to Windows by using one of the following methods:
 * Use an account that is a member of the administrators group on the computer.
 * Use the same user account under which the SQL Server service is running.

For the Sqldumper.exe utility to work successfully through Remote Desktop or through Terminal Services, you must start Remote Desktop or Terminal Services in console mode. For example, to start Remote Desktop in console mode, click Start, click Run, type mstsc /console, and then click OK. Be aware that if the target server runs Windows 2000, the /console option is silently ignored. You can connect to the server through Remote Desktop. But you will not be using the console session.

<div class="moreinformation_section">

If you notice that no dump file has been generated in the current folder after you run the Sqldumper.exe utility, review the information that the utility has generated at the command line to try to determine the possible cause of the failure. This information is also logged in the Sqldumper_errorlog.log file in current directory. The following are two possible error messages and their causes:

Message 1

OpenProcess failed 0x57 - The parameter is incorrect

An invalid Process ID was passed to the Sqldumper.exe utility.

Message 2

Invalid value for thread id - Parameter error

An invalid parameter was passed to the Sqldumper.exe utility.

If an error message that resembles the following is generated, you can safely ignore this message:

Unknown callback type during minidump 6

Unknown callback type during minidump 7

Cluster failovers and the Sqldumper.exe utility
In cluster failover scenarios, the SQL Server resource DLL now can obtain a dump file before the failover occurs. When the SQL Server resource DLL determines that a SQL Server resource has failed, the SQL Server resource DLL uses the Sqldumper.exe utility to obtain a dump file of the SQL Server process. To make sure that the Sqldumper.exe utility successfully generates the dump file, you must set the following three properties as prerequisites:
 * SqlDumperDumpTimeOut

A user-specified time-out. The resource DLL waits for the dump file to be completed before the resource DLL stops the SQL Server service.
 * SqlDumperDumpPath

The location where the Sqldumper.exe utility generates the dump file.
 * SqlDumperDumpFlags

Flags that the Sqldumper.exe utility uses.

If any one of the properties is not set, the Sqldumper.exe utility cannot generate the dump file. A warning message will be logged both in the event log and in the cluster log whenever the resource is brought online.

To set the Sqldumper.exe utility properties for cluster failover, follow these steps: <ol> Click Start, click Run, type cmd, and then click OK.</li> For each property, type the corresponding command at the command prompt, and then press ENTER: <ul> The SqlDumperDumpFlags property

To set the SqlDumperDumpFlags property for a specific kind of dump file, type the corresponding command at the command prompt, and then press ENTER: <ul> All thread full dump file

cluster resource &quot;SQL Server&quot; /priv SqlDumperDumpFlags = 0x01100

</li> All thread mini-dump file

cluster resource &quot;SQL Server&quot; /priv SqlDumperDumpFlags = 0x0120

</li> Filtered all thread dump file

cluster resource &quot;SQL Server&quot; /priv SqlDumperDumpFlags = 0x8100

</li></ul> </li> The SqlDumperDumpPath property

cluster resource &quot;SQL Server&quot; /priv SqlDumperDumpPath=

Note  is a placeholder for the path of the directory that you specify for the dump file. The Sqldumper.exe utility will not create the directory if the directory that you type in the path does not already exist.</li> The SqlDumperDumpTimeOut property

cluster resource &quot;SQL Server&quot; /priv SqlDumperDumpTimeOut=

Note  is a placeholder for the time-out in milliseconds (ms).

The time that the utility takes to generate a dump file of a SQL Server process depends on the computer configuration. For a computer that has lots of memory, the time could be significant. To obtain an estimate of the time that the process takes, use the Sqldumper.exe utility to manually generate a dump file. The valid values for the SqlDumperDumpTimeOut property are from 10000 ms to MAXDWORD. MAXDWORD represents the highest value in the range of the DWORD data type (4294967295).</li></ul> </li></ol>

To remove the Sqldumper.exe utility properties for cluster failover, follow these steps: <ol> Click Start, click Run, type cmd, and then click OK.</li> For a specific property, type the corresponding command at the command prompt, and then press ENTER: <ul> <li>The SqlDumperDumpFlags property

cluster resource &quot;SQL Server&quot; /priv:SqlDumperDumpFlags /usedefault

</li> <li>The SqlDumperDumpPath property

cluster resource &quot;SQL Server&quot; /priv:SqlDumperDumpPath /usedefault

</li> <li>The SqlDumperDumpTimeOut property

cluster resource &quot;SQL Server&quot; /priv:SqlDumperDumpTimeOut /usedefault

</li></ul> </li></ol>

Keywords: kbsql2005tool kbexpertiseadvanced kbhowto kbinfo KB917825

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.