SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identity column maxed out


Identity column maxed out

Author
Message
Lowell
Lowell
SSC Guru
SSC Guru (181K reputation)SSC Guru (181K reputation)SSC Guru (181K reputation)SSC Guru (181K reputation)SSC Guru (181K reputation)SSC Guru (181K reputation)SSC Guru (181K reputation)SSC Guru (181K reputation)

Group: General Forum Members
Points: 181925 Visits: 41569
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143499 Visits: 18649
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
Learn Extended Events

Carlos Pereyra
Carlos Pereyra
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 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
Carlos Pereyra
Carlos Pereyra
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143499 Visits: 18649
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
Learn Extended Events

Joe-420121
Joe-420121
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1778 Visits: 560
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...
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222662 Visits: 40381
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.

Cool
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)
homebrew01
homebrew01
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29226 Visits: 9222
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.



SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143499 Visits: 18649
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
Learn Extended Events

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)

Group: General Forum Members
Points: 504679 Visits: 44234
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search