Monday, February 23, 2009

SQL Agent job fails with xXx subsystem failed to load

We had a SQLAgent job that runs cmdexec fail. Looking at the job history, the following error was present "The CmdExec subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.". Looking in the SQLAgent log the below errors were reported at startup [125] Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'Merge' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found) After a little research we found that the subsystems (a collection of DLLs) loaded by SQLAgent are stored in a table named "dbo.syssubsystems" in msdb. The entries in dbo.syssubsystems pointed to a location that did not exist. A search for one of the DLLs quickly revealed the actual location of the "missing" DLLs, in our case same drive, but a different folder. Once we had the true location, we updated dbo.syssubsystems.subsystem_dll to reflect to correct location (see script below). After restarting SQLAgent the previously logged errors were not present & the job was able to complete successfully. /* Allow updates to system tables */ sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE /* Update to correct path */ UPDATE msdb.dbo.syssubsystems SET subsystem_dll = REPLACE(subsystem_dll,'C:\Program Files\Microsoft SQL Server\MSSQL.1','C:\Program Files\Microsoft SQL Server\MSSQL.2') FROM msdb.dbo.syssubsystems WHERE subsystem_dll LIKE 'C:\Program Files\Microsoft SQL Server\MSSQL.1%' /* Dis-allow updates to system tables */ sp_configure 'allow updates', 0 RECONFIGURE WITH OVERRIDE /* Verify update */ SELECT * FROM msdb.dbo.syssubsystems

8 comments:

  1. Thanks for that post, had the same problem when we moved a server between environments and you saved me a lot of time and head scratching!

    ReplyDelete
  2. Same here; this blog has been a great help. Much appreciated.

    ReplyDelete
  3. What a time save...excellent...
    Note: in my case this was caused by the fact that I restored msdb from a foreign clustered server. My installation paths had differences.

    ReplyDelete
  4. Worked like a charm. Thanks a bunch!!!

    ReplyDelete
  5. this will happen if on the first node in a cluster you install sql server say on the d drive say, when sql goes off and does its installation piece it doesnt respect that and installs onto the system disk.

    ReplyDelete
  6. great!!! thanks from Davide Sitta. Sinergy S.p.A. Italy.

    ReplyDelete
  7. Thanks for this post.

    ReplyDelete