May be in your DBA carrier you never used SQL Server sqldumper application but for a DBA, you should have the working knowledge of this application. Our focus in this blog is to explain the below things:
1- What is sqlDumper.exe?
2- Why we use it?
3- How can we use it?
What is sqlDumper.exe?
SqlDumper.exe is an application that generally internally called by sql server process during some special kind of situations like Non Yielding schedulers, Non Yielding resource monitor, Deadlock Schedulers, Latch Timeout etc. SQL Server also passes some specific parameters during execution call of application. The output generated by this application will depend on these parameters. This will decide to generate output as mini dump file, filtered dump file or full dump file.
Why we use it?
SqlDumper.exe generates stack dump file as an output that can be used to troubleshoot various kind of problems. Generally these file are required by Microsoft SQL Server Support when we contact them for some specific issues.
How can we use it?
SQL Server uses this internally but we can also use it. SqlDumper.exe comes with sql server installation. It can be found at below location:
SQL Installation Drive:\Program Files\Microsoft SQL Server\number\Shared
Here Sql installation drive may be C or D or E or anything else as per your Sql Server installation.
Number will depend on which version is installed. In my case for SQL Server 2014 it is 120.
1- For SQL Server 2005: 90
2- For SQL Server 2008: 100
3- For SQL Server 2012: 110
4- For SQL Server 2014: 120
Now to use it, open command prompt and run
cd SQL_Installation_Drive:\Program Files\Microsoft SQL Server\number\Shared
Replace SQL_Installation_Drive and number with appropriate value as per your environment.
After this now you can run any one below command in command prompt as per your requirement. Replace the ProcessID with the process id of your sql server for which you wants to generate the dump file. If you don’t know about how to get the process id of sql server then you can click here.
1- Sqldumper.exe ProcessID 0 0x01100 [to generate full dump as an output]
2- Sqldumper.exe ProcessID 0 0x0120 [to generate mini dump as an output]
3- Sqldumper.exe ProcessID 0 0x8100 [to generate filtered dump as an output]
The name of generated dump file, can be found in command prompt screen output as
xxxxDump Completed: SQLDmprxxxx.mdmp
By default dump file will be generate to the same folder where sqlDumper.exe resides. Before generating the dump file, please make sure that the destination drive is having sufficient space. Dump file size can be same as memory consumed by that SQL Server Process.
PS: This is not a general purpose debugging utility and you can’t use it for general purpose debugging.
Prince Kumar Rastogi