I have a table within a SQL 2005 database that fills up after about three
months and I'd like to schedule a daily job to delete rows or records older
than 60 or 90 days.

Could someone provide the syntax for a DELETE script?

Thanks in advance.

The following is one way to do it while minimizing the transaction length
and impact of locking on the table:

DECLARE @BatchSize INT,
        @Criteria DATETIME,
        @RowCount INT
SET @BatchSize = 1000
SET @Criteria = getdate()-60
SET @RowCount = 1000
SET ROWCOUNT @BatchSize
WHILE @RowCount > 0
BEGIN
    DELETE
    FROM MYTABLE
    WHERE MYCOL < @Criteria
    SELECT @RowCount = @@rowcount
END
SET ROWCOUNT 0


Show quoteHide quote

In the next release of SQL Server (after SQL Server 2008) SET ROWCOUNT
will not affect DELETE, INSERT, and UPDATE statements. It is better to
use TOP to limit the number of rows deleted.

DELETE TOP (@BatchSize) FROM...

Thanks for point that out.  It is documented as such in the 2008 BOL.
However, you should still use TOP to limit the number of affected rows at a
time as follows to minimize impact and duration of the process:

DECLARE @Criteria DATETIME,
        @RowCount INT
SET @Criteria = getdate()-60
WHILE @RowCount = 1000
BEGIN
    DELETE TOP(1000)
    FROM MYTABLE
    WHERE MYCOL < @Criteria

    SELECT @RowCount = @@rowcount
END

Doing a single delete as in your other post is generally less efficient and
takes more time than batching it in smaller groups.  In SIMPLE recovery, it
also causes the transaction log to grow needlessly since it can't truncate
on checkpoint until the entire delete is completed and commited.

Show quoteHide quote

I agree on the batch delete comments. But if you will be running this
process daily as indicated, I doubt there will be a million or more rows
  to delete every day.

If you will be deleting daily those rows, then probably a simple delete
statement like this will do:

DELETE FROM Foo
WHERE foo_date <= DATEADD(DAY,
                           DATEDIFF(DAY,
                                    '20010101',
                                    CURRENT_TIMESTAMP) - 60,
                           '20010101');

The DATEDIFF function is used to trim the time portion of the current
date so you do not get partial day removed. Also, you can set 60 as
parameter.

This can easily be a long running transaction that causes excessive log
growth and blocking in the database while it runs needlessly depending on
the number of rows being deleted each time it runs.  A batched delete using
SET ROWCOUNT in older versions of SQL Server or TOP(N) moving forward
overcomes both of these problems by minimizing the duration of each actual
delete statement.

Show quoteHide quote

SET ROWCOUNT will be in effect for any triggers. This could give rise to
orphan rows.

Cascading constraints should enforce that kind of logic, not triggers.  If
your constraints are enabled with check, the transaction should halt and
rollback to prevent the constraint violation.

Show quoteHide quote

Server Disk Space
in full recovery mode but log keeps self-truncating
Max server memory being ignored
Consolidate datafiles to one drive
how to store decimals in tables
Security Question
Cumulative package 2 for SQL 2008
How to do bulk update in SQL Server 2005.
Hebrew + Arabic collation
Maintenence Cleanup Task does not delete backup files