Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identity column maxed out


Identity column maxed out

Author
Message
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14992 Visits: 39023
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-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21157 Visits: 18259
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

Carlos Pereyra
Carlos Pereyra
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
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
Carlos Pereyra
Carlos Pereyra
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21157 Visits: 18259
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

Joe-420121
Joe-420121
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 558
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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24291 Visits: 37996
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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2992 Visits: 9071
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-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21157 Visits: 18259
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

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45464 Visits: 39948
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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