How to generate a dump for a SQL Server error using DBCC Dumptrigger

Last week I wrote a post on Distribution agents failing with Unknown Security error, While we still dont know the root cause of the issue other than the possibility of Memory Pressure, there was a reoccurrence early this week. This time around we wanted to collect all the data that is possible and then give it to Microsoft for analysis. Along with the different data that we collected we decided to take dumps during that time.

That was a small challenge as we exactly dont know when to take a dump, as we were not sure when the error would pop up. A dump on SQL Server will not work as SQL Server was not the faulting application. We then decided that as we are getting an error in Sql Server we will trap that error. So this post will detail around how to take a SQL Server dump on a particular error.

There is a DBCC command “DBCC Dumptrigger” which can be used to generate dumps on various error numbers. There are three different parameters that we can use along with the DBCC Dumptrigger. I will explain on all of those.

So let us start with how to set SQL Server to take a dump on a particular error. For the ease of reproducing I am picking this error.

Now we need to set the error number 208 and take a dump whenever this error occurs in SQL Server. For that we need to run this command

DBCC Dumptrigger (‘SET’,208)

Once you run this command you get an output similar to this.

Now that you have enabled DBCC Dumptrigger to generate a dump on error 208, let us see how we can confirm that.

For that first you have to enable a trace flag 3604, this is enabled to see the output in the result pane. If not enabled, we will not be able to see the output for DBCC Dumprtrigger command.

DBCC TraceON (3604,-1)

Once the trace flag is enabled run this command to see whether the dump has been configured properly.

DBCC DumptTrigger (‘display’)

You will see an output like below.

Now let us see how this will generate a dump based on the error number.

Let us try to run a simple select from a table which doesn’t exist, it will error out and also generate the dump. The dump file will be located in the default SQL Server ERRORLOG folder. Also it will be printed in your SQL Server error log.

2011-06-24 12:38:02.030 spid53 Server was configured to produce dump on the following event – ex_raise2: Exception raised, major=2, minor=8, state=1, severity=16, attempting to create symptom dump
2011-06-24 12:38:02.430 spid53 Using ‘dbghelp.dll’ version ‘4.0.5’
2011-06-24 12:38:02.480 spid53 **Dump thread – spid = 0, EC = 0x10E9B748
2011-06-24 12:38:02.480 spid53 *
2011-06-24 12:38:02.480 spid53 * User initiated stack dump. This is not a server exception dump.
2011-06-24 12:38:02.480 spid53 *
2011-06-24 12:38:02.480 spid53 ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0004.txt
2011-06-24 12:38:02.490 spid53 * *******************************************************************************
2011-06-24 12:38:02.490 spid53 *
2011-06-24 12:38:02.490 spid53 * BEGIN STACK DUMP:
2011-06-24 12:38:02.490 spid53 * 06/24/11 12:38:02 spid 53
2011-06-24 12:38:02.490 spid53 *
2011-06-24 12:38:02.490 spid53 * Server was configured to produce dump on the following event – ex_raise2: Exception raised, major=2, minor=8, state=1, severity=16
2011-06-24 12:38:02.490 spid53 *
2011-06-24 12:38:02.520 spid53 * Input Buffer 72 bytes –
2011-06-24 12:38:02.520 spid53 * select * from sqllearnings
2011-06-24 12:38:02.520 spid53 *

Note that the size of the dump depends on whether any other trace flags are enabled and also depends on the RAM available on the Machine and whether it is a 32 bit or 64 bit system.

Now that we have set the dumptrigger, checked whether it is properly set and all it is also super important to disable the Dumptrigger. Else you might get ‘n’ number of dumps and cause further problems. So always make sure that as soon as the required dumps are captured, disable Dumptrigger.

To disable dump trigger you need to run the below command.

DBCC Dumptrigger (‘CLEAR’, 208)

Now you can run DBCC Dumptrigger (‘DISPLAY’) to check whether the dumptrigger is disabled.

NOTE: PLEASE take utmost care when trying to use any of these commands, as mistakes can cause lot of unexpected errors. These should be only tried by an experienced Senior SQL DBA.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s