SQL Server subsystems

When SQL Server starts it loads the location of 12 external subsystem dll and exe files into a table in msdb. You can view the contents of this table with this query:

select * from msdb..syssubsystems


exec msdb..sp_enum_sqlagent_subsystems

There is a fairly well known issue that can arise in a cluster where the powershell executable SQLPS.exe is installed in different locations on the two nodes. This ends up causing the syspolicy_purge_history job to fail when the cluster fails over.

There is a stored procedure in the msdb database called msdb.dbo.sp_verify_subsystems that can be called with a parameter of 1 to refresh the table:

use msdb
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1

Automate this by adding to a job set to run at startup.

If you want to dig a bit deeper you can have a look at the code behind the sproc with:

use msdb
sp_helptext 'sp_verify_subsystems'

This will show you where all the registry keys are. Alternatively in the SSMS object explorer open msdb>Programmability>System Stored Procedures and scroll down to the sproc. Right click and select modify. Beware – any modifications will persist if you execute the modified alter script!

This entry was posted in SQL Server. Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s