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:

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.

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

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