msdb.dbo.sysjobs Clustered Unique Index allowing duplicates?

Question

Post reply

msdb.dbo.sysjobs Clustered Unique Index allowing duplicates?

Kristen-173977

SSCrazy Eights

Points: 8687
More actions
February 10, 2020 at 5:31 pm

#3724442
Our SQL Agent jobs have a "step 1" which checks if all jobs are on hold (e.g. server being patched / rebooted) and if so prevents the job starting. As part of that step it INSERTS to [MyJobs] table (in our ADMIN database) any new not-yet-seen-jobs - so that, prior to maintenance, we can check if there are any jobs not doing a STEP 1 "Check if all jobs paused" and rectify that before the scheduled maintenance window.
So today we have been getting DUPLICATE KEY on INSERT to this table. I'm struggling to see how this is possible ...
```
INSERT INTO dbo.MyJobs(MyJobsGUID, ...)
SELECTSyJ.job_id
, ...
FROMmsdb.dbo.sysjobs AS SyJ
LEFT OUTER JOIN dbo.MyJobs AS MyJ
 ON MyJ.MyJobsGUID = SyJ.job_id
WHEREMyJ.MyJobsGUID IS NULL-- Does not exist

Msg 2601, Sev 14, State 1, Line 336 : 
Cannot insert duplicate key row in object 'dbo.MyJobs' with unique index 'IX_MyJobsGUID'. 
The duplicate key value is (63699854-3715-474a-8fac-62e660092f09). [SQLSTATE 23000]

msdb.dbo.sp_help sysjobs

index_name index_description index_keys
---------- ----------------- ----------
clust      clustered, unique job_id

MyAdminDBb.dbo.sp_help MyJobs

index_name    index_description index_keys
------------- ----------------- ----------
IX_MyJobsGUID clustered, unique MyJobsGUID
```
On checking the [clust] index on msdb.dbo.sysjobs more carefully it seems that it has the "Ignore duplicate values" property set.
Is that what my problem is? and if so how does SQL Agent figure out which job is which if it doesn't have a unique index? (I need something unique if I am to refer to the rows in msdb.dbo.sysjobs), or is there something else going on here that I have missed?
February 12, 2020 at 1:05 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

It looks like your dbo.MyJobs table is the one not allowing duplicate values, which matches your CI as being unique.

You can confirm that you have no duplicate job_id values in msdb.dbo.sysjobs by trying the following:

SELECT job_id, COUNT(*)
  FROM msdb.dbo.sysjobs 
  GROUP BY job_id 
  HAVING COUNT(job_id) > 1;

This reply was modified 4 years, 10 months ago by Ed Wagner.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 2

Ed Wagner wrote:

It looks like your dbo.MyJobs table is the one not allowing duplicate values, which matches your CI as being unique.

Yes, that's the intention, but given the Unique Clustered index on the Source Table I'm scratching my head as how the duplicates are possible.

But good idea on the COUNT(*), I'll put that in to prevent any duplicates and at the same time output any that it finds. The SQL Agent job is (now) logging to a Text File, so I'll be able to retrieve that output to see that there are indeed duplicates in msdb.dbo.sysjobs

Assuming that does turn out to be the case I'm mystified as to how it works if it has a Unique Index and FKeys on that ... but then allows duplicates.