question

SQLDBA123 avatar image
SQLDBA123 asked

SQL Server 2012 SP2/Win2012 R2 defaultdata file location not showing in registry key

Hi, Installed SQL Server 2012 SP2 EE/Win 2012 R2 Server, defaultdata file location not able to see in registry keys. Is this bug, any one have this kind of experience? How can we resolve this kind of issue. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer --Here not able to see the Defaultdata file locaiton, but able to see defaultlog file location. Thanks
sql-server-2012registry
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

happycat59 avatar image happycat59 commented ·
I think this is normal behaviour. I think what is going on is - by default log and data are in the same place as master database and no registry entry is needed - if you change the default location, the changed location gets written to the registry (and if you only change the location of the log files, only that location is written to the registry) -if you change the default location back to its original location (i.e. undo the above), the registry key gets updated NOT deleted So, I think that what you are observing is "by design"
2 Likes 2 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
If you're on SQL 2012 or above you can get these paths from new server properties: select InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'), InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath') Happycat's theory is quite plausible so I'd be interested to know what these return on your system. It would be better if there was a reliable registry entry fr the path, even if you haven't changed it from the default value.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

happycat59 avatar image
happycat59 answered
Yes - the serverproperty query from David is right way to get this info. SQL gets the info either from the registry or derived from the location of the master (I think) and returns the correct values as per David's query. You should have no need to check what is in the registry Using David's select statement returns the correct values from my system (although I think there was an issue with the correct values that disappeared when I restarted SQL. The old value was being shown instead of the new value. Not sure whether this was me or SQL ... I was a little distracted at the time)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

perrywhittle avatar image
perrywhittle answered
Until the first time the default paths are set the registry keys do not exist. Once updated the keys are created. This is the behaviour I have observed
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.