SQL Agent Job Error CMDEXEC subsystem failed to load

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:

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:

subsystem

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

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

If you do not like messing with MSDB and want your SQL Jobs to be moved between instances, there is an excellent article by Ahmad Osama on  move jobs using powershell

~ Adios

Khan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |

Follow me on Twitter

 

Avatar

About Arsalan Khan

People know me as 'Khan' ... nope not Shah Rukh's movie but Star Trek II - Wrath of Khan and since than 'Khan' it has been. Born and raised in Dubai before moving to US for my masters. Been blessed with solid 8+ yrs with SQL (and counting) and currently working for the biggest publishing house as Sr. Database Administrator. I have had an opportunity to speak about tips & tricks to write efficient tsql for Quest International Users Group (PeopleSoft). Love playing TT, badminton and thoroughly enjoy watching cricket when not occupied with my loving daughter. Expertise in DR, Performance Tuning, Troubleshooting and Problem Solving. With that being said I have finally decided to roll my sleeves up and give back to the community bit by bit. Finally.. as my wonderful wife puts it ... "if you don't have a smile, I will give you one of mine ~ Rabia Khan" ~Cheers

View all posts by Arsalan Khan →

Leave a Reply

Your email address will not be published. Required fields are marked *