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