SQL Agent Jobs getting Suspended State

Today we had to do some maintenance on one SQL Server and had to restore MSDB database. After restoring MSDB database we found that few of our SQL jobs were in Suspended state and we could not re-run them.

SQL Agent log showed messages like “Subsystem %s could not be loaded (reason: The specified module could not be found)”

This happens because of issues with the SQL Server Agent subs system information which  can be obtained frommsdb.dbo.syssubsystems table.

SELECT * FROM msdb.dbo.syssubsystems

In our case the information was not correct and hence I DELETED and repopulated the table.

–- Delete the existing rows.

DELETE FROM msdb.dbo.syssubsystems

-– Re-fill the table with new rows pointing to the proper location of the DLLs.

EXEC msdb.dbo.sp_verify_subsystems 1

Problem should be solved after restarting SQL Server Agent.