20-05-2012

Posts Comments

You are here: Home / Articles / SQL Agent MSDB subsystems

SQL Agent MSDB subsystems

Issue:

After the restore of msdb, ALL other SQL Agent subsystems work, but T-SQL. Amongst others, cmdexec replication and so forth.

Jobsteps fail with:

hpj_art_1_1

Jobs are getting the status ’suspended’. I have not found any way of getting the job out of this state. Restart of SQL Agent seemed to do the trick.

hpj_art_1_2

An attempt to start the job once again, results in:

hpj_art_1_3

And an attempt to stop the job again, results in:

hpj_art_1_4

Analysis :

The databases incl. ‘msdb’ was copied from another SQL Server server. On this server, the SQL Server software was installed on the D:\ drive. On the new server, the software was installed on C:\Program Files …

In ‘msdb’, these links exists, between the subsystems og physical files (*sight*) :

SELECT subsystem,subsystem_dll FROM msdb.dbo.syssubsystems

TSQL                             [Internal]

ActiveScripting                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLATXSS90.DLL

CmdExec                          C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLCMDSS90.DLL

Snapshot                         C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLREPSS90.DLL

LogReader                        C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLREPSS90.DLL

Distribution                     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLREPSS90.DLL

Merge                            C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLREPSS90.DLL

QueueReader                      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\sqlrepss90.dll

ANALYSISQUERY                    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLOLAPSS90.DLL

ANALYSISCOMMAND                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLOLAPSS90.DLL

SSIS                             C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL

(11 row(s) affected)

So after moving to another server with another install path, the SQL Agent cannot find these.

Solution:
Update the systemtables with the correct referances (Thanks to Bertrand Rohrbock)

SET NOCOUNT ON
DECLARE @SQLRoot NVARCHAR(512)

IF @@VERSION LIKE ('% 9.%')
BEGIN
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\MSSQLServer\Setup'
,N'SQLPath', @SQLRoot OUTPUT

UPDATE msdb.dbo.syssubsystems
SET subsystem_dll =
@SQLRoot+RIGHT(subsystem_dll,LEN(subsystem_dll)-CHARINDEX('\MSSQL\binn\',subsystem_dll)-5)
WHERE
subsystem_dll NOT LIKE @SQLRoot + '%'
AND subsystem_dll <> '[Internal]'

IF @@ROWCOUNT > 0
BEGIN
PRINT 'Bad references found on '+@@SERVERNAME
PRINT 'Server root: '+@SQLRoot
END

END

Afterwards, the SQL Agent must be restarted.

References:
http://blogs.infosupport.com/blogs/bertrand/archive/2007/11/14/Restoring-MSDB-across-instances_2C00_-a-bad-idea.aspx