Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Agent Error the CmdExec subsystem failed to load


By:   |   Last Updated: 2011-10-07   |   Comments (12)   |   Related Tips: 1 | 2 | More > 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:

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.
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.

subsystem subsystem_dll
ActiveScripting C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlatxss90.dll
CmdExec C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlcmdss90.dll
Snapshot C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll
LogReader C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll
Distribution C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll
Merge C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll
QueueReader C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlrepss90.dll
ANALYSISQUERY C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlolapss90.dll
ANALYSISCOMMAND C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqlolapss90.dll
Dts C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\Sqldtsss90.dll

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


Last Updated: 2011-10-07


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 26, 2017 - 4:43:13 PM - David Hutton Back To Top

 Thank you, fixed an issue I had. 

 

David

 


Thursday, May 04, 2017 - 8:29:35 AM - Raghavendra Back To Top

 

 In my case the path is same as the subsystems and the dll's are existing in the same location as mentioned in the subsystem table, but my jobs are still giving me the same issue "Unable to start execution of step 1 (reason: The SSIS subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended).  The step failed." Let me know if you have any idea


Wednesday, November 25, 2015 - 3:31:33 AM - Christo Back To Top

Wonderful. This helped me alot. Thanks.


Friday, May 23, 2014 - 12:37:35 PM - Vikram Gupta Back To Top
Thanks a lot, you have helped me a lot.

Wednesday, December 18, 2013 - 3:54:23 PM - FS Back To Top

Thanks for the tips! It worked out great!


Sunday, December 08, 2013 - 4:47:14 AM - Aaron Back To Top

Thanks so much for taking the time to post this fix!


Wednesday, October 16, 2013 - 8:30:51 PM - Scott Isom Back To Top

Super job!  This fixed our issue too.

 


Thursday, May 23, 2013 - 3:34:19 AM - venkat Back To Top

Thanks a Lot to u, This article is Superb - I got a same Issue to day, Simply i have followed this Article and finally issue got resolved.


Wednesday, September 05, 2012 - 12:32:48 PM - Kanav Back To Top

 

GOD Bless You - This is just Perfect - especially the way it has been presented..IT really helped me today...

 


Saturday, March 24, 2012 - 6:26:24 AM - Renuka Back To Top

Thank you very much for this article. It saved me during a DR scenario....


Wednesday, October 12, 2011 - 3:13:52 AM - manoj Back To Top

Thanks for the solution.


Friday, October 07, 2011 - 10:44:37 AM - Rohit Garg Back To Top

Nice troubleshooting..........


Learn more about SQL Server tools