How does sys.dm_server_memory_dumps work in SQL Server 2008 R2?

It uses FindFirstFile and FindNextFile to enumerate the files matching a pattern like this:

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.KILIMANJAROX86\MSSQL\LOG\SQLDump*.mdmp*

For each file found, it retrieves its full physical path, name, and extension, its creation time, and its size in bytes, and those three are turned into values of three columns (filename, creation_time, and size_in_bytes) of a resultset.

The pattern used for the search consists of two parts: 1) the path where the files are searched, and 2) a filename followed by an extension. Filename and extension are hardcoded and cannot be changed. They are SQLDump*.mdmp* .

As for the path that it will use to center its search, it is initialized when the Database Engine starts.

It first checks if the value SQLExceptionDumpPath exists under the Setup base key (in my example, it was HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10_50.KILIMANJAROX86\Setup). If that value exists, that is what it will use as the searching path for the lifetime of the process.

If the previous value doesn’t exist in that location, it falls back to whatever is stored in the ErrorDumpDir value, under the CPE key, which hangs from the root Registry key for the instance (in my case, that one corresponded to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10_50.KILIMANJAROX86\CPE).

Now, every time SQL Server has to generate a memory dump, it goes into the Registry, under the Setup key, to see if there is a string value called SQLExceptionDumpPath in there. If there is one, and its data is different than the path resolved during the startup of the service, it uses that path as an alternative where it will save the dump files, and only the dump files. The accompanying text files generated with every dump will still be put in the path originally resolved.

The problem is that the DMV we are talking about today (sys.dm_server_memory_dumps), doesn’t take all those possibilities into consideration. As long as the service (SQL Server service) is not recycled, it will keep on looking for dump files in the directory originally determined during the startup of the service.

Then, if the DBA decides to create the unexisting SQLDumpExceptionPath value once the service has been started, or decides to change the data stored in that value (provided it existed already) to a different path, any new dumps will be generated in that new location, while the DMV will continue to search in the original path only, until the following restart of the SQL Server service.