Routinely deleting data from tables using stored procedure

Question

I've created a maintenance stored procedure to delete data older than 30 days on events table.

What are the best practices for deleting data?

I've encased the procedure with error handling, i.e. TRY... CATCH, and also used SET XACT_ABORT ON - for timeout error from application.

Should I use a loop in running the delete process, as to not fill the transaction log?

Right now I don't expect many rows to be deleted but what happens if there are millions of rows?

Here is the code I've written:

    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    SET XACT_ABORT ON;
    BEGIN TRY              
        BEGIN TRANSACTION               
            DELETE FROM Saga.SagaEvents 
            WHERE Created < DATEADD(DAY,-@NumDays,GETUTCDATE())
        COMMIT TRANSACTION               
    END TRY              
    BEGIN CATCH              
        IF @@TRANCOUNT > 0              
            ROLLBACK              
                
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int              
        SELECT @ErrMsg = ERROR_MESSAGE(),              
            @ErrSeverity = ERROR_SEVERITY()              
              
        RAISERROR(@ErrMsg, @ErrSeverity, 1)              
    END CATCH              
END
GO

Answer 1

If this is a maintenance job and you want it to be resumable, then you should delete in batches with a commit in between them. You will need to call it multiple times if you experience timeouts though. This will prevent the log file from filling up if the number of records per date becomes onerous, and prevent blocking as well. Further enhancements is to include a max runtime and exit when you reach that as well.

See my below example.

SET NOCOUNT ON 

SET XACT_ABORT ON 

DECLARE @RCount INT = 1
DECLARE @BatchSize INT = 5000

BEGIN TRY

    WHILE @RCount <> 0
    BEGIN

        BEGIN TRANSACTION 

            DELETE TOP (@BatchSize)
            FROM Saga.SagaEvents 
            WHERE Created < DATEADD(DAY, -@numDays, GETUTCDATE())

            SET @RCount = @@ROWCOUNT

        COMMIT TRANSACTION 

    END

END TRY
BEGIN CATCH

    --If we have an open transaction, then rollback.
    IF @@TRANCOUNT > 0 
    BEGIN
        ROLLBACK
    END

    --Toss back the error message to the calling application
    ;THROW;

END CATCH

However, if this table is still in it's infancy, then I recommend (and have done this myself) a rotating partitioning scheme. Thomas Kejser has a great blog on it. Set the number of partitions to a reasonable number of "days" (I use 512 usually) and then just identify the partitions that shouldn't exist and truncate them specifically.

It's only available on the wayback machine for some reason, but this is the Wayback link: https://web.archive.org/web/20190209220048/http://kejser.org/table-pattern-rotating-log-ring-buffer/

Original Link: http://kejser.org/table-pattern-rotating-log-ring-buffer/