GilaMonster (7/12/2009)
If you want to delete the oldest rows, then you need something like this (because order by is not permitted in a delete statement)
DELETE FROM SomeTable WHERE PrimaryKeyColumn IN (SELECT TOP (200) PrimaryKeycolumn FROM SomeTable ORDER BY SomeDate)
Or, you identify the oldest rows by your date column instead of order:
DELETE TOP (200)
FROM SomeTable
WHERE datecolumn 0
BEGIN
DELETE TOP (200)
FROM SomeTable
WHERE datecolumn <= {some date limiter};
SET @rowcount = @@rowcount;
-- CHECKPOINT --database is in simple recovery model
BACKUP LOG {database} TO DISK = '{backup location & file}';
END;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs