Sql-server – Cannot perform SELECT COUNT(*), rows over 1 billion on a table

deletesql servert-sql

I have this table that contains over a billion rows and to issue the query, I issued:

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'audit_tb' AND (index_id < 2)

Output:
1055075195

I was asked to DELETE records in 2014 so to test out, I tried Deleting the last 3 months in 2014, it's been over 1 hr and it's still executing, any ideas? I suspect there are bad rows.

 DELETE 
  FROM [audit_tb]
  where datetime >= '2014-01-01 00:00:00' 
  AND datetime <= '2014-03-31 00:00:00' --3 months in 2014

The table also has a CLUSTERED INDEX.

Best Answer

Here is a script to delete in chunks. It deletes in chunks of 4500 records to avoid lock escalation. It performs 100 loops to delete a total of 450,000 rows. If your database is using the FULL recovery model, you may want to backup the transaction log after each run to keep if from growing too large.

SET NOCOUNT ON;
DECLARE @continue INT
DECLARE @rowcount INT
DECLARE @loopCount INT
DECLARE @MaxLoops INT
DECLARE @TotalRows BIGINT
DECLARE @PurgeBeginDate DATETIME
DECLARE @PurgeEndDate DATETIME

SET @PurgeBeginDate = '2014-01-01 00:00:00'
SET @PurgeEndDate = '2014-03-31 23:59:59'

SET @MaxLoops = 100
SET @continue = 1
SET @loopCount = 0

SELECT @TotalRows = (SELECT COUNT(*) FROM audit_tb (NOLOCK) WHERE datetime between @PurgeBeginDate and @PurgeEndDate)
PRINT 'Total Rows = ' + CAST(@TotalRows AS VARCHAR(20))
PRINT ''

WHILE @continue = 1 
BEGIN
    SET @loopCount = @loopCount + 1
    PRINT 'Loop # ' + CAST(@loopCount AS VARCHAR(10))
    PRINT CONVERT(VARCHAR(20), GETDATE(), 120)

    BEGIN TRANSACTION
        DELETE TOP (4500) audit_tb WHERE datetime between @PurgeBeginDate and @PurgeEndDate
        SET @rowcount = @@rowcount 
    COMMIT

    PRINT 'Rows Deleted: ' + CAST(@rowcount AS VARCHAR(10))
    PRINT CONVERT(VARCHAR(20), GETDATE(), 120)
    PRINT ''

    IF @rowcount = 0 OR @loopCount >= @MaxLoops
    BEGIN
        SET @continue = 0
    END
END

SELECT @TotalRows = (SELECT COUNT(*) FROM audit_tb (NOLOCK) WHERE datetime between @PurgeBeginDate and @PurgeEndDate)
PRINT 'Total Rows Remaining = ' + CAST(@TotalRows AS VARCHAR(20))
PRINT ''


GO

I have used this in a SQL Agent job to run repeatedly until the required number of rows were removed. I just schedule the job to run every 15 minutes so my tran log backup job runs between runs of this job.

I hope this helps

Related Question