Adding SQL Server post on this BUG, registered in microsoft as BUG # 425161 (SQLBUDT) encountered to me last week. So that it won’t waste time for any other DBA working on same kind of situations.
I have been told to restore the MSDB on to 2005 SQL Instance by my application team from Test to Production Server. . Since Database contains many SQL Agent jobs and DTS Packages without any documentations, They have tested it out on test Servers and wanted the same to be deployed on Production server.
Coming to the point, MSDB got restored successfully. But got below issues right after that…
SQL Agent jobs right after restoring the MSDB database on SQL Server 2005 went to suspended status – when triggering manually or through the schedule.
1) SQL Agent logs shows: Subsystem %s could not be loaded (reason: The specified module could not be found)
2) Job Step History Shows: Unable to start execution of step 1 (reason: The %s subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.
3) SQL Agent logs shows: Subsystem %s could not be loaded (reason: The specified module could not be found)
I tried creating a new test job to see if that works or not, But that again got suspended while running it manually.
This has been a registered bug in micorosoft website as listed below:
by running the below statements:
use msdb go delete from msdb.dbo.syssubsystems exec msdb.dbo.sp_verify_subsystems 1 go
If again the enteries in msdb.dbo.syssubsystems table do not refreshed by doing so as mentioned in the above link, then You have to do everything manually as mentioned in the Option 2) below.
USE msdb GO select * FROM syssubsystems --check the value of subsystem_dll column from the old and new server . On old server, This value was: 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLPGNRDM2T\%' On New server, This value was: 'D:\SQLServer\MSSQL10_50.SQLPGNRDM3P\%' . (b) sp_configure 'allow updates', 1 reconfigure with override (c) update syssubsystems set subsystem_dll= replace(subsystem_dll,'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLPGNRDM2T', 'D:\SQLServer\MSSQL10_50.SQLPGNRDM3P') FROM syssubsystems where subsystem_dll like 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLPGNRDM2T%' . (d) exec sp_configure 'allow updates', 0 reconfigure with override (e) Problem will be resolved after restarting the SQL Agent services