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 1:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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?

if there are any dependent foreign keys on the table, or if the table has any constraints or defaults then no, that is NOT the way to do it.
doing a full backup and additionally backing up the table as Jason suggested, and then using the SSMS GUI is the way to do it.


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 #837500
Posted Monday, December 21, 2009 1:03 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
Lynn Pettis (12/21/2009)
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.


As Lynn said, I would use the INSERT INTO. It will be faster in most cases.

Also, it is very important that when you create the table that it is created to mirror the original table with the exception of the BIGINT.

I, would also not Drop the old table for several weeks. I like to keep it around as a failsafe. It is much faster to recover from that table than from backup.




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #837501
Posted Monday, December 21, 2009 1:05 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 12, 2010 3:04 PM
Points: 75, Visits: 35
If you're going to use INSERT INTO, you'll need to name all the columns. One easy way to get the list of columns (if the table has many) is "select name + ',' from syscolumns where object_name(id) = 'mytablename' order by colorder (Replace mytablename with the name of your table) This will give you the columns and the commas in-between.

Carlos.



Regards,

Carlos
Post #837505
Posted Monday, December 21, 2009 1:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 12, 2010 3:04 PM
Points: 75, Visits: 35
Any reason why you're not using alter table alter column?
as in alter table mytable alter column my column bigint?

Carlos.



Regards,

Carlos
Post #837512
Posted Monday, December 21, 2009 1:15 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
Carlos Pereyra (12/21/2009)
Any reason why you're not using alter table alter column?
as in alter table mytable alter column my column bigint?

Carlos.


The best answer is - as a safeguard - but the OP will have to respond.




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #837520
Posted Monday, December 21, 2009 1:22 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, November 6, 2014 2:19 PM
Points: 111, Visits: 482
1. The table has only 4 coulmmns, no FK, constrains etc.
2. The table is used 24 hours, and about 2000 users use it all the time.
3. When I use GUI to modify data type to BIGINT, I get timeout error after 3 min.
4. Copy data to tmp table and when all done, and drop the old table and just rename it quicky.
( also I am creating the table in a new file group).
5. The size of table is about 60GB, so I feel SELECT INTO might take more time than SSIS. am I wrong?

Thanks for all your helps...
Post #837529
Posted Monday, December 21, 2009 1:25 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 9:11 PM
Points: 20,861, Visits: 32,887
Joe-420121 (12/21/2009)
1. The table has only 4 coulmmns, no FK, constrains etc.
2. The table is used 24 hours, and about 2000 users use it all the time.
3. When I use GUI to modify data type to BIGINT, I get timeout error after 3 min.
4. Copy data to tmp table and when all done, and drop the old table and just rename it quicky.
( also I am creating the table in a new file group).
5. The size of table is about 60GB, so I feel SELECT INTO might take more time than SSIS. am I wrong?

Thanks for all your helps...


SSIS probably would not be any faster.



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 #837532
Posted Monday, December 21, 2009 1:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:43 PM
Points: 2,838, Visits: 8,570
Joe-420121 (12/21/2009)

3. When I use GUI to modify data type to BIGINT, I get timeout error after 3 min.


Instead of using the GUI locally on your desktop, use the GUI to generate the script, then log on to the server remotely and run the the script there in a query window.



Post #837545
Posted Monday, December 21, 2009 3:45 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
IMHO, SSIS will be slower for this kind of process.

As homebrew said, you could use the GUI to script the changes. And then run them from the server itself.

Also, you could use the alter column to change the column to a bigint.






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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #837618
Posted Monday, December 21, 2009 7:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
homebrew01 (12/21/2009)
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.


It's an absolute myth that was once true prior to SQL Server 6.5 sp1.
http://support.microsoft.com/kb/153441/EN-US/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #837697
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse