May 4, 2015 at 6:22 am
has anyone seen an issue with xp_instance_regread not returning supposedly correct values? the behavior i think i see is that if the values are blank in SQL2008R2 or prior, they are really blank, but in 2012 (and above?), the registry cannot be read.
I took over an extra 30 or so SQL servers under my umbrella, and i was documenting and making sure everything is set up to my satisfaction.
I went to document the directories for Data/Log and Backup;
this is what i typically might use, since it's supposed to work across all versions of SQL, undocumented or not:
DECLARE @DefaultData varchar(256),
@DefaultLog varchar(256),
@DefaultBackup varchar(256)
EXECUTE [master].dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
@DefaultData OUT
EXECUTE [master].dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
@DefaultLog OUT
EXECUTE [master].dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@DefaultBackup OUT
SELECT @@SERVERNAME As ServerName,
@DefaultData As DefaultData,
@DefaultLog AS DefaultLog,
@DefaultBackup AS DefaultBackup
--MyResults
ServerName DefaultData DefaultLog DefaultBackup
HOL-TFS-P01\TFS2012 NULL NULL D:\SQLBackups
So on a SQL 2012 instance, the data and log directories are blank, which i figure is a setup issue, so i go to the facets to fix them, but they are populated.
I know on 2012 and above, there's a couple of server properties that give the data and log directories (but not the backup directory)
so i cross check with that, and get the same values I saw in the facets.
/*
--Results
InstanceDefaultDataPath InstanceDefaultLogPath
D:\Microsoft SQL Server\MSSQL11.TFS2012\MSSQL\DATA\ D:\Microsoft SQL Server\MSSQL11.TFS2012\MSSQL\DATA\
*/
select
InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')
i looked a little deeper, and i see i get info messages that imply an error in the path, but it seems to be working on 47 out of 50 servers.
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1
(1 row(s) affected)
Lowell
May 4, 2015 at 7:01 am
SSMS reads the information from a number of sources and it's difficult to tell which one it uses when the path is not set.
This is what you get if you trace SSMS while it opens the instance properies window:
DECLARE @HkeyLocal NVARCHAR(18)
DECLARE @ServicesRegPath NVARCHAR(34)
DECLARE @SqlServiceRegPath SYSNAME
DECLARE @BrowserServiceRegPath SYSNAME
DECLARE @MSSqlServerRegPath NVARCHAR(31)
DECLARE @InstanceNamesRegPath NVARCHAR(59)
DECLARE @InstanceRegPath SYSNAME
DECLARE @SetupRegPath SYSNAME
DECLARE @NpRegPath SYSNAME
DECLARE @TcpRegPath SYSNAME
DECLARE @RegPathParams SYSNAME
DECLARE @FilestreamRegPath SYSNAME
SELECT @HkeyLocal = N'HKEY_LOCAL_MACHINE'
-- Instance-based paths
SELECT @MSSqlServerRegPath = N'SOFTWARE\Microsoft\MSSQLServer'
SELECT @InstanceRegPath = @MSSqlServerRegPath + N'\MSSQLServer'
SELECT @FilestreamRegPath = @InstanceRegPath + N'\Filestream'
SELECT @SetupRegPath = @MSSqlServerRegPath + N'\Setup'
SELECT @RegPathParams = @InstanceRegPath + '\Parameters'
-- Services
SELECT @ServicesRegPath = N'SYSTEM\CurrentControlSet\Services'
SELECT @SqlServiceRegPath = @ServicesRegPath + N'\MSSQLSERVER'
SELECT @BrowserServiceRegPath = @ServicesRegPath + N'\SQLBrowser'
-- InstanceId setting
SELECT @InstanceNamesRegPath = N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
-- Network settings
SELECT @NpRegPath = @InstanceRegPath + N'\SuperSocketNetLib\Np'
SELECT @TcpRegPath = @InstanceRegPath + N'\SuperSocketNetLib\Tcp'
/* ...SNIP... */
DECLARE @BackupDirectory NVARCHAR(512)
IF 1 = isnull(cast(SERVERPROPERTY('IsLocalDB') AS BIT), 0)
SELECT @BackupDirectory = cast(SERVERPROPERTY('instancedefaultdatapath') AS NVARCHAR(512))
ELSE
EXEC master.dbo.xp_instance_regread @HkeyLocal
,@InstanceRegPath
,N'BackupDirectory'
,@BackupDirectory OUTPUT
DECLARE @InstallSqlDataDir NVARCHAR(512)
EXEC master.dbo.xp_instance_regread @HkeyLocal
,@SetupRegPath
,N'SQLDataRoot'
,@InstallSqlDataDir OUTPUT
DECLARE @MasterPath NVARCHAR(512)
DECLARE @LogPath NVARCHAR(512)
DECLARE @ErrorLog NVARCHAR(512)
DECLARE @ErrorLogPath NVARCHAR(512)
SELECT @MasterPath = substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name)))
FROM master.sys.database_files
WHERE NAME = N'master'
SELECT @LogPath = substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name)))
FROM master.sys.database_files
WHERE NAME = N'mastlog'
SELECT @ErrorLog = cast(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(512))
SELECT @ErrorLogPath = substring(@ErrorLog, 1, len(@ErrorLog) - charindex('\', reverse(@ErrorLog)))
DECLARE @SmoRoot NVARCHAR(512)
EXEC master.dbo.xp_instance_regread @HkeyLocal
,@SetupRegPath
,N'SQLPath'
,@SmoRoot OUTPUT
/* ...SNIP... */
DECLARE @InstallSharedDirectory NVARCHAR(512)
EXEC master.sys.xp_instance_regread @HkeyLocal
,@SetupRegPath
,N'SQLPath'
,@InstallSharedDirectory OUTPUT
/* ...SNIP... */
SELECT
@BackupDirectory AS [BackupDirectory]
,ISNULL(@InstallSqlDataDir, N'') AS [InstallDataDirectory]
,CAST(@@SERVICENAME AS SYSNAME) AS [ServiceName]
,@ErrorLogPath AS [ErrorLogPath]
,@SmoRoot AS [RootDirectory]
,@LogPath AS [MasterDBLogPath]
,@MasterPath AS [MasterDBPath]
,SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile]
,SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog]
,ISNULL(@InstallSharedDirectory, N'') AS [InstallSharedDirectory]
As you can see, when the path is not set, it probably assumes the path to master and mastlog as default data and log location.
Interestingly enough, the serverporperties appear to be always set, but there's no way to tell how they are calculated. Probably the same logic found in SSMS, or similar. I have always found it to be the same value as in the registry, when set. I have never found a different path between the two, when both set.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply