Debug SQL Server (i) How to generate a dump file

Debug SQL Server (i) How to generate a dump file

Debug SQL Server (ii) Debugging environment settings for SQL Server using WinDbg
Debug SQL Server (iii) Some commands for debugging SQL Server using WinDbg

We know that there are two main ways of debugging a program

One is: Live debugging (attach process to keep process hang) production environment best not live debugging

One is: Post-mortem debugging or reading dump files (generate dump file and analyze it)

Now let's look at how to generate the dump file, and the differences between the various methods

The first step : Determine the process ID of SQL Server

Because my machine has four instances of SQL Server installed, I see four processes

Method 1: Enter the following command in the CMD window

| /i "sqlservr"

Method 2: Open Task management to view

Method 3: Execute the following SQL statement in SSMs

SELECT Serverproperty ('PROCESSID' as Sqlpid

Method 4: Get the process ID from SQL errorlog

EXEC [sys]. [sp_readerrorlog]

Step Two : Generate dump File

Method 1: Use SQLDumper

The most common approach is to use the SQLDumper program inside SQL Server, and if you use the default installation path, installation path will be

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

The syntax is as follows:

<process ID (PID)><thread ID (TID)><flags:minidump Flags><sqlinfoptr><Dump Directory>

If you are not familiar with the syntax, you can use the/? View Help

In general, we will use the following flag:

0x0120–minidump (only dump the stack and the loaded module, which is the smallest dump type, and this is the dump type that SQL Server automatically generates under normal conditions)

0x01100–full dump (This dump type contains the entire process space, if it is a 64-bit system and SQL Server takes up a lot of memory then the dump file will be very large)

0x8100–filtered dump (Filtered dump dumps the Stolen memory and buffer pool portions)

SQLDumper This tool not only can dump SQL Server, can also dump out other software, and then generate dump file

Example:

Minidump:sqldumper3116 0 0x0120 0C +Temp Full Dump: SQLDumper3116 0 0x01100 0C +TempFilteredDump: SQLDumper3116 0 0x8100 0C +Temp

There are currently 45 threads in the SQL process

The generated minidump file

The naming rules for dump files are generally:sqldmpr####.mdmp

Method 2: Use debugger Tools

For example, use WinDbg or other debugger tools to attach debugger to a process (using PID)

WinDbg: http://msdn.microsoft.com/en-us/windows/hardware/hh852365

Download it for installation and, of course, you can download the public symbol package at the same time

: Http://msdn.microsoft.com/zh-cn/windows/hardware/gg463028#Download_windows

Once installed, you can start the menu to see the WinDbg program and click on it to launch

Select Attach to a Process ...

We attach to this SQL Server process with a process ID of 2168

Once connected, we only need to use the. dump command to generate a dump file with the following syntax:

Options are:/A- CreateDumps for  AllProcesses (requires-u) Create dump file for all processes required-u Options/B[a] -PackageDump inchA CAB and Delete Dumppackage dump file for CAB format and then delete dump file/C<Comment> - ADDA comment ( notSupportedinch  Allformats) Add comments that do not support all formats/J<Addr> -provide a jit_debug_info address provides a jit_debug_info/F- CreateA legacy style Full DumpCreate a History fullDump/M[Acdffhiprrtuw] - CreateA minidump (default) Create a mini dump file (default option)/O-Overwrite anyExistingfileoverwrite any files that already exist/U-AppendUniqueIdentifier to DumpName append unique identifier to dump file name

The ". Dump/ma" command is appropriate for creating a full user-state memory dump file

Use the following command to create a mini dump file for SQL Server placed under the C:\Temp path

. Dump /ma \ cTemp\sqlexpress_pid2168_dump.dmp

Method 3: Use SQL Server built-in commands

In SQL Server, you can create a dump file using two methods, first, manually (manually triggered) using the undocumented command below.

DBCC Stackdump

This command generates a dump file in the log folder under the SQL Server instance installation path, to generate the full Dump,mini dump,full-filtered dump needs to match the different trace flags

To generate full dump use the following command

--Full DumpDBCCTraceon2544,-1) GoDBCCTraceon2546,-1) GoDBCCStackdumpGoDBCCTraceoff (2544,2546,-1) Go

Generate mini dump using the command below

--DBCC traceon (2546-1goDBCC  StackdumpGODBCC traceoff (2544,2546-1  GO

Generate full-filtered dump using the following command

--DBCC traceon (2551-1goDBCC  StackdumpGODBCC traceoff (2544,2546,2551- 1  Go

First look at where your SQL instance is installed, open the server properties, see the root directory column

Then execute the above command

Full dump

Mini Dump

Full-filtered Dump

You can see that the dump file is not the same size

Another way is to create a dump file using the following other undocumented command (SQL Server Auto-trigger)

DBCC Dumptrigge

The DBCC dumptrigger command triggers a dump when an error occurs, and you can, of course, specify when a particular error occurs.

You can use the following command to trigger when a 701 error occurs

--turn on TFs for full dumpDBCCTRACEON (2544,-1) GO DBCCTRACEON (2546,-1) GO--set DUMP TRIGGER for exception 701DBCCDumptrigger ('Set',701) GO--Exception 701 occurBACKUP DATABASE [TEMP2]  to DISK ='E:\Temp21FULLBACKUP.BAK' ,DISK = 'E:\Temp22FULLBACKUP.bak',DISK = 'E:\Temp23FULLBACKUP.bak',DISK = 'E:\Temp24FULLBACKUP.bak',DISK = 'E:\Temp25FULLBACKUP.bak',DISK = 'E:\Temp26FULLBACKUP.bak' withBUFFERCOUNT=999999999, FORMAT--msg 3013, Level 16, State 1, line 2nd--BACKUP DATABASE is terminating abnormally. --msg 701, Level 17, State 17, line 2nd--The resource pool ' default ' does not have enough system memory to run this query. --View exceptions set for DUMP TRIGGERDBCCTRACEON (3604,-1) GO DBCCDumptrigger ('Display') GO DBCCTraceoff (3604,-1) GO--Turn off Dumptrigger for exception 701DBCCDumptrigger ('Clear',701) GO

You will see the generated dump file in the log folder.

Method 4: Add the startup parameters for SQL Server

–y: Startup parameters can complete the DBCC DUMPTRIGGER command similar functions when SQL is started

For more information refer to:

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

Method 5: Create a dump file by right-clicking in Task Manager

This method is not too recommended, not too controllable

When you click the button "Create dump File", Windows creates a full dump file

This feature can only be used on Windows 2008, Windows R2, Vista, Windows 7

The next article explains environment settings for debugging SQL Server using WinDbg

Reference articles

Http://blogs.msdn.com/b/askjay/archive/2010/02/05/how-can-i-create-a-dump-of-sql-server.aspx

Welcome everyone to shoot brick O (∩_∩) o

Debug SQL Server (i) How to generate a dump file