June 19, 2017 at 10:34 am
Hello,
I have a database with many tables containing auto-incrementing Pk's. Somehow, things got messed up and an insert against a table with 1,000 records tries to insert with a Pk value of "1" even though the table should be trying to insert "1001" for the Pk.
I understand there are ways to reseed every table in a database. How is this done?
Thank you for any assistance.
June 19, 2017 at 10:57 am
have a look at DBCC CHECKIDENT to fix the next identity value:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql
June 19, 2017 at 10:57 am
The command you would be looking for is DBCC CHECKIDENT. Here is the link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql. Best bet would be to run something like:select 'dbcc checkident(''' + name + ''', reseed)'
from sys.tables
If you include the sys.columns table, you should be able to get only tables that have an identity column in them.
June 19, 2017 at 11:27 am
tarr94 - Monday, June 19, 2017 10:34 AMHello,I have a database with many tables containing auto-incrementing Pk's. Somehow, things got messed up and an insert against a table with 1,000 records tries to insert with a Pk value of "1" even though the table should be trying to insert "1001" for the Pk.
I understand there are ways to reseed every table in a database. How is this done?
Thank you for any assistance.
Don't fix it, this behaviour is correct and is encountered after a service restart. Here's the original Microsoft connect item, if you wish to use the original auto incrememt behaviour you need to enable trace flag 272
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 19, 2017 at 12:31 pm
Perry Whittle - Monday, June 19, 2017 11:27 AMDon't fix it, this behaviour is correct and is encountered after a service restart. Here's the original Microsoft connect item, if you wish to use the original auto incrememt behaviour you need to enable trace flag 272
Actually that Connect item looks like the exact opposite situation, this table has a thousand rows already, not under a thousand, and this identity was reset to 1, not 1000.
June 19, 2017 at 3:27 pm
Thanks for the responses everyone. I'll give these a try. 🙂
June 20, 2017 at 4:55 am
Chris Harshman - Monday, June 19, 2017 12:31 PMPerry Whittle - Monday, June 19, 2017 11:27 AMDon't fix it, this behaviour is correct and is encountered after a service restart. Here's the original Microsoft connect item, if you wish to use the original auto incrememt behaviour you need to enable trace flag 272Actually that Connect item looks like the exact opposite situation, this table has a thousand rows already, not under a thousand, and this identity was reset to 1, not 1000.
possible someone has already re seeded to attempt to fix the issue
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply