We have a replication job that copies data from our production environment into a testing environment. As far as I can tell, the replication completed successfully, and I'm not sure the issue is being caused by the replication or not.
We have a table containing an auto-incrementing PK column that runs from "1" to "3000". However, when we try to insert a new record, we're getting a constraint violation error:
"Cannot insert duplicate key in object 'MyTable''. The duplicate key value is (1000)."
When I try running the insert again, the error changes a bit:
"Cannot insert duplicate key in object 'MyTable''. The duplicate key value is (1001)."
Clearly, there is a disconnect in which SQL Server thinks the next available Pk value much lower than it should be. How can I fix this?
I greatly appreciate any assistance!