<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
Running out of Identity values
45 posts, Page 1 of 5
1
2
3
4
5
»
»»
Running out of Identity values
Rate Topic
Display Mode
Topic Options
Author
Message
sql-lover
sql-lover
Posted Tuesday, October 02, 2012 10:27 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 226,
Visits: 723
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
bteraberry
bteraberry
Posted Tuesday, October 02, 2012 12:06 PM
Ten Centuries
Group: General Forum Members
Last Login: Saturday, February 23, 2013 11:07 AM
Points: 1,103,
Visits: 1,170
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
sql-lover
sql-lover
Posted Tuesday, October 02, 2012 12:43 PM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 226,
Visits: 723
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
CELKO
CELKO
Posted Tuesday, October 02, 2012 1:54 PM
SSCommitted
Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945,
Visits: 2,782
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
ScottPletcher
ScottPletcher
Posted Tuesday, October 02, 2012 4:14 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 1,318,
Visits: 1,764
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)
One man with courage makes a majority. Andrew Jackson
Post #1367323
bteraberry
bteraberry
Posted Tuesday, October 02, 2012 4:29 PM
Ten Centuries
Group: General Forum Members
Last Login: Saturday, February 23, 2013 11:07 AM
Points: 1,103,
Visits: 1,170
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
ScottPletcher
ScottPletcher
Posted Tuesday, October 02, 2012 4:47 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 1,318,
Visits: 1,764
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)
One man with courage makes a majority. Andrew Jackson
Post #1367329
ScottPletcher
ScottPletcher
Posted Tuesday, October 02, 2012 5:02 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 1,318,
Visits: 1,764
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)
One man with courage makes a majority. Andrew Jackson
Post #1367332
sql-lover
sql-lover
Posted Tuesday, October 02, 2012 5:13 PM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 226,
Visits: 723
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
ScottPletcher
ScottPletcher
Posted Tuesday, October 02, 2012 5:18 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 1,318,
Visits: 1,764
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)
One man with courage makes a majority. Andrew Jackson
Post #1367336
« Prev Topic
|
Next Topic »
45 posts, Page 1 of 5
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
