How can I create a dump of SQL Server?

You can create a memory dump of the SQL Server process space in several ways. There are many external tools that can help you accomplish this such as userdump.exe, debugdiag.exe, and ADPlus.exe. In this post, I’ll cover 3 common ways to accomplish this for SQL Server: The most common way (sqldumper), using the debugger, and three methods from within SQL Server itself.

First, two of these methods will require that you get the process id (PID) of SQL Server. Here are a few simple ways to do that:

1. From a command window, execute --> tasklist | find /i “sqlservr” or from the Debugging tools directory execute –> tlist | find /i “sqlservr”

2. Open task manager, and find sqlservr.exe and get the PID from the PID column. If you don’t see this column, then select View –> Select Columns to add it.

3. In a query window inside of SSMS on the instance you wish to dump, execute –> “SELECT SERVERPROPERTY(‘PROCESSID’)

4. Get the process ID from the SQL Server Errorlog.

Now that you have the PID, you can use one of the following ways to create the dump:

Method 1 (Using SqlDumper)

The most common way (and the way used internally by SQL Server) is to run SqlDumper.exe. You will find SqlDumper.exe in the following directory for a default installation of SQL Server:

C:\Program Files\Microsoft SQL Server\100\Shared

The syntax of SqlDumper is as follows:

SqlDumper <process id (PID)> <thread id (TID)> <Flags:Minidump Flags> <SQLInfoPtr> <Dump Directory>

more parameters can be seen with SqlDumper /?

The common flags are as follows:

0x0120 – Minidump (this is a dump of just the stacks and loaded modules – this is the smallest of the dump types – this is normally the type of dump created automatically by SQL Server during AVs and other exceptions)

0x01100 – Full Dump (this dumps the entire process space – this can be very large on a 64 bit system or any system with a large amount of memory assigned to SQL Server)

0x8100 – Filtered Dump (a filtered dump is a dump of all of Stolen Memory plus certain areas of the buffer pool)

NOTICE: SQLDumper can be used to dump ANY user mode process – not just SQL Server.

Some examples for SQLServer running under SPID 4024 to c:\temp:

Minidump: sqldumper 4024 0 0x0120 0 c:\temp
Full Dump: sqldumper 4024 0 0x01100 0 c:\temp
Filtered Dump: sqldumper 4024 0 0x8100 0 c:\temp

The dump file will have the naming convention of: SQLDmpr####.mdmp (i.e. SQLDmpr0001.mdmp)

Method 2 (Using a debugger)

To dump SQL Server from WINDBG or other debugger, attach the debugger to the process using the PID:

Once connected, just use the .dump command which has the following syntax:

Options are:
/a - Create dumps for all processes (requires -u)
/b[a] - Package dump in a CAB and delete dump
/c <comment> - Add a comment (not supported in all formats)
/j <addr> - Provide a JIT_DEBUG_INFO address
/f - Create a legacy style full dump
/m[acdfFhiprRtuw] - Create a minidump (default)
/o - Overwrite any existing file
/u - Append unique identifier to dump name

“.dump /ma” is the recommend method of creating a complete memory dump of a user mode process.

So, to create a complete memory dump of the SQL Server to c:\temp, execute the following:

.dump /ma c:\temp\sqldump.dmp

Method 3 (From within SQL Server)

From inside SQL Server, you can create a dump using two different methods. First, to create a manual dump immediately, use the following undocumented command:

DBCC STACKDUMP

This will create a memory dump in the LOG directory of your SQL Server instance installation. To enable this method to create a FULL DUMP, you must turn on trace flags 2544 and 2546:

dbcc traceon(2544, -1)
go

dbcc traceon(2546, -1)
go

dbcc stackdump

To create only a minidump, enable trace flag 2546. To create a full-filtered dump, use trace flag 2551.

You can use the undocumented DBCC DUMPTRIGGER command to enable SQL Server to create a dump on the occurrence of an error. You can use the following to enable a full dump on error 802 (There is insufficient memory available in the buffer pool):

-- turn on TFs for full dump
dbcc traceon(2544, -1)
go
dbcc traceon(2546, -1)
go

-- set DUMP TRIGGER for exception 802
dbcc dumptrigger('set', 802)
go

-- view exceptions set for DUMP TRIGGER
dbcc traceon(3604, -1)
go
dbcc dumptrigger('display')
go
dbcc traceoff(3604, -1)
go

-- Turn off dumptrigger for exception 802
dbcc dumptrigger('clear', 802)
go

Finally, you can also use the –y parameter on SQL Server startup to achieve the same functionality as SQL Server’s DBCC DUMPTRIGGER. For more information refer to:

https://blogs.msdn.com/psssql/archive/2008/01/10/how-it-works-sql-server-engine-error-messages.aspx

One method not covered in this post is to create the dump from within Task Manager as seen here:

I am not a big fan of this method since you have little control over it. It will create a full dump inside your user profile by default. It is also only available on Windows 2008 and Windows 2008 R2 (or Vista and Windows 7). However, it is yet another way to dump the SQL Server process.

- Jay