Holy SQL Failure, Batman or... SQL Identity Value Change

I had literally one of the most stressful group of hours as an admin... I made an update to a production table that was required and two hours later it magically decides to start inserting duplicate numbers in the identity field (which happens to be the primary key as well, as that would naturally prevent duplicates since SQL generates the number and knows not to duplicate numbers, right?!)... Well, that was not the case today.

Today I got the dreaded "Violation of PRIMARY KEY constraint (contraint_name). Cannot insert duplicate key in object (table_name). The statement has been terminated" when trying to insert a new record into the table.

Of course, there was nothing that I changed in the table that should have created this problem... but here we were. So as I scoured the internet for solutions I noticed that there were absolutely no actual solutions readily available, so I stepped into Sleuth Mode and started pounding my head against my palms to figure out a solution to a problem that I hadn't identified yet.

Well, I knew that all of the other data that was being entered was not duplicate (because I had randomly entered it) and the only thing that could be causing a conflict would be the identity.

My first deduction was to identify what the value of the identity field should have been. A quick query against the table with a descending order of the id field solved that problem quickly: 119139.

Now I had to figure out what the current identity SQL was trying to use was. At first I hadn't any idea of how to do this off the top of my head (software dev first, database admin second), so I started digging. I found a very helpful command courtesy of MS: ident_current(). So there I was, sitting at the query window and I typed in my command... select ident_current('tablename') as currentid. Bingo... It said the current id being used was 119086. Wait... what? How could that even be? Why would it think that it was ok to roll back a number and begin trying to insert duplicates? I still don't know to this very moment why it did that. Remember, I hadn't changed anything about the primary key nor the identity. I had merely added a few more fields to the table.

So this is where the panic set it: This is a live solution. A database that the production software was using to store call logs, client data, anything and everything that made us money... and it was refusing to coincide to what it was supposed to be doing.

So then the Googling started again. I started simple: Why would SQL start duplicating identity fields. Guess how many valuable results that came up with. If you guessed ZERO then you are absolutely right.

After my first failure at finding a potential solution, I slapped myself mentally and asked myself why I hadn't just thought of how to change the seed. Well, I knew one way... and had tried it an hour ago. Guess what, it locks up the DB for a substantial amount of time and times out. Can't have that. So then I start looking for commands and I come across this nice fellow: dbcc checkident('tablename', reseed, newseedid). Simply replace the term tablename with your table's name (leave the single quotes in the command) and change newseedid to the new number you'd like the id seed to be and bingo, you have a new seed. Problem solved and crisis averted.

I hope this helps out some of you poor souls who may run into this issue in the future. It's far from an end-all-be-all solution, and it should be done only if you understand what it is you are doing (please look up the commands on MSKB before you implement them), and of course you are doing any of these things at your own risk... but it fixed my problem, and that is why I decided to share the experience.

Peace,
B

Wow, this long?!

Wow, I can't believe I have neglected this poor blog for this long. Life has been very hectic (as those of you who have read my other blog know) for a while now... but now I'm good to go. While I may not update this blog as often as I like, I've had a few people stumble across the blog this week so it's important that I at least keep it alive (they were asking questions about some of my old posts, which is good because I LOVE answering questions!).

Well, until the next tip... :)