So it show that one index was missing... However, due to the fact that these tables are heavily used we're gonna need to scratch that idea because sql logs are growing out of control and this process could potentially create a locking scenario.

Now that we're taking a step back and looking at what's happening during the archive process we're wanting to avoid a few things:

1.) Avoid tons of entries in our log files - we're using a hosting company and I believe there is a limit in size of our database which may include the log file. We can grow bigger if we have to but we'll get charged more - in this economy we need to avoid cost as much as possible.

2.) Avoid locking tables that are heavily used - being that this application is an accounting software transaction matching is used heavily. These tables are an important part of the application but data becomes stale very quickly.

3.) Avoid loss of data - if the archiving process happens to fail we need a way of reversing the process without holding 10 mil recs in a sql transaction. So transactions are out of the question for such a large set of records.

The plan of attack:

1.) Say DB1..zTable has 20 mil recs... we'll bcp all 20 mil recs to DB1..zStaging - this will prevent any locking on zTable which is heavily used during the time we need to determine what needs to be archived.

2.) Reverse our query to determine what doesn't need to be archived and bcp DB1..zStaging to DB1..zSwap (possibly 10 mil recs)

3.) Recreate necessary indexes on DB1..zSwap

4.) In one sql transaction swap the name of zTable to zTableOld and zSwap to zTable

5.) Use our query to determine what needs to be archived and bcp out DB1..zStaging to ArchiveDb..zTable_custNo

6.) Perform any clean up necessary

Any feedback is greatly appreciated.