Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Running out of Identity values Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 10:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 416, Visits: 1,334
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,
Post #1367161
Posted Tuesday, October 2, 2012 12:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 4:49 PM
Points: 1,093, Visits: 1,175
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
Post #1367216
Posted Tuesday, October 2, 2012 12:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 416, Visits: 1,334
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 ...
Post #1367236
Posted Tuesday, October 2, 2012 1:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:29 PM
Points: 1,945, Visits: 3,121
What is the real key for this nameless table? At least you know you are modeling a file with records instead of a table with rows in a RDBMS.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1367269
Posted Tuesday, October 2, 2012 4:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1367323
Posted Tuesday, October 2, 2012 4:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 4:49 PM
Points: 1,093, Visits: 1,175
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
Post #1367327
Posted Tuesday, October 2, 2012 4:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1367329
Posted Tuesday, October 2, 2012 5:02 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1367332
Posted Tuesday, October 2, 2012 5:13 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 416, Visits: 1,334
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

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.
Post #1367334
Posted Tuesday, October 2, 2012 5:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 2,268, Visits: 3,425
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?

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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1367336
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse