Today we are going to see two trace flags which is going to help for troubleshooting purposes. These two trace flags will get you full SQL Server dump by using the undocumented DBCC command.DBCC STACKDUMP command will take the dump of SQL Server. In some situations you are required to take full memory dump of SQL Server for entire analysis. There are other options also to do this however you can do the same thing using this DBCC command. This DBCC command also accepts SPID or User ID as parameter, so if you are going to generate full memory dump make sure you pass the parameter else the file size will be big and you will end of loosing disk space.
Let me do a test, I’m first going to run stackdump command to generate Mini dump file with portion of memory. Use the command below to get the dump file, as shown below you can see that it has generated mini dump with porition of memory.
DBCC STACKDUMP GO
Let’s enable the trace flag to get full memory dump. Use the command below, from the image below you can see that it has generated mini dump with full memory.
DBCC TRACEON(2544,-1) GO DBCC TRACEON(2546,-1) GO DBCC STACKDUMP GO
To generate only mini memory dump you need to turn on trace flag 2546 only. Thus using this trace flag will give you full memory dump for analysis or else forwarding it to MS support.
Leave a Reply