SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running out of Identity values


Running out of Identity values

Author
Message
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 1930
Ok

Long story short, I am running out of Identity values on two tables (different databases) but very big tables, a billion records.

I can't reset the values via DBCC, so I designed this plan ...

1.Export data via BCP
2.Import via BCP to the new table that now has BIG INT on that column (while system is online)

--System offline for an hour or maybe less --

4.Insert or copy new rows (added after bcp) from old table into new table
5.Drop FK on old table and create on new table
6.Rename tables (keep old one just in case)
5.Compare row count, just in case

--Bring system online ---

Am I missing something? I am planning to test the logic with those tables on a different environment, of course.

Any comment or advice is appreciated ...

Thanks,
Ben Teraberry
Ben Teraberry
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1199
Are the records in the tables in question ever updated or just inserted?

└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 1930
bteraberry (10/2/2012)
Are the records in the tables in question ever updated or just inserted?


Good point...

Ever increased ... it is my understanding, no updates or inserts in the middle ...
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7764 Visits: 7140
Maybe I'm missing something.

Why not just ALTER the column to be a bigint instead of an int?

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Ben Teraberry
Ben Teraberry
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1199
ScottPletcher (10/2/2012)
Maybe I'm missing something.

Why not just ALTER the column to be a bigint instead of an int?


He said these are very big tables. Altering the column means that every single records needs more storage space to accommodate the larger data type. Tons of downtime is likely to result because in most environments the extra space won't be available without shuffling everything around.

└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7764 Visits: 7140
bteraberry (10/2/2012)
ScottPletcher (10/2/2012)
Maybe I'm missing something.

Why not just ALTER the column to be a bigint instead of an int?


He said these are very big tables. Altering the column means that every single records needs more storage space to accommodate the larger data type. Tons of downtime is likely to result because in most environments the extra space won't be available without shuffling everything around.



He packed a huge table 100% full? Yeah, that's a mistake for a number of reasons, particularly in SQL Server. Even turning on certain options / features lengthens rows in SQL Server.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7764 Visits: 7140
Some other thoughts/possibilities:

Rather than export and then import, it should be faster to just directly INSERT INTO the new table, naturally doing a table lock on the new table.

Pre-allocate enough log space to handle the activity (you'd want to do this whatever method you use).

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 1930
ScottPletcher (10/2/2012)
Maybe I'm missing something.

Why not just ALTER the column to be a bigint instead of an int?


NO way! Because downtime! Why do you think that I am taking all those extra measures or steps? The table has over 1 billion records Cool

I did not design this table / database, I am the new DBA. It is my responsibility to take care of the system though, that's what I am doing.

I pretty much know what I need to do. I was just asking to validate the overall steps.

The database is a MC one. I can't touch it! Plus is a very old hardware with serious IO bottleneck. Any activity on that table will make the table useless and trigger a downtime.
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7764 Visits: 7140
sql-lover (10/2/2012)
ScottPletcher (10/2/2012)
Maybe I'm missing something.

Why not just ALTER the column to be a bigint instead of an int?


NO way! Because downtime! Why do you think that I am taking all those extra measures or steps? The table has over 1 billion records? Cool

I did not design this table / database, I am the new DBA. It is my responsibility to take care of the system though, that's what I am doing.

I pretty much know what I need to do. I was just asking to validate the overall steps.

The database is a MC one. I can't touch it! Plus is a very old hardware with serious IO bottleneck. Any activity on that table will make the table useless and trigger a downtime.



Yes, I read the 1B rows.

Do you really think that with adequate freespace in each block to begin with, it will be faster to insert the rows twice than to change the length of one column?? I don't see how that's even remotely possible.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search