During your life as a DBA, you will probably have to restore the master database and all the user databases on a new operating system to bring an already existing SQL Server instance online. One of my clients recently had an issue with hardware failure. Our only option was to install SQL Server on a new OS and use the backup files to restore all the databases from the old SQL Server instance. Once the new system was completely built and we restored msdb, we noticed that some of the SQL Server Agent jobs began to fail. Below is the error output from the job history. It states that the CMDEXEC subsystem failed to load.

This will occur because the installation of the previous SQL Server install was done on a different drive than the newly- built SQL Server installation. There are 2 different ways to fix the issue.

Option 1

If you run the below command in a SQL Server query window on the new instance, you will get the output below and find that the old installation, in this examples, was done on the C: drive. The newly-built SQL Server installation was done on the E: drive, so we have to update the table to correct the paths.

SELECT *
FROM msdb..syssubsystems

In order to update the table, you will have to run the below command and replace the subsystem_dll path for each subsystem to the drive that the new SQL Server installation is on.

sp_configure 'allow updates',1
After you are finished updating the table to the new paths of the SQL Server installation, you will have to restart the SQL Server Agent for the changes to take place.RECONFIGURE WITH OVERRIDE
UPDATE syssubsystems
SET subsystem_dll = REPLACE(subsystem_dll, 'C:Program Files', 'E:Program Files')
FROM syssubsystems
WHERE subsystem_dll LIKE 'C:Program Files%'
sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
--Restart SQL Server Agent

After you are finished updating the table to the new paths of the SQL Server installation, you will have to restart the SQL Server Agent for the changes to take place.

Option 2

You would run the same exact SELECT statement to verify that the paths are not the correct paths of the installation. Then you would run the DELETE statement to truncate the table. Step 3 would be to execute the sp_verify_subsystems stored procedure which will update the subsystem table with the correct information. After a quick restart of the SQL Server Agent, the table will be repopulated, and that will be all that is needed for your jobs to now run successfully.

Step 1
--Look for the existence of the subsystem values
SELECT *
FROM msdb..syssubsystems
Step 2
--DELETE the existing subsystem values
DELETE FROM msdb..syssubsystems
Step 3
--Reload the subsystem values
EXEC msdb.dbo.sp_verify_subsystems 1
--Restart SQL Server Agent.