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

• Charlieface: What exactly are you asking? Best practice: matter of opinion and depends on exactly what you are doing. Should you batch it: possibly, depends who else is using the table, and whether you care about a large transaction log. Is your error handling correct: no, instead of SELECT ERROR_MESSAGE etc just do THROW;. How is the error-handling tag relevant?
• dexon: Thanks for your answer. The table is used by other application. About the THROW... I'll Change it, Thanks :)
• J.D.: Is your application what's going to run this stored procedure?...you could just schedule it in a SQL Agent Job instead which doesn't have a timeout so you wouldn't have to worry so much about error handling timeouts then.
• dexon: I'm running it with Power Automate on Azure Sql Paas.
• J.D.: You might want to mention those details in your post.

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/

• dexon: Hey, Great Response,. The partitioning scheme is very interesting! wasn't familiar with it, Many Thanks :)