Yesterday evening, I was going through the SQL Server Agent Error Log on one of our monitoring servers. I saw the below informational message in the Agent Log and this post is an outcome of it.
 There are 12 subsystems in the subsystems cache
What does this message mean? What are the subsystems that it is referring to? These are the SQL Server Agent proxy subsystems that are used while running a job. These subsystems are listed in the Type dropdown box for any job as in the below screenshot.
Ok now we know what is the subsystem. Now from where does the SQL Server Agent get this information when it is starting up? Since it is Agent it should be in MSDB. Yes it is stored in ?syssubsystems table under MSDB database.
SELECT * FROM msdb..syssubsystems
The values in subsystem column are ones which are shown in the Job Step. Each subsystem is dependent on one subsystem_dll and one executable listed in agent_exe. How does the values in these columns and the physical location of these files affect SQL Server?
Let me take this example. One of the instances crashes and SQL Server was reinstalled. Due to some reasons the binaries were installed in E drive instead of the default C drive. After installation MSDB database was restored from the backup. But while trying to run the jobs all of them fail and an error message similar to the one below is logged in the sqlagent.out file.
 Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found)  Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found)
This error is because the files referred to in the subsystem_dll column does not exist. Since the executables were installed in E drive and these files were in C drive earlier (when the MSDB backup was taken). The resolution to this issue issue is real simple. Update all the entries in subsystem_dll to point to the new location. Here is a sample query.
sp_configure "allow updates", 1 go reconfigure with override go update syssubsystems set subsystem_dll= replace(subsystem_dll,'C:\Program Files\Microsoft SQL Server','E:\') FROM syssubsystems where subsystem_dll like 'C:\Program Files\Microsoft SQL Server%' go sp_configure "allow updates", 0 go reconfigure with override go