SQL Server 2016 Standard Ed
We have a clean up job that runs some pretty simple sql:
use productionDB
go
delete from transactionaltable where
createdat < DATEADD(day, -21, GETDATE())
go
This runs every weeknight, and has been great…. until last night, when an (apparent) lock escalation or conflicting jobs caused it to hang, locking the table and causing all kinds of mess.
I thought to myself: "Hey, there must be some some magic I can put on the job or on the step, a property like 'kill if running for more than ten minutes'" But I can find no such.
Is the only technique to:
- Fire the real job at 0100
- Fire a kill job at 0110 // a safety that rarely gets a hit
The kill job to be built with this technique:
USE msdb ;
GO
EXEC dbo.sp_stop_job N'Your Job Name' ;
GO
Or is there a better/cleaner way to do this?
SOLUTION
Stop using that crude WHERE statement and start deleting rows in small batches, using a method like that described here.
Best Answer
I have been using the following stored proc on a SQL agent job:
Edited to include table creation: