DBCC STACKDUMP. Get trace files in SQL Server

GPS Open Source NewsHello everyone.
Today we want to share one of the two ways we have to get trace files when we have errors IN SQL Server, and asking Microsoft support service requires us to send it for analysis. These trace files can be obtained in two ways: using the Sqldumper. exe tool and by using the DBCC STACKDUMP command.

Today we will talk about DBCC STACKDUMP, but in upcoming entries we’ll talk about Sqldumper. exe. If you don’t want to miss it, subscribe to our newsletter.

How does DBCC STACKDUMP work?

This command runs from SQL Server Management Studio or a client such as sqlcmd. Performs a memory dump in a trace file, which is saved in the default path. Taking into account that the test is performed WITH SQL Server 2016 and instance by default, in this case the path is:

<Install_dir>MSSQL13. MSSQLSERVERMSSQLLog

The files created are named SQLDump0001 for the first run, and the number is increased in subsequent executions.

Set a trace flag

To run the command you have to set a trace flag to indicate what trace data you want to find. There are two trace flags that you can use:

  • Trace Flag 2544 – Generates full memory dump
  • Trace Flag 2551 – Generates filtered memory Dump (Full Dump-Data/index pages)

To activate any of the two flags, use the DBCC traceon command

How to use DBCC STACKDUMP?

Once enabled, the DBCC STACKDUMP command is executed and the trace file is generated in the previously commented path.

DBCC STACKDUMP

Be careful with running the command in productive environments, because it can generate locks and waits on the system, and affect the normal operation of the instance.
Once the trace file is generated, it is good practice to turn off the trace flag to avoid these performance problems. To remove the trace flag, use the DBCC TRACEOFF command.

DBCC TRACEOFF

To know the level of Trace Flag that is on, you can Use the DBCC TRACESTATUScommand.
This command returns the level of Trace flag enabled:

1> DBCC TRACESTATUS (-1)
2> Go
TraceFlag Status Global Session
--------- ------ ------ -------
 2551 1 1 0

(1 row affected)
DBCC Execution completed. If there are error messages, consult the system administrator.

If there was no Trace flag enabled, the command does not return error, it simply ends the execution without showing anything.

1> DBCC TRACESTATUS (-1)
2> Go
DBCC Execution completed. If there are error messages, consult the system administrator.

There is official Microsoft documentation for both the DBCC STACKDUMP command and each of the Trace flags. We leave the links to them:

DBCC STACKDUMPCommand Documentation:
https://support.microsoft.com/en-us/help/917825/use-the-sqldumper-exe-utility-to-generate-a-dump-file-in-sql-server

Documentation of the Trace flag 2551 in the following link:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/caaaf57e-21e0-45b3-b501-125c51b6ecba/trace-flag-2551?forum=sqldatabaseengine

The interpretation of the trace file is done by the Microsoft support.

We hope you will be useful. See you in next innings.

A greeting,

Database Equipment