[EDIT - I did not invent this code, just remembered that I don't remember where I found it :blush: but it proved useful to me.]

I did this for a table with about 3 millions rows. By default the system did not trim old rows, so I had to bring it down to keeping only rows for the last 60 or 90 days. Probably not the most efficient, but it worked without blocking up things. Hope it helps.

Before delete:

Table NameRow Count

YourTable1797867

-- Turn off record messages.

SET NOCOUNT ON

-- Set up variables to store dates.

DECLARE @StartDate datetime

DECLARE @EndDate datetime

-- Set start and end date values.

SET @StartDate = '01/15/2015'

SET @EndDate = '01/31/2015'

-- Loop in batches of 1000 records to break up processing.

WHILE EXISTS ( SELECT * FROM YourTable WHERE YourDateTime BETWEEN @StartDate AND @EndDate)

BEGIN

SET ROWCOUNT 1000

DELETE FROM YourTable WHERE YourDateTime BETWEEN @StartDate AND @EndDate

SET ROWCOUNT 0

END

-- Run select to make sure records have been deleted

SELECT MIN(YourDateTime) FROM YourTable

- webrunner