How can I create a dump of SQL Server?

https://blogs.msdn.microsoft.com/askjay/2009/12/29/basic-debugging-concepts-and-setup/

You can create a memory dump of the SQL Server process space in several ways.  There is 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 (sqldumper), using the Debu Gger, and three methods from within SQL Server itself.

First, the These methods would require that the process ID (PID) of SQL Server. Here is a few simple ways to does that:

1. From a command window, execute–> tasklist | find/i "sqlservr" or from the Debugging Tools directory exec ute–> 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 so you had the PID, you can use one of the following ways to create the dump:

Method 1 (Using sqldumper)

The most common (and the same-used internally by SQL Server) are to run SqlDumper.exe. You'll 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 Direc Tory>

More parameters can is seen with sqldumper/?

The common flags are as follows:

0x0120–minidump (This is a dump of just the stacks and loaded modules–this are 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 is very large on a a-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 used to dump any user mode process–not just SQL Server.

Some examples for SQL Server 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 would 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 have 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 the different methods. First, to create a manual dump immediately, use the following undocumented command:

DBCC Stackdump

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

DBCC TRACEON (2544,-1)
Go

DBCC TRACEON (2546,-1)
Go

DBCC STACKDUMP

To create 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 a error. You can use the following-to-enable a full dump on Error 802 (there are 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 with the–y parameter on SQL Server startup to achieve the same functionality as SQL server ' s DBCC DU   Mptrigger. For more information refer to:

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

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

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

–jay

Tags Debug dumps Internals Troubleshooting Windows

How can I create a dump of SQL Server?