Issue with SCCM Package Distribution - Violation of PRIMARY KEY constraint 'PkgStatus_G_PK'. Cannot insert duplicate key in object 'dbo.PkgStatus_G'. The duplicate key value is (xxxxxxxxxxxxxxxxxxxx).

Question

Hi,

One of our user reported that the distribution of some packages were hanging, stuck "In Progress" for hours.

Upon looking at the SMS_DISTRIBUTION_MANAGER log on our site server, we have found multiple SQL errors like this one, dating back a few days:

Severity Type Site code Date / Time System Component Message ID Description
Warning Milestone [redacted] 7/29/2021 5:19:08 PM [redacted] SMS_DISTRIBUTION_MANAGER 619 Microsoft SQL Server reported SQL message 2627, severity 14: [23000][2627][Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'PkgStatus_G_PK'. Cannot insert duplicate key in object 'dbo.PkgStatus_G'. The duplicate key value is ([redacted]). : tr_PkgStatus_ins Please refer to your Configuration Manager documentation, SQL Server documentation, or the Microsoft Knowledge Base for further troubleshooting information.

We updated our site to 2103 on July 2nd, and these errors appeared for the first time on July 7th. Some of these early packages which appeared to have been at the source of these errors eventually get resolved and have since reached 100% distribution for all targeted DPs. However, most of the attempts at distribution attempted today - including a few I created while troubleshooting the issue - are stuck "In Progress" many hours after distribution was launched. This is not typical in our environment where distribution usually take a few minutes.

If I query the PKGStatus_G table in the database for the value being reported as a duplicate, I do find a record with that value (PKID field) but it is (or at least it looks to me) a valid record. I verified that the package referenced in that record (ID field) does exist in SCCM, and that the PKGServer is a valid DP on which this package is currently distributed and they both check out.

Attempts at redistribution are failing. Creating new packages with the same source encounters the issue as well.

Any hint as to what might be happening here?

Thank you!

Sebastien

Answer 1

Hi,

1. Absolutely, here is what I can see in the three logs:

DistMgr.log: Contains the exact same SQL errors I was seeing in the console when looking at SMS_DISTRIBUTION_MANAGER. Here is one such example:

GetDPUsableDrives - ["Display=\<servername>\"]MSWNET:["SMS_SITE=<sitecode>]"]\<servername>\ $$<SMS_DISTRIBUTION_MANAGER><07-28-2021 15:23:30.823+240><thread=73168 (0x11DD0)>
Finished GetDPUsableDrives - ["Display=\<servername>\"]MSWNET:["SMS_SITE=<sitecode>"]\<servername>\ $$<SMS_DISTRIBUTION_MANAGER><07-28-2021 15:23:30.829+240><thread=73168 (0x11DD0)>
SetContentLibLocationInReg - ["Display=\<servername>\"]MSWNET:["SMS_SITE=<sitecode>"]\<servername>\ $$<SMS_DISTRIBUTION_MANAGER><07-28-2021 15:23:30.829+240><thread=73168 (0x11DD0)>
Finished SetContentLibLocationInReg - ["Display=\<servername>\"]MSWNET:["SMS_SITE=<sitecode>"]\<servername>\ $$<SMS_DISTRIBUTION_MANAGER><07-28-2021 15:23:30.902+240><thread=73168 (0x11DD0)>
****[23000][2627][Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'PkgStatus_G_PK'. Cannot insert duplicate key in object 'dbo.PkgStatus_G'. The duplicate key value is (<bigint from db>). : tr_PkgStatus_ins $$<SMS_DISTRIBUTION_MANAGER><07-28-2021 15:23:30.915+240><thread=73168 (0x11DD0)>****
STATMSG: ID=2326 SEV=E LEV=M SOURCE="SMS Server" COMP="SMS_DISTRIBUTION_MANAGER" SYS=<servername> SITE=<sitecode> PID=2828 TID=73168 GMTDATE=Wed Jul 28 19:23:30.921 2021 ISTR0="<packageid>" ISTR1="15" ISTR2="5" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=2 LE=0X0 AID0=400 AVAL0="<packageid>" AID1=404 AVAL1="["Display=\<servername>\"]MSWNET:["SMS_SITE=<sitecode>"]\<servername>\" $$<SMS_DISTRIBUTION_MANAGER><07-28-2021 15:23:30.921+240><thread=73168 (0x11DD0)>
~Cannot save the initial package status to the data source. $$<SMS_DISTRIBUTION_MANAGER><07-28-2021 15:23:30.925+240><thread=73168 (0x11DD0)>
Error occurred. Performing error cleanup prior to returning. $$<SMS_DISTRIBUTION_MANAGER><07-28-2021 15:23:30.926+240><thread=73168 (0x11DD0)>

PkgXferMgr.log: This one is very clean, I went through it all over the last 3 days and did not find traces of errors.

SMSDPProv.log: Went around a few of our distribution points and also cannot find traces of anything suspect in this log.

We also have a few secondary sites. When examining sender.log and despool.log, the only other trace of an error I have been able to find is this one from despool.log.

Old package storedUNC path is . SMS_DESPOOLER 7/29/2021 1:57:09 PM 8704 (0x2200)
This package[<packageid>]'s information hasn't arrived yet for this version [3]. Retry later ... SMS_DESPOOLER 7/29/2021 1:57:09 PM 8704 (0x2200)
Created retry instruction for job 00006F96 SMS_DESPOOLER 7/29/2021 1:57:09 PM 8704 (0x2200)

2. I did look over the notes associated with the hotfix and nothing jumped to me as potentially being related to this issue. I'll see if I can give it a shot but I'll need a little bit of time.

I think I may have been able to come up with a workaround for the time being. I looked over the table PKGStatus_G and the primary key (PKID) is a bigint and an identity field. Unsurprisingly, the bigint that is returned in the log as being in violation of the primary key constraint increments by 1 on every attempt at distribution. When I queried the table and looked at the range of IDs stored in PKID, I could see that the range of IDs SCCM/SQL was currently attempting to use ended about 50 of so values later. So I created myself a dummy package and well, scripted a few "Update Distribution Points" tasks. When the "currently used ID" finally moved beyond the range of what could be found in PKID, I stopped getting the errors and distribution resumed. Am I dealing with a SQL issue more than an SCCM one?

Thank you!

Sebastien

Answer 2

Hi,

Thanks for posting in Microsoft Q&A forum.

1, Please help check the DistMgr.log, PkgXferMgr.log and SMSDPProv.log to see if there is any further information.

2, If possbile, please install the latest SCCM 2103 hotfix to have a try.

Best regards,
Simon

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.