Failed to retrieve VerSpecificRootDir for syssubsystems population

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.