Identity column maxed out

Question

Post reply

Identity column maxed out

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 1

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 SSC Guru Points: 442467 More actions · Answer 2

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.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

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 SSC Guru Points: 281334 More actions · Answer 4

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[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)