|
|
Posted by sql_server_user on 05/10/06 21:49
I'm trying to copy all 440 million rows from one table in my SQL Server
2005 db to another table with a different clustering scheme. After a
few test inserts that were successful (up to a million rows in 20
seconds with no problem), I crossed my fingers and went whole-hog with
the naive "insert into dest_table (column1, column2, ...) select
column1, column2, ... from source_table" but of course it ran out of
space in *both* the db log file (at about 130 GB) and the tempdb log
file (at about 54GB) and rolled it back, wrapping up 10 hours after I
kicked it off.
I think it would work if I could insert in batches and/or disable
logging, like with a bulk insert, so I tried exporting the source table
to a flat file in the hopes of bulk-importing it back into the
destination table, but the export just halted for no reason after 176
million rows, using all the CPU and not doing anything at all, just
sitting there. (The fact that this operation not only failed, but
brought down the whole server - any operation on any database in the
instance timed out, no clients could connect, the whole deal - I find
very disappointing for what MS claimed was a scalable, robust product
that could rival Oracle.)
What I'm wondering is, does anyone know of a way to copy a table that
is apparently just too large for SQL Server 2005 to handle?
Thanks,
Seth
Navigation:
[Reply to this message]
|