Duplicate Agent jobs – A good reason not to meddle with Msdb

 

Some time ago I came across a strange issue where I found a number of duplicated SQL Agent jobs, the odd thing is SQL will not allow you to have more than one agent job with the same name – they need to be unique.

To demonstrate here is an example of what i saw:

 

 

This got me scratching my head a little at first, so I started out with some basic checks of the msdb tables.

 

What does it show in sys,jobs?

 

 

 

Well kinda what we expected to see – uniquely named Agent jobs…. hmmm… so we know that there is no old fashioned witchcraft going on here – but something is definitely amiss…

 

Lets see what makes these particular jobs stand out from the rest (Apart from their ridiculous names 🙂 )

 

Let see what Category they have assigned…

 

 

Interesting… they share the same Category and none of the other jobs have this category assigned – lets see what this category is.

SELECT *
FROM msdb..syscategories
WHERE category_id = 1100

 

 

Aha!! Two categories with the same ID but here is something interesting , because there are two categories that share the same Category_id , ‘TestCategory2′ can never be assigned to a job as it will always default back to ”TestCategory1’ because it comes first Alphabetically.

 

So how did it get this way?

 

Well its not going to be sp_Update_job  or sp_update_category or any other system stored procedure for that matter as these procedures will not allow duplicate ID’s to be used , My guess is that there was a Meddler… Someone issued a manual INSERT statement against the table and to prove this is allowed in SQL I will make another duplicate using an insert too but name it ‘TestCategory3’:

 

SET IDENTITY_INSERT msdb..syscategories ON
INSERT INTO msdb..syscategories (category_id,category_class,category_type,name)
SELECT category_id,category_class,category_type,'TestCategory3'
FROM msdb..syscategories
WHERE Name = 'TestCategory2'
SET IDENTITY_INSERT msdb..syscategories OFF

 

Querying the Categories table again we can see that we now have three duplicates:

 

And now if we look at the Agent job list we now have 3 of each job with this Category_id 1100!!

 

Lets fix this!

 

--Remove the duplicated Catgeory (this will remove all categories with the ID of 1100 as they are duplicated
EXEC msdb..sp_delete_category @Class = 'job', @name = 'TestCategory1'
GO

--Re add the categories correctly
EXEC msdb..sp_add_category @Class = 'job', @name = 'TestCategory1'
GO
EXEC msdb..sp_add_category @Class = 'job', @name = 'TestCategory2'
GO
EXEC msdb..sp_add_category @Class = 'job', @name = 'TestCategory3'
GO

 

Now we have the following..

 

 

I set TestCategory1 against _AgentJob1 and TestCategory2 against _AgentJob2 , so what does the agent job list look like now:

 

 

Much better!

 

Don’t Meddle with system databases is just not worth it , there are some great little system procedures that do common tasks for you or use the GUI 🙂

If you have any other instances of manual updates to system databases that have caused issues I would love to hear about them in the comments section.

 

Thanks for reading 🙂