We've recently been migrating instances of SQL Server between different clusters in order to dismantle an old cluster. To move the jobs we've been copying the MSDB 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, put the job in suspended state and give 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.
Checking the agent log provided the following error message:
Step 1 of job '<jobname>' (<job_id>) cannot be run because the CmdExec subsystem failed to load. The job has been suspended
After further examination of the 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 it turns out, SQL agent stores a path name to the DLL files required to perform the non T-SQL jobsteps in the MSDB. This is documented here: Microsoft KB 903205
Moving (reinstalling) the server caused the directory of these DLL's to change (in this case from MSSQL.6 to MSSQL.12) and the agent was unable to load them. As we rarely use any non T-SQL jobsteps we hadn't run into this problem yet.
The solution was performing an update to the msdb.dbo.syssubsystems table updating the values of subsystem_dll column to the current situation and restarting the agent.
EDIT: It appears only SQL 2005 is affected, SQL 2000 doesn't store these references in the MSDB. I've compiled a script to fix wrong references:
SET NOCOUNT ON
DECLARE @SQLRoot NVARCHAR(512)
IF @@VERSION LIKE ('% 9.%')
BEGIN
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
,N'SOFTWAREMicrosoftMSSQLServerSetup'
,N'SQLPath', @SQLRoot OUTPUT
UPDATE msdb.dbo.syssubsystems
SET subsystem_dll = @SQLRoot+RIGHT(subsystem_dll,LEN(subsystem_dll)-CHARINDEX('MSSQLbinn',subsystem_dll)-5)
WHERE
subsystem_dll NOT LIKE @SQLRoot + '%'
AND subsystem_dll <> '[Internal]'
IF @@ROWCOUNT > 0
BEGIN
PRINT 'Bad references found on '+@@SERVERNAME
PRINT 'Server root: '+@SQLRoot
END
END
18 comments
Hi there
I can’t tell you how helpful this blog has been – I have been banging my head against this for a while now. Most sincere thanks – drinks are on me!
Barry
For any other readers I should add that error messages of the form
[125] Subsystem ‘XXX’ could not be loaded (reason: 126)
are caused by the same issue.
And you even wrote a script!!!
Barry again
Glad I could help !
The script is because I had to fix a lot of instances and I didn’t want to bother finding out which ones to fix manually. So I rolled this script out over all our servers 🙂
BertrandR
Thank you so much for the script.
Just a one question, after executing the script problem fixed, but after
restarting SQL Agent , opening also command line window from sqlagent90.exe and still to be opened.
What can be the reason?
thank you
Dmitry
If I closing this window of Agent it restarting automatically and I get error at event viewer
———–
SQLServerAgent Monitor successfully restarted SQLServerAgent after SQLServerAgent terminated unexpectedly.
———-
Dmitry
Are you using services.msc or the configuration manager to start SQL agent ?
BertrandR
services.msc
and when i’m starting service SQL Agent, now also opening this window
Dmitry
I have no idea why you would get a popup window when starting SQL Agent, it has never happened to me before, does it give any extra information in the agent log ?
BertrandR
It’s a my mistake
Parameter “Allow service to interact with desktop” on SQL Agent was checked
🙂 Thank you again for the script !
Dmitry
Ah that explains it, good luck !
BertrandR
Many thanks to you for not only the explanation of the problem, but for the script as well! I have been fighting these “stored path” problems for the last few days, but when this one happened, I found your page and my problem was solved immediately!
Thank you, thank you, thank you!
Brandon M
Hi,
I am getting this
Message
[125] Subsystem ‘ActiveScripting’ could not be loaded (reason: The specified module could not be found)
I used your script and executed. But when I restart the sql server the error logs are giving the same error.
Ken
Maybe your setup has something I didn’t account for in the script. Do a select on msdb.dbo.syssubsystems and look for the DLL files on your setup, then modify the path’s to the DLLs. Good luck !
BertrandR
You are a genius. Thank you.
DavidB
Thanks !
Henning
I moved MSDBs across instances as well…in hindsight it was a bit more trouble than it was worth. Your fix saved my butt. Thanks
Josh
Great script and explanation–thanks a lot of saving a great deal of my time!
Btw, this script works on SQL 2008 as well! I just commented out the IF check and ran rest of the script as it is. Of course, I thoroughly reviewed the script and verified that the logic would work before I went ahead with it 🙂
Raj
Thanks, gret script, even does not make sense hat for a Disater Recovery scenario when is desirable to do a full restore, this information comes hardcoded in table…
Federico Von