I ran into something peculiar today.

I have a bunch of standard Sql Agent jobs on my Sql Server 2008.  Along with a few maintenance jobs (“Database Integrity Check “, ‘Cleanup History’, etc), there are some business process jobs i.e. ‘Purge’, ‘Expire Old Products’, ‘Process Reports’, etc. Some of these jobs are not listed in SSMS under ‘Sql Agent >> Jobs‘. They can be queried from ‘sysjobs’ table in MSDB database.

On further investigation, I see that all these un-listed [in SSMS (Sql Agent >> Jobs)] jobs are either in 0 or 4 category. These jobs were originally created when the application was on Sql Server 2000. But since then it was upgraded to Sql Server 2005 and Sql Server 2008. If you look at this MSDN article, only 3 categories are supported starting Sql 2005 i.e. 1, 2, 3.  So these jobs categories (0 and 4) are not part of the standard list in Sql Server 2005/2008/2012.

So my guess is that, because of this unexpected category, SSMS is not listing them under “Sql Agent >>  Jobs“. May be the query that SSMS runs to list out jobs is something like “SELECT * FROM msdb.sysjobs WHERE category_id IN (1, 2, 3)”. So it is not displaying these jobs with different categories.

Solution:

For the jobs that are not displayed is SSMS, UPDATE  the category column to 3.

[sourcecode language=”sql”]

UPDATE dbo.sysjobs
SET category_id = 3
WHERE job_id = ‘285A30B2-11C9-4B54-8B4D-48E48A059426’
AND category_id = 4
GO

[/sourcecode]

Hope this helps,

_SqlTimes