Sql log shipping – CMDEXEC subsystem failed to load 1

We came cross strange problem related to subsystem failure which says The CMDEXEC subsystem failed to load after log shipping configuration in Sql server 2005. The task was to re-install Sql server 2005 in 64 bit version earlier it was in 32 bit.

As per scheduled activity, we have re-installed SQL Server 2005 in 64 bit version after removing 32 bit on the same hardware. Once installation of SQL Server 2005 completed, we configured log shipping as DR alternative.

After configuration of Log shipping, we wanted to check the basic functionality of log shipping to avoid any surprises in future. So while in the process of testing, first thing we need to perform the backup as we knows whenever we configure log shipping backup, copy and restore job get created on their respective system.

Well always do a throw testing before releasing system on production environment, here we found that backup jobs are failed due to subsystem failure as shown in the 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.”

As error suggests, we have some issues in the syssubsystems table which located in msdb database. So checked syssubsystems table, it was showing ‘C:\Program Files (x86)’ instead of ‘C:\Program Files’ in the susbsystem_dll column that should be updated due to reinstallation of sql server 2005 in 64 bit.

Use msdb

Select * from msdb.dbo.syssubsystems

Now we need to update this value with new value, before update any system table, you have to reconfigure settings by executing below statement.

sp_configure ‘allow updates’, 1

Reconfigure with override

Here we are updating old values from syssubsystems with new values.

Update syssubsystems

Set subsystem_dll = replace (subsystem_dll,’C:\Program Files (x86)’,’C:\Program Files’)

 

Verify updated value by executing below query in sql server.

Select * from syssubsystems Where subsystem_dll like ‘C:\Program Files (x86)%’

sp_configure ‘allow updates’, 0

Reconfigure with override

 

Restart the SQL Server Agent service and check the functionality again.

Everything is working fine after updating values in syssubsystem.