November 13, 2016 at 4:20 pm
Can anyone tell me the quick way to copy a source table of 1.3Tb data to destination table with xml datatype column in source table.
November 14, 2016 at 12:46 am
psred (11/13/2016)
Can anyone tell me the quick way to copy a source table of 1.3Tb data to destination table with xml datatype column in source table.
Can you elaborate further on this and provide all the relevant details such as the Source Database Size, the Destination Database Details etc.? The fact that it has an XML column only tells us that there will be lots of LOBs but that is almost irrelevant to the copy process.
😎
November 14, 2016 at 8:46 am
Source db is 2Tb and destination table is also on same server but each dbs are on different drives.
November 14, 2016 at 9:12 am
psred (11/13/2016)
Can anyone tell me the quick way to copy a source table of 1.3Tb data to destination table with xml datatype column in source table.
Will the two tables need to be kept in sync? If so, just use a synonym instead. If the two tables are required to contain different data, then you have a whole lot more to worry about than just speed. You have a log file explosion getting ready to happen, which also slow things down a whole lot.
To avoid a lot of that, set the target database to BULK LOGGED, create a table WITH the same Clustered Index as the source, and use "Minimal Logging" techniques to copy the data in the same order as the Clustered index on both tables. You should also look into Trace Flag 610 but I'd avoid putting any non-clustered indexes on the table until all the database was copied. It would also be better to chunk the data (perhaps roughly 10 or 20GB at a time) so that if something goes wrong, you won't have to start over completely.
The addition of NCIs after all the data is copied should also be done during BULK LOGGED recovery model.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply