Whenever you get stuck on a SQL Server issue, generally you call the next layer of support. Whether that next layer is Microsoft or a vendor, a number of new tools are available to communicate with that next layer of support. The SQLDumper.exe and SQLDiag.exe programs can be used to help you better communicate with support to give them an excellent picture of your environment and problem while you reproduce the error.
Beginning in SQL Server 2000 SP3, SQLDumper.exe was included to help your SQL Server perform a dump of its environment after an exception occurs. A support organization such as Microsoft's Product Support Services (PSS) may also request that you execute the program on demand while you have a problem such as a hung server.
If you wish to create a dump file on demand, you need the Windows process ID for the SQL Server instance. There are a few ways you can obtain this ID. You can go to Task Manager, look in the SQL Server log, or go to SQL Server Configuration Manager, covered earlier in the chapter. On the SQL Server 2008 Services page of Configuration Manager, you can see each of the SQL Server services and the process ID.
By default, SQLDumper.exe can be found in the C:\Program Files\Microsoft SQL Server\100\Shared directory, as it is shared across all the SQL Server instances installed on a server. This directory may vary, though, based on where you installed the SQL Server tools. To create a dump file for support, ...