Error Messages:
Failed to retrieve
VerSpecificRootDir for syssubsystems population
SQLServerAgent could not be started (reason: Failed to load any
subsystems. Check errorlog for details.)
Reason:
As a part of SQL Agent start up
process, process executes the stored procedure msdb.dbo.sp_verify_subsystems to
read the registry values. If the stored procedure throws the execution error,
then agent will go to stop state. You can identify the failure step from Agent
.out error log file.
Below is the part of stored
procedure msdb.dbo.sp_verify_subsystems code, where it is
trying to read the registry values
IF (
(@syssubsytems_refresh_needed=1) OR (NOT EXISTS(select * from syssubsystems)) )
BEGIN
EXEC
master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\Microsoft SQL Server 2019 (RTM)
- 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Licensin
g (64-bit) on
Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)',
N'VerSpecificRootDir', @VersionRootPath OUTPUT
IF
@VersionRootPath IS NULL
BEGIN
RAISERROR(14659, -1, -1) WITH LOG
RETURN(1)
END
EXEC
master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\Microsoft SQL Server 2019 (RTM)
- 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Licensin
g (64-bit) on
Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)\SSIS\Setup\DTSPath',
N'', @DtsRootPath OUTPUT, N'no_output'
IF
(@DtsRootPath IS NOT NULL)
BEGIN
SELECT @DtsRootPath = @DtsRootPath + N'Binn\'
SELECT @DTExec = @DtsRootPath + N'DTExec.exe'
CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
INSERT #t EXEC xp_fileexist @DTExec
SELECT TOP 1 @DTExecExists=file_exists from #t
DROP TABLE #t
IF ((@DTExecExists IS NULL) OR (@DTExecExists = 0))
SET @DtsRootPath = NULL
END
SELECT
@ComRootPath = @VersionRootPath + N'COM\'
DECLARE
@edition nvarchar(256)
DECLARE
@bitness int
SELECT
@edition = @@version
SET
@bitness = CASE WHEN @edition like '%(X64)%' THEN 64 ELSE 32 END
IF
@bitness = 64
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Wow6432Node\Microsoft\Microsoft Sql Server\Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Cor
e-based
Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build
17763: ) (Hypervisor)\Tools\ClientSetup', N'SQLPath', @ToolsPath OUTPUT
ELSE
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft Sql Server\Microsoft SQL Server 2019 (RTM) -
15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Lice
nsing (64-bit) on Windows Server
2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)\Tools\ClientSetup',
N'SQLPath', @ToolsPath OUTPUT
We can see from registry editor
that above highlighted path does not exist
Solution:
Alter the stored procedure to update the correct registry path:
In the code N'VerSpecificRootDir’ exists under the
registry path \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\130
Hence update the code as
EXEC master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\130', N'VerSpecificRootDir', @VersionRootPath
OUTPUT
Similarly update all the paths to the correct locations.
