SQL Delete Query --- more detail

Question

I have several tables in a database. One table (tbl_transactions) has thousands of orphaned records that are not linked to any of the remaining tables. I need to run a script that will delete these records in order to regain some lost space in my database. I tried to run a script that deleted all the records, but the log file consumed 20 GB of space, thus filling the HDD and the script did not complete. My script looks like this:

delete tbl_Transactions
where not exists (select * 
                    From tbl_SocketConnections
                   where tbl_Transactions.TransactionID = tbl_SocketConnections.TransactionID)
  And Not Exists(Select * 
                   From tbl_ProtocolCommands
                  where tbl_Transactions.TransactionID = tbl_ProtocolCommands.TransactionID)
  And Not Exists(Select * 
                   From tbl_EventRules
                  where tbl_Transactions.TransactionID = tbl_EventRules.TransactionID)

There are several other tables, but the pattern repeats. Can someone advise on how I can limit the scope of this script to say 1000 records at a time?

Answer 1

You can add WHERE clauses and LIMIT TO clauses to any delete statement.

Answer 2

SQL Server 2005+

Change TOP to LIMIT covers mySQL too

SELECT 'starting' --gives one row
WHILE @@ROWCOUNT <> 0
    delete TOP (10000) tbl_Transactions
    where not exists (select * 
                        From tbl_SocketConnections
                       where tbl_Transactions.TransactionID = tbl_SocketConnections.TransactionID)
      And Not Exists(Select * 
                       From tbl_ProtocolCommands
                      where tbl_Transactions.TransactionID = tbl_ProtocolCommands.TransactionID)
      And Not Exists(Select * 
                       From tbl_EventRules
                      where tbl_Transactions.TransactionID = tbl_EventRules.TransactionID)

Answer 3

If you are using MS SQL Server 2005 or later you can use a while loop and delete TOP 1000 * otherwise you could use the while loop with a WHERE clause that uses IN (SELECT TOP 1000 * ...)

Answer 4

Since you didn't specify what percent of the table is bad vs good, I can't say for sure but ... If the number of deletes exceed or approach the number of actual good data rows I would look at other options.

• Rename the current table, recreate it with the old name, and run an insert to populate the new copy.

• BCP out the good rows, truncate the table, and BCP back in (which aren't logged operations).

As there doesn't appear to be any foreign keys involved, these should be easy to do.

my $.02 anyway.