We have been recently migrating a lot of physical clusters to virtual. The quickest way to move sql agent jobs was to restore MSDB database. To validate if jobs were in order we executed our System Backup job and surprisingly it went in ‘Suspended’ mode. Investigation for the failed job revealed:
[LOG] Step 1 of job ‘DatabaseBackup – SYSTEM_DATABASES – FULL’ (0xCE446B0C82AA3141B2898110806513A1) cannot be run because the CMDEXEC subsystem failed to load. The job has been suspended
Upon further investigation of the agent log:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
exec xp_readerrorlog 0,2
2015-04-09 14:50:22.000 1 [125] Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'Merge' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found)
2015-04-09 14:50:22.000 1 [125] Subsystem 'PowerShell' could not be loaded (reason: The specified module could not be found)
|
Don’t panic if you see your sql agent job error stating xxxxx ‘
subsytem failed to load’. It certainly does not mean your SQL world is crashing. All it means is the location of the binary files was changed. This was first discovered in SQL2005 and well documented in
KB903205. In total there are 11 subsystems as per my testing for SQL 2008/R2 & 2012. Here is the list:
Test Scenario
Migrating MSDB from SQL instances of 2008R2, 2012 –> SQL 2008R2, 2012
Fix
First thing we need to check is the syssubsystem table and determine the location of the dll’s.
|
|
/*This will show the description for each subsystem*/
select ss.subsystem as Subsystem,sm.text as Description,ss.subsystem_dll,
ss.agent_exe from msdb.dbo.syssubsystems ss
inner join sys.messages sm on ss.description_id = sm.message_id
where sm.language_id = 1033
/*This will skip description*/
select * from msdb.dbo.syssubsystems
|
You will need to pay attention to subsystem_dll column. Check if the files exist in the location. In our case, the old cluster pointed to S:\Program Files\Microsoft SQL Server\MSSQL10_50.xxx\MSSQL\binn\SQLCMDSS.DLL and on the new cluster it was on C:\Program Files\Microsoft SQL Server\MSSQL10_50.xxx\MSSQL\binn\SQLCMDSS.DLL
In order to do this seamlessly run the following command followed by restart of sql agent
1
2
3
4
5
6
7
8
9
10
11
12
|
sp_configure "allow updates",1
reconfigure with override
go
update msdb.dbo.syssubsystems
set subsystem_dll = replace(subsystem_dll,'S:\Program Files','C:\Program Files')
from msdb.dbo.syssubsystems
where subsystem_dll like 'S:\Program Files%'
go
sp_configure "allow updates", 0
reconfigure with override
|
