After a recent server crash, we had the opprotunity to rebuild the SQL Server instance from the previous nights backups. After going through the various steps to restore the Master and MSDB databases, as well as change the location of the tempdb, resource and model databases, we thought everything was set.
Over the weekend, the server in question was set to backup user databases to a file share using SQLCMD. Upon starting the job, the SQL Agent returned the following error message: "Unable to start execution of step 1 (reason: The CMDEXEC subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed."
After looking throught the SQLAGENT.OUT file (which yielded the same error message), we took a look at the MSDB..SYSSUBSYSTEMS table. Looking in the subsystem_dll column, the issue presented itself.
CmdExec D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLCMDSS90.DLL
During the server rebuild, the SQL Server program files where installed under the C:\Program Files directory whereas on the crashed server installation, all the old binaries were installed under D:\Program Files. Since we restored the MSDB database from an old backup, those values overwrote the values from the new server install.
In order for the binaries to load, MSDB..SYSSUBSYSTEMS table had to be updated.
SET subsystem_dll = REPLACE(subsystem_dll,'D;\','C:\')
WHERE subsystem = 'CMDEXEC'
Once the subsystem_dll column has been updated to point to the C:\Program File directory, we restarted the SQL Agent service and checked the SQLAGENT.OUT file again. The error message was gone and the job completed successfully after we restarted it.