SELECT count(*)
FROM  tblinstantmessage  
WHERE (DateDiff(dd, date, GetDate()) > 60  AND checked = 1 )
OR  (deletedbysender =1 and deletedbyrecipient = 1 AND DateDiff(dd, date, GetDate()) > 14)

DECLARE @intRowCount bigint,
@intBatchSize bigint,
@intRowTotal bigint,
@dtStart datetime,
@intElapsedS bigint,
@intTotalMax bigint

SELECT @intRowCount = 1, -- Force first loop iteration
@intBatchSize = 500, -- Records to delete in each batch
@intRowTotal = 0,
@intTotalMax = 0

WHILE @intRowCount >= 1 or @intTotalMax <= 50
BEGIN

SET ROWCOUNT @intBatchSize -- Restrict ALL subsequent operations to batch size

BEGIN TRANSACTION
SELECT @dtStart = GetDate()


	SELECT TOP 600  instantMessageID
INTO  #TEMP_IM
FROM  tblinstantmessage  

WHERE (DateDiff(dd, date, GetDate()) > 60  AND checked = 1 )

OR  deletedbysender =1 and deletedbyrecipient = 1 AND DateDiff(dd, date, GetDate()) > 14


DELETE D
FROM  #TEMP_IM AS T
      JOIN tblinstantmessage AS D 
          ON D.instantMessageID = T.instantMessageID
-- WHERE deletedbysender =1 and deletedbyrecipient =1 -- Belt and braces!

drop table #TEMP_IM

SELECT @intRowCount = @@ROWCOUNT, -- Rows actually deleted
@intRowTotal = @intRowTotal + @intRowCount -- Total rows
COMMIT
SET ROWCOUNT 0 -- Remove the restriction
SELECT @intElapsedS = DATEDIFF(Second, @dtStart, GetDate())
IF @intElapsedS > 120
BEGIN
SELECT @intBatchSize = @intBatchSize / 2 -- Reduce batch size
END
ELSE
IF @intElapsedS < 30
BEGIN
SELECT @intBatchSize = @intBatchSize + (@intBatchSize / 4) -- Increase batch size
END

SELECT @intTotalMax = @intTotalMax + 1

-- ... Optionally backup TLog if @intRowTotal > 1,000,000 and reset @intRowTotal to 0 ...
WAITFOR DELAY '000:00:06' -- Allow other queries to run
END

declare @LeftToDelete int

select @LeftToDelete = 
count(*)
FROM  tblinstantmessage  

WHERE (DateDiff(dd, date, GetDate()) > 60  AND checked = 1 )

OR  deletedbysender =1 and deletedbyrecipient = 1 AND DateDiff(dd, date, GetDate()) > 14

while @LeftToDelete > 0

BEGIN

-- Start a transaction
-- delete the top 600
-- commit the transaction
-- get a new count

END

DELETE TOP (600)
FROM   Table1
WHERE  Date >= DATEADD(DAY, -60, CURRENT_TIMESTAMP) AND checked = 1
       OR DeletedBySender = 1 AND DeletedByRecipient = 1 AND DATEADD(DAY, -14, CURRENT_TIMESTAMP)

WHILE 1=1
BEGIN
  -- do delete
  IF @@ROWCOUNT = 0 BREAK
  -- process new batch size and do delay
END

quote:

---

Originally posted by jshepler

You could keep looping until rowcount = 0. Something like

WHILE 1=1
BEGIN
  -- do delete
  IF @@ROWCOUNT = 0 BREAK
  -- process new batch size and do delay
END

No need to try and figure out how many iterations you need. You do, at most, 1 iteration with 0 deletes.


/jeff

---