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.


SELECT *
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'
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..

 

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: