SQL Agent Error the CmdExec subsystem failed to load

By: Rajendra Gupta   |   Updated: 2011-10-07   |   Comments (13)   |   Related: 1 | 2 | > SQL Server Agent

Problem

We've recently been migrating instances of SQL Server between different clusters in order to dismantle old clusters. To move the jobs and history we've been restoring the MSDB database from the old instance to the new one by performing a restore and we didn't run into any problems ... until yesterday. A job that used CmdExec instead of a T-SQL jobstep wouldn't run and it put the job in a suspended state and gave the following error:

Solution

After further examination of the SQL Agent log I found the following startup errors:

• Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found)
• Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found)
• Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found)
• Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found)
• Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found)
• Subsystem 'Merge' could not be loaded (reason: The specified module could not be found)
• Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found)
• Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found)
• Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found)
• Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found)

As per Microsoft KB 903205, SQL Agent stores a path to the DLL files required to perform the non T-SQL jobsteps in MSDB. Since we moved (reinstalled) the server this caused the directory of these DLL's to change and the agent was unable to locate and load them.

The default locations of the subsystem_dlls should be as follows unless SQL Server was installed on another drive.  You can check your server folders to see where these files exist.

So we checked the syssubsytems table, using the below query, and found the path was "E:\Program Files\..." instead of "C:\Program Files\...".  The reason for this was that on the old server SQL was installed on the E: drive, but on the new server SQL was installed on the C: drive.

select * from msdb.dbo.syssubsystems

So we needed to update the subsystem_dll paths to point to "C:\Program Files\...".  Since we are updating a system table we need to run sp_configure to allow updates to system tables and at the very end we turn off this feature so an update is not made accidently to the system tables.  Also, after this update we needed to restart the SQL Server Agent service for the changes to take effect.  Once this was done everything worked without a problem.

sp_configure "allow updates", 1 
reconfigure with override 


update syssubsystems 
set subsystem_dll = replace(subsystem_dll,'E:\Program Files','C:\Program Files')  
from syssubsystems 
where subsystem_dll like 'E:\Program Files%' 


sp_configure "allow updates", 0 
reconfigure with override


--Restart SQL Server Agent
 

Next Steps

• If you run into this issue, make the change to this system table and restart SQL Agent for the changes to take effect.
• Moving msdb is probably not a common practice, but if you do end up restoring the database to a different server and run into this issue remember this tip.
• Check out these other tips related to SQL Server Agent

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips