Vryjdfkk

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.

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

select 1 

while (@@rowcount > 0)

begin 

  DELETE top (100000) 

  FROM [audit_tb]

  where datetime >= '2014-01-01 00:00:00' 

    AND datetime <  '2015-01-01 00:00:00' 

end

You can adjust the 100000 but that is a good starting point