Debug SQLSERVER (1) to generate a dump file

Debug SQLSERVER (1) generate dump file debugging SQLSERVER (2) use Windbg to debug SQLSERVER's Environment Settings debugging SQLSERVER (3) using Windbg to debug some SQL Server commands, we know that there are two main ways to debug the program: livedebugging (the process hang is the best) Production Environment

Debug SQLSERVER (1) generate dump file debugging SQLSERVER (2) use Windbg to debug SQLSERVER's Environment Settings debugging SQLSERVER (3) using Windbg to debug some SQL Server commands, we know that there are two main ways to debug the program: live debugging (adding a process to make the process hang live) production environment is the best

Debug SQLSERVER (1) to generate a dump file

Debug SQLSERVER (2) use Windbg to debug SQLSERVER's Environment Settings
Debug SQLSERVER (3) Use Windbg to debug some SQL Server commands

We know that there are two main ways to debug a program:

One type is: live debugging (the process is hang attached to the Process). It is best not to use live debugging in the production environment.

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

Now we will introduce how to generate dump files and the differences between various methods.

Step 1: Determine the SQLSERVER process ID

Since four SQLSERVER instances are installed on my machine, four processes are displayed.

Method 1: Enter the following command in the cmd window:

tasklist | find /i "sqlservr"

Method 2: Open Task Management for viewing

Method 3: execute the following SQL statement in SSMS

SELECT SERVERPROPERTY('PROCESSID') AS sqlpid

Method 4: Obtain the process ID from SQL errorlog

EXEC [sys].[sp_readerrorlog] 

Step 2: Generate a DUMP file

Method 1: Use SqlDumper

The most common method is to use the SqlDumper program in SQLSERVER. If you use the default installation path will be

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

Syntax:

SqlDumper 
  
   
    
     
     
    
   
  
 

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

Generally, the flag is as follows:

0x0120-Minidump (only the stack and loaded modules are dumped, which is the smallest dump type and is automatically generated by sqlserver normally)

0x01100-Full Dump (this dump type contains the whole process space. If it is a 64-bit system and SQLSERVER occupies a large amount of memory, the files dumped by dump will be very large)

0x8100-Filtered Dump (Filtered Dump will dump Stolen Memory and buffer pool)

SqlDumper can not only dump SQL Server, but also dump other software to generate dump files.

Example:

Minidump: sqldumper 3116 0 0x0120 0 C:\TempFull Dump: sqldumper 3116 0 0x01100 0 C:\TempFiltered Dump: sqldumper 3116 0 0x8100 0 C:\Temp

There are currently 45 threads in the SQL Process

Minidump file generated

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

Method 2: Use debugger tools

For example, you can use WINDBG or other debugger tools to append a debugger to a process (using a PID ).

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

Download and install it. Of course, you can download the public symbol package at the same time.

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

After the installation, you can view the WINDBG program in the Start menu and click Start.

Select Attach to a Process...

We attach the SQL server process with the process ID 2168.

Once connected, we only need to use the. dump command to generate the dump file. The syntax is as follows:

Options are:/a-Create dumps for all processes (requires-u) to create a dump file for all processes, you need-u option/B [a]-Package dump in a CAB and delete dump. Wrap the dump file in the CAB format and delete the dump file/c.
 
  
-Add a comment (not supported in all formats) to Add comments. all formats are not supported./j
  
   
-Provide a JIT_DEBUG_INFO address provides a JIT_DEBUG_INFO address/f-Create a legacy style full dump to Create a history full dump/m [acdfFhiprRtuw]-Create a minidump (default) create a mini dump file (default option)/o-Overwrite any existing file/u-Append unique identifier to dump name to Append a unique identifier to the dump file name
  
 

The ". dump/ma" command is suitable for creating a complete user-mode memory dump file.

Run the following command to create the mini dump file and place it in the C: \ Temp path.

.dump /ma C:\Temp\sqlexpress_pid2168_dump.dmp

Method 3: Use the built-in SQL Server commands

In SQLSERVER, you can create a dump file in two ways. First, use the following unauthenticated ented command.Manual (manually triggered)Create

DBCC STACKDUMP

This command will generate a dump file in the LOG folder under the installation path of the SQLSERVER instance. To generate a full dump, mini dump, full-filtered dump, different trace flags must be used together.

To generate full dump, run the following command:

--full dumpDBCC traceon(2544, -1) godbcc traceon(2546, -1) godbcc stackdumpgodbcc TRACEOFF(2544,2546, -1) go

Run the following command to generate a mini dump:

--mini dump dbcc traceon(2546, -1) godbcc stackdumpGOdbcc TRACEOFF(2544,2546, -1) GO

Use the following command to generate full-filtered dump:

--full-filtered dump dbcc traceon(2551, -1) godbcc stackdumpGOdbcc TRACEOFF(2544,2546,2551, -1) go

First, let's take a look at where your SQL instance is installed. Open the server properties and see the column in the root directory.

Then execute the above command

Full dump

Mini dump

Full-filtered dump

The dump file sizes are different.

Another method is to use the following unmarshented command.(Automatically triggered by SQLSERVER)Create a DUMP file

DBCC DUMPTRIGGE

The dbcc dumptrigger command triggers dump when an error occurs. Of course, you can specify that dump is triggered when a specific error occurs.

You can use the following command to trigger a 701 Error

-- Turn on TFs for full dump dbcc traceon (2544,-1) go dbcc traceon (2546,-1) GO -- set dump trigger for exception 701 DBCC dumptrigger ('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' WITH BU FFERCOUNT = 999999999, FORMAT -- message 3013, level 16, status 1, 2nd rows -- backup database is terminating abnormally. -- Message 701, Level 17, status 17, 2nd rows -- resource pool 'default' does not have enough system memory to run this query. -- 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 701 DBCC dumptrigger ('clear', 701) GO

In this case, the generated dump file is displayed in the LOG folder.

Method 4: Add SQL server startup parameters

-Y: the startup parameter can perform similar functions of the dbcc dumptrigger command during SQL startup.

For more information refer:

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

Method 5: Right-click the task manager and choose create dump file.

This method is not too recommended, not too controllable

When you click "create dump file", Windows creates a full dump file.

This functionOnlyUsed on Windows 2008, Windows 2008 R2, Vista, and Windows 7

The following describes how to use Windbg to debug the environment settings of SQLSERVER.

References

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

Welcome to Brick o (∩ _ ∩) o