Recently, we published a PowerShell script to create Memory dumps of Sql Server, SSAS, SSRS, and SSIS - called it SQLDumpHelper.ps1 . The PS script uses SQLDumper.exe and it makes it very easy to generate the dumps by avoiding having to look up SQLDumper.exe command switches.

I wrote the PS script as an extension to automatically generating a dump via a batch file initially, but also extended it to other SQL Server products and made it interactive for the user.

Where to Get it: You can copy the script code from KB article 917825. (Chose the Code Details link to show the entire script)

How to Run it: Once you save the text into SQLDumpHelper.ps1 and execute it under Administrator privilege, you will see these screens. Below is an example of how to generate multiple SQL Server memory dumps.

Next, if there are multiple SQL Server instances, you must type the PID for the SQL Server instance you want to target

Next, you can select the type of memory dump you need to generate. In most cases you will pick Mini-Dump with Referenced Memory. This choice provides the balance of collecting a small dump (which means short time to freeze the server for dump collection) and getting sufficient amount of memory for dump analysis. Filtered dumps are desired, but on most SQL Server systems that have lots of memory today it is not practical to collect this type. That is, it takes a significant amount of time to write the bits to disk and therefore the process is frozen for a longer period of time (see KB article 917825 for details on Dump Types)

Want multiple memory dumps? Select 'y' and specify how many and at what interval.

And finally, a list of the memory dump files in your destination folder is displayed

Namaste

Joseph