Problem running a query against a large table in MS SQL 2005 using all the server resources

I have an MSSQL 2005 database with GBs of orphaned useless data. This bad data causes the database to be hard to work with, taking 4 hours to back up and 7 hours to restore. All the bad data is in one table.

I have decided to import the data into another table and delete the records I believe are bad. So I am running a simple sql query like this:

Insert databaseB.dbo.t开发者_JAVA技巧able (col1,col2) 
select col1,col2  from databaseA.dbo.table

My last attempt to run the above query ran for 12 hours without completing before I had to cancel and restart the sql services because any application that used any database on that server would not respond.

After this operation completes I will be running simple delete “delete databaseA.dbo.table where condition=1” which the last time I tried it locked the server as well.

I do not need any locks on the table I am trying to update.

Any suggestions on how I can limit this query to not affect this or other production systems? Are there any flags or options I can set to make this run quicker or smoother? (like changing the recovery model) It can run for days if necessary I just need to somehow throttle the resources used. Thanks in advance for any advice and let me know if I have provided enough information.

It has occured to me to backup and restore the database to another location. Truncate the table and then import the good data back to the table in question. But since it would take a minimum of 11 hours to complete step 1, and step 2 is an unknown length, I do not like this option.

The easiest way to do this without affecting other operations, is to transfer the information using a cursor and insert one row at a time or loop through the rows manually.

For a cursor see here: http://msdn.microsoft.com/en-us/library/ms180169.aspx

Looping:

1. Select 10 rows
2. Import them into the new table

3. Delete those 10 rows

   WHILE (SELECT COUNT(*) FROM YOUR_BAD_RECORDS) > 0
   BEGIN
       INSERT INTO TABLE...SELECT TOP 10 * FROM BAD_RECORDS
   END
   

Do this until the job completes. It will take longer, but it won't lock the server resources and allow other systems to access the db.

Because you mention this is blocking other database activity, it sounds to me that the problem you are encountering is that your insert into the archive table is locking the records that are being selected and blocking other users. Your statement may also be getting blocked by other activity if it is running during production hours.

I suggest an incremental process to move these records. Your tags indicate you are using SQL Server 2005, which supports the OUTPUT clause, so you can delete the old records and insert them into the archive table in one shot. Choose a number of records that seems reasonable to do at a time, and if you use the READPAST locking hint, you won't get blocked on records locked by other users. Just run your batch as many times as necessary, perhaps as a scheduled task. Of course make sure your WHERE clause only selects records you want to archive.

DELETE TOP (10000) FROM dbo.Valid_Date WITH (READPAST)
OUTPUT 
    DELETED.valid_date, DELETED.valid_date_KEY
    INTO dbo.Archive_Of_Valid_Date(valid_date, valid_date_KEY)
WHERE 
    valid_date < '19800101'

Edited to expand on Matthew's comment to my answer

Using a COUNT(*) to determine if there is any work to do is not free, so most of the time you can either use EXISTS (which short-circuits as soon as any record that matches is found) or use the @@rowcount after the statements

WHILE (SELECT COUNT() FROM DatabaseA.dbo.table WHERE Condition=1) > 0 
BEGIN 
    DELETE TOP (100) FROM DatabaseA.dbo.table WITH (READPAST)
    OUTPUT 
        DELETED.x, DELETED.y
        INTO ArchiveDB.dbo.table(x, y)
    WHERE 
        condition = 1
END

Try:

WHILE (1 = 1)
BEGIN 
    DELETE TOP (100) FROM DatabaseA.dbo.table WITH (READPAST)
    OUTPUT 
        DELETED.x, DELETED.y
        INTO ArchiveDB.dbo.table(x, y)
    WHERE 
        condition = 1

    IF(@@rowcount <= 0)
        break;
END

or

WHILE (EXISTS(SELECT * FROM DatabaseA.dbo.table WHERE Condition=1)) 
BEGIN 
    DELETE TOP (100) FROM DatabaseA.dbo.table WITH (READPAST)
    OUTPUT 
        DELETED.x, DELETED.y
        INTO ArchiveDB.dbo.table(x, y)
    WHERE 
        condition = 1
END

Remove all indexes and foreign keys from the destination table. Recreate after the table is cleaned.

In instead of deleting after, just don't insert what will be deleted.

Insert databaseB.dbo.table (col1,col2) 
select col1,col2  from databaseA.dbo.table
where condition <> 1