December 14, 2007 at 9:28 am
We have a process that runs every 10 minutes to store user activity on our database servers. The process runs a query against the master..sysprocesses table and enters the data in our user_activity table. The user_activity has a primary key on SPID, ECID and CreateDate. About 3 times a day the job fails and we receive a primary key error. I thought that the combination of SPID and ECID would always be unique. Am I wrong?
Thanks
December 14, 2007 at 10:18 am
I checked in our system ecid is always returning 0. What is CreateDate, is it date and time when your script is runnning every 10 min ? If so, it does not guarantee uniqueness. At any given time more than 1 SPID can be running, but they differ by KPID.
What I would recommend is to use a combo of SPID + KPID + last_batch
December 14, 2007 at 1:18 pm
Thank you for the response. The CreateDate is just the getdate() function so we have a time to equate to the process. We thought the combination of SPID and ECID would be unique. It seems to be in 2000 where this is happening in 2005.
If the SPID appears more than once in the sysprocesses table, shouldn't that mean that there is a subthread running along with the parent thread? If that is the case, the parent thread should have an ECID of 0 and any subthread would have an ECID of 1 or some other number? This would make that combination unique.
I also only see an ECID of 0 most of the time when I look at the sysprocesses table. However, this is because the SPID only appears once also meaning there is only one parent thread processing. What happens when a subthread for a specific thread runs so the SPID appears more than once?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply