How: Take SQL Server Full Memory Dump from within SQL Server and via Automated way

Sometimes there is need to take full dump for various kind of SQL Server issues and most of the time a DBA don’t know when issue going to occur hence becomes difficult to capture the dump, now to do same we need to have a standard approach, this post is compiled to work as procedure to take required dump file or automate it via trigger to ensure dump is taken even when no one is around,


From inside SQL Server, you can create a dump using two different methods…


First, to create a manual dump immediately, use the following undocumented command:

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


To create only a mini dump, 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 an error. For example, you can use following to enable a full dump on error 802 (There is insufficient memory available in the buffer pool):



Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 263 other subscribers

Translate this blog!



December 2020
« Nov    

View Post by Categories

%d bloggers like this: