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:\
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:\
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.
I’ve been browsing online more than three hours lately, yet I by no means discovered any interesting article like yours. It is beautiful price enough for me. In my view, if all webmasters and bloggers made good content as you probably did, the web can be a lot more useful than ever before.