Sean Lange (3/27/2014)
sql-lover (3/27/2014)
Is someone else experiencing this problem, SQL2012, of course:
Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (1842649).
The statement has been terminated.
This never, ever, happened to me before on my MS-SQL 2008 or MS-SQL 2005 systems. Once I moved to MS-SQL2012, I started having these errors, which are a pain in the butt, especially for my Developers.
For some weird reason, MS-SQL losses track of the most recent ID value and try to insert and old one. And no ... the store procedure is the only one doing the inserts, and like I said, never had that issue before.
My new variables are:
-MS-SQL2012 (had SQL2005/2008 before)
-SAN
-VMware (had physical boxes before)
I fixed it via DBCC command, but I should not being doing this manually, MS-SQL2012 should not insert any existing value on my tables.
Is any one aware of any bug and a fix for this? I am aware of the existing bug when SQL is restarted, but this is not the case here.
I have not experienced anything like this or heard of it.
You didn't provide a lot of detail here. Are these inserts using identity columns or some sort of other way to determine what value to insert? I sort of get the impression this is identity.
Yes, you are right. There is a column that uses Identity. So sometimes, the nightly batches fail because the Identity value that the sproc is about to insert is lower than what it is.
I have no technical data to prove is the SAN, or VMware, but I can't say is MS-SQL2012 either, as I never had that issue before. And this happens sporadically. Sometimes one or two times in a week. Sometimes one per month.
I think the ID values are generating on SQL2012 in a different way, but I am not sure. What I am know for sure, is that this is not a code issue and this is not normal.