Table copy, one server to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table copy, one server to another

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2010-11-22 : 13:19:32

Running the following:

insert into Stored_Perf_Data
            (Parameter_ID,
             Portfolio_ID,
             Security_ID,
             Seg_Intersect_ID,
             End_Date,
             Item_Data,
             Source,
             Is_Partial)
select Parameter_ID,
       Portfolio_ID,
       Security_ID,
       Seg_Intersect_ID,
       End_Date,
       Item_Data,
       Source,
       Is_Partial
from   [MI-MKE-IMCSQLDV].Perform.dbo.Stored_Perf_Data

Problem I'm running into is its killing the log file filesystem. The log file starts off @ 2GB in size and its dying when its hits 20GB since the FS is 20GB in size. The database is in simple mode, there are no indexes on the table, and there is no data in the destination table prior to kicking off copy. Trying to understand what I need to do in order to complete the insert without increasing size of log FS and how to prevent the database log from going crazy with an insert of 40 million records.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-22 : 14:09:48

You will need to do it in batches. You can use SELECT TOP n for that and continue doing it until there are no more rows.

By the way, the transaction still gets logged even in SIMPLE recovery model. The different recovery models have to do with what happens after the transaction completes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog