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

2017-09-07 21_31_55


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:


2017-09-07 20_31_12


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?


2017-09-07 20_33_09



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…


2017-09-07 20_36_29


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

FROM msdb..syscategories
WHERE category_id = 1100


2017-09-07 20_39_25


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:

2017-09-07 20_52_53


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

2017-09-07 20_54_24


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'

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


Now we have the following..


2017-09-07 21_00_25


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


2017-09-07 21_01_32


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 🙂


One thought on “Duplicate Agent jobs – A good reason not to meddle with Msdb

Add yours

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: