SQL Batched Delete

sql sql-server tsql sql-server-administration
Tom Ferguson picture Tom Ferguson · May 22, 2009 · Viewed 18k times · Source

I have a table in SQL Server 2005 which has approx 4 billion rows in it. I need to delete approximately 2 billion of these rows. If I try and do it in a single transaction, the transaction log fills up and it fails. I don't have any extra space to make the transaction log bigger. I assume the best way forward is to batch up the delete statements (in batches of ~ 10,000?).

I can probably do this using a cursor, but is the a standard/easy/clever way of doing this?

P.S. This table does not have an identity column as a PK. The PK is made up of an integer foreign key and a date.

Answer

Nick Kavadias picture Nick Kavadias · May 22, 2009

You can 'nibble' the delete's which also means that you don't cause a massive load on the database. If your t-log backups run every 10 mins, then you should be ok to run this once or twice over the same interval. You can schedule it as a SQL Agent job

try something like this:

DECLARE @count int
SET @count = 10000

    DELETE  FROM table1 
    WHERE table1id IN (
        SELECT TOP (@count) tableid
        FROM table1
        WHERE x='y'
    )

Related questions

RESTRICTED_USER

Before changing database schema I issue: ALTER DATABASE SET RESTRICTED_USER On completion: ALTER DATABASE SET MULTI_USER I understand that a running transaction will be permitted to continue until completion. Q: Is there any way to wait till all …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More