Getting to Know You(r SQL Server) — Part 2

In my previous post, we gathered some basic information about the connected SQL Server using standard T-SQL commands and formatted it in a nice two-column output. To dig deeper, we’ll need to turn to the undocumented, unsupported (cue scary music) xp_instance_regread. Let’s get things going by finding where the current instance is installed:

SQL Server stores all of its default paths under various registry keys beneath an instance root key. The name of this key is unique for every instance installed on the server. Ordinarily you’d have to jump through a number of hoops to determine the name of this key and modify the registry path accordingly. For example, the actual key (on my server) from the previous example is “Software\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Setup” but xp_instance_regread does all the translation for you. The “Microsoft\MSSQLServer” part of the key is substituted with “Microsoft SQL Server\InstanceName” for the actual API call. So with the gymnastics out of the way, the hard part is simply finding where the data is stored. Here’s what I’ve found interesting:

Instance Registry Key Value Name Description
Software\Microsoft\MSSQLServer\Setup SQLDataRoot Instance Installation Directory
Software\Microsoft\MSSQLServer\MSSQLServer DefaultData MDFs Go Here
Software\Microsoft\MSSQLServer\MSSQLServer DefaultLog LDFs Go Here
Software\Microsoft\MSSQLServer\MSSQLServer BackupDirectory BAKs Go Here
Software\Microsoft\MSSQLServer\CPE ErrorDumpDir SQL Server Error Log Files
Software\Microsoft\MSSQLServer\SQLServerAgent WorkingDirectory SQL Agent Log and Default for Job Output

And if we take the xp_instance_regread command and mail merge it with data from the above table, we get something like this:

Depending on how your server is configured, the EXECUTE for DefaultData and/or DefaultLog may throw an error. That’s because those registry keys only exist if your server’s default data or log directories are different from the directory where the master database was originally installed. In that case, we need to find where master.mdf or mastlog.ldf are located and use that directory for the default:

Unfortunately, xp_instance_regread doesn’t throw a trappable error nor seemingly return any value other than 0, so we have to set the output parameter to Null beforehand and check the value after. Next time, we’ll wrap it up and put a bow on it. We’ll format the output into a single tidy result set and replace the xp_instance_regread calls with a convenient scalar-valued UDF. Finally, we’ll literally put this info at your fingertips.

Leave a Reply

Your email address will not be published.