Reseeding indexes for tables with data

  • 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.

  • 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

  • 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.

  • tarr94 - Monday, June 19, 2017 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.

    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

    https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle - Monday, June 19, 2017 11:27 AM

    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

    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.

  • Thanks for the responses everyone.  I'll give these a try.  🙂

  • Chris Harshman - Monday, June 19, 2017 12:31 PM

    Perry Whittle - Monday, June 19, 2017 11:27 AM

    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

    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.

    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