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 12»»

Identity column maxed out Expand / Collapse
Author
Message
Posted Monday, December 21, 2009 10:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:39 PM
Points: 111, Visits: 480
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
Posted Monday, December 21, 2009 11:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 12,905, Visits: 32,161
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
Posted Monday, December 21, 2009 11:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:17 PM
Points: 17,817, Visits: 15,744
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #837416
Posted Monday, December 21, 2009 11:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:39 PM
Points: 111, Visits: 480
Thank you guys for your helps.
Post #837428
Posted Monday, December 21, 2009 11:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:17 PM
Points: 17,817, Visits: 15,744
NP



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #837434
Posted Monday, December 21, 2009 12:14 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:50 PM
Points: 2,832, Visits: 8,509
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
Posted Monday, December 21, 2009 12:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:17 PM
Points: 17,817, Visits: 15,744
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #837455
Posted Monday, December 21, 2009 12:32 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 5:33 AM
Points: 746, Visits: 557
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
Posted Monday, December 21, 2009 12:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:39 PM
Points: 111, Visits: 480
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
Posted Monday, December 21, 2009 12:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 20,734, Visits: 32,505
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 »

Add to briefcase 12»»

Permissions Expand / Collapse