SQL Agent job SUSPENDED after restoring msdb database in SQL 2005 (BUG # 425161))

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…

Issue: 

.

SQL Agent jobs right after restoring the MSDB database on SQL Server 2005 went to suspended status – when triggering manually or through the schedule. 

.

Cause:

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.

.

Workaround:

1)

This has been a registered bug in micorosoft website as listed below:

http://support.microsoft.com/?kbid=914171

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.

2)

(a)

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



About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: