I’m posting this to show what I have put in place ( that is working ) so other, more accomplished and smarter DBAs can give feedback and help others in the same situation. I didn’t see any solutions like this anywhere else, so my 16-year-old daughter told me I should share it. I know that it’s a hack, but we are often put in situations where a one-off execution needs to happen and what we have to work with can’t be changed for whatever reason. I hope this helps someone, and look forward to seeing the other ways people have handled this situation.

I am purging ~400 million old records from an active log table with a dozen indexes, and the database is sitting on a NAS so the deletes are slow ( no, I can’t change any of the configuration; I’m just a contractor helping with the process ). The locks that that this generates brings the user application to a halt. I need a non-blocking way to keep the deletes going while allowing the users to keep working.

The short answer is to break whatever your long-blocking process is into chunks. The problem I had with that in SQL Server is that “Inner” or “Nested” Transactions don’t help; they don’t release locks when you commit them – only when the main outer transaction commits – so the whole thing can be rolled back. I couldn’t just loop through the deletes in a single process, because the table would be locked until the whole process finished ( estimated 20 hours ), so I looked to Jobs. Scheduling a job that would delete 25,000 records every 30 seconds would take approximately 8,000 minutes ( over 5.5 days )… not a great option.

Here’s my “solution”: I created a SQL Server Agent Job with two steps. The first step deletes 25,000 records at a time, and exits if another process is being blocked by it. The second step is just there to let the first step’s transaction close and release the locks, then go back to Step 1.

Step 1: Set to Go to the next step on Success, and Quit the job on Failure

While Not Exists(Select [session_id] From [sys].[dm_exec_requests] Where [blocking_session_id] = @@SPID)
Begin
      Delete Top (25000) From [My_Table] Where 'My_Criteria' = 'Delete_This'
      If 'Criteria for Exiting the Process' = 'Whatever reason you want to quit'
            ;THROW 51000, 'Reasons', 1
End

Step 2: Set to Go to Step 1