December 21, 2009 at 1:22 pm
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...
December 21, 2009 at 1:25 pm
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.
December 21, 2009 at 1:43 pm
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.
December 21, 2009 at 3:45 pm
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
December 21, 2009 at 7:41 pm
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply