Daily deletion of records

Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far"

select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180

If it meets this criteria I can change the select to a delete? Please Let me know what you thinkEither of these would be faster and make more efficient use of an index on your DATE_TIME column:

select * FROM dbo.Temp_table WHERE DATE_TIME <=dateadd(day, -180, getdate())

select * FROM dbo.Temp_table WHERE DATE_TIME <=dateadd(month, -6, getdate())|||2 million deletes a day and 2 million inserts?

I think I'd worry about the transaction log a wee bit...

USE Northwind
GO

CREATE TABLE myTable99(
Col1 int IDENTITY(1,1)
, Col2 datetime
)
GO

SET NOCOUNT ON

--Lets make some data for 180 days old

DECLARE @.x char(10), @.y int

SELECT @.x=CONVERT(char(10),DATEADD(dd,-180,Getdate()),101), @.y = 4

WHILE @.y < 24
BEGIN
INSERT INTO myTable99(Col2)
SELECT CONVERT(datetime, @.x + ' ' + RIGHT('0'+CONVERT(varchar(2),@.y),2)+':00:00')
SELECT @.y = @.y + 4
END

INSERT INTO myTable99(Col2)
SELECT CONVERT(datetime,@.x + ' ' + '23:59:59')

SELECT * FROM myTable99

-- And basically Delete them in the same manner

SELECT @.x=CONVERT(char(10),DATEADD(dd,-180,Getdate()),101), @.y = 4

WHILE @.y < 24
BEGIN
BEGIN TRAN
DELETE FROM myTable99
WHERE Col2 <= CONVERT(datetime, @.x + ' ' + RIGHT('0'+CONVERT(varchar(2),@.y),2)+':00:00')
SELECT @.y = @.y + 4
COMMIT TRAN
END
BEGIN TRAN
DELETE FROM myTable99
WHERE Col2 <= CONVERT(datetime,@.x + ' ' + '23:59:59')
COMMIT TRAN

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
GO

DROP TABLE myTable99
GO

SELECT @.@.TRANCOUNT|||Oooops...forgot this...

CREATE INDEX myIndex99 ON myTable99(Col2)

GO

Make sure you have the index...it'll slow down inserts, but the index seek, with the transaction batch deletes should fly...|||Why can't you just do:

DELETE FROM dbo.Temp_table WHERE DATE_TIME <=dateadd(day, -180, getdate())

That will delete all records that are older then 6 months.|||That's what I came up with so far. I just used select for testing in the Anayser. The only thing is it does'nt address the transaction log.|||I've changed the backup type and I continue to get this error. I'm at a lost on this one. The sequence goes like this. I make my select/delete statement followed by a backup the log before running the shrink. I've attempted suppling a path in the statement but I just get another error. The complete statement is:

select * FROM dbo.INCOMING WHERE DATE_TIME <=dateadd(day, -180, getdate())
BACKUP LOG wslogdb62 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(wslogdb62_log, 1)

Could not locate file 'wslogdb62_log' in sysfiles.|||Exactly what is your SELECT statement for, and why do you think you need to combine that with backing up your log? The results of the select statement are not logged. It makes no change to the database.|||The select is only when testing??|||Is that a question? Or a statement made in confusion?|||Just a statement in my case.