<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 2005
»
Administering
»
Identity column maxed out
20 posts, Page 1 of 2
1
2
»»
Identity column maxed out
Rate Topic
Display Mode
Topic Options
Author
Message
Joe-420121
Joe-420121
Posted Monday, December 21, 2009 10:31 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:17 AM
Points: 107,
Visits: 331
My production environment has a table with indenity column with INT datatype; The column size is maxed out, The table size is 60GB. What is the best way to change the datatype to BIGINT to support more inserts. The table is heavly used by the users.
Post #837371
Lowell
Lowell
Posted Monday, December 21, 2009 11:02 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 11,648,
Visits: 27,762
you'll want to use the GUI in SSMS;and change it to bigINT; the GUI will create a new table witht he proper structure, migrate the data, migrate all the foreign keys, constraints, etc, and drop teh old, then rename the new table to the proper name;
it is much easier and faster to use the GUI than to do that by hand, especially on a big busy table.
i'm fairly sure that it also changes the
child tables
that point to it with foreign keys, since they need to be changed to BIGINT also. one simple operation like changing the data type might affect dozens or hundreds of tables.
everyone will be locked out of the table while you are changing it, but they cannot insert right now, i would assume, so you are probably going to have to do this right away.
Lowell
--
There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #837397
SQLRNNR
SQLRNNR
Posted Monday, December 21, 2009 11:27 AM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754,
Visits: 12,337
In addition, I recommend you make a backup of the table as a precaution.
I would go to the extent of scripting out the table, including indexes and Key relationships.
Then a simple select * into tablebackup12212009 from table would suffice for the backup.
Doing this provides a bit of protection should something happen during the conversion. This has saved me from catastrophy due to a transaction deadlock on the table rebuild that killed the temp table and the data in it. If not for the backup, I would have been hosed.
Other than that, do as Lowell recommends.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #837416
Joe-420121
Joe-420121
Posted Monday, December 21, 2009 11:45 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:17 AM
Points: 107,
Visits: 331
Thank you guys for your helps.
Post #837428
SQLRNNR
SQLRNNR
Posted Monday, December 21, 2009 11:56 AM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754,
Visits: 12,337
NP
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #837434
homebrew01
homebrew01
Posted Monday, December 21, 2009 12:14 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 6:41 PM
Points: 2,554,
Visits: 7,213
CirquedeSQLeil (12/21/2009)
Then a simple select * into tablebackup12212009 from table would suffice for the backup.
I seem to recall that you can get blocking when creating a table this way if the source table is big because system locks are held until the insert is complete. You could create the empty table tablebackup12212009 first , then insert the data.
Post #837446
SQLRNNR
SQLRNNR
Posted Monday, December 21, 2009 12:25 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754,
Visits: 12,337
Good Point Homebrew - It is a better practice in most cases to create the table first.
If all is broken
currently because nothing can be inserted into the table, then it may be fine in this scenario to use the select into.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #837455
Mark_Pratt
Mark_Pratt
Posted Monday, December 21, 2009 12:32 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:50 PM
Points: 573,
Visits: 494
This is quick and dirty way to create an empty table ... Not one to file under best practice though
select * into tablebackup12212009 from table where 2=1
Post #837466
Joe-420121
Joe-420121
Posted Monday, December 21, 2009 12:33 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:17 AM
Points: 107,
Visits: 331
The following procedure is OK?
1. Create Table [backup12212009] with BIGINT Indentity
2. using SSIS export data from old table to [backup12212009]
3. Delete old table
4. Rename backup12212009
Can do as above?
Post #837467
Lynn Pettis
Lynn Pettis
Posted Monday, December 21, 2009 12:37 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 21,630,
Visits: 27,485
Joe-420121 (12/21/2009)
The following procedure is OK?
1. Create Table [backup12212009] with BIGINT Indentity
2. using SSIS export data from old table to [backup12212009]
3. Delete old table
4. Rename backup12212009
Can do as above?
Why not just use INSERT INTO? Just be sure to enable INDENTITY insert either way, and if using T-SQL, to turn it off when done.
Lynn Pettis
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here
or
when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here
and
here
Managing Transaction Logs
SQL Musings from the Desert
Fountain Valley SQL
(My Mirror Blog)
Post #837474
« Prev Topic
|
Next Topic »
20 posts, Page 1 of 2
1
2
»»
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.
