Stored Procedure stuck at waitfor delay '00:00:02'

I have a stored procedure that's supposed to run every night. The procedure deletes data in batches of 10k rows, and between each batch issues a WAITFOR DELAY '00:00:02' with the intention of letting other processes do things in between batches (I didn't write this, this is the intention I was given).

The job ran and has been running for a while now. I ran sp_WhoIsActive and it shows this sp is suspened with the waitfor statement the sql text. Why would sql server keep this from processing further?

Additional information:

It's been running for over a day. It should have deleted 400k records, and has deleted 10k of them.

sp_whoIsActive output:


dd hh:mm:ss.mss 01 06:32:28.126


session_id 58


sql_text


login_name [redacted]


wait_info (131ms)WAITFOR


CPU 42,655,358


tempdb_allocations 7,981,282


tempdb_current 130


blocking_session_id NULL


reads 1,862,017,930


writes 26,792,391


physical_reads 273,169


locks


used_memory 4


status suspended


open_tran_count 0


percent_complete NULL


database_name [redacted]


program_name SQLAgent - TSQL JobStep (Job
0xA887960BA5034D4F89EEBDA2CB934921 : Step 2)

This could be expected behaviour especially if you're deleting based on an index (aka where [primarykeyfield] < targetID)

since that delete will take in the ms of time you'll almost always catch the process in the wait time between the two batches

Say for example the delete takes 10ms (i imagine it will potentially take less than this but its just an example), the wait time is 2000ms

So for every time you run the usp_whoisactive command there is a 10/2010 chance of hitting that in the period of time the delete is running (less than 0.5%) and a 2000/2010 chance of catching it during the wait for delay.

If you want to observe it directly find the spid that its running over and run the profiler over it checking for the SP:StmtCompleted search and you'll see the delete occurring every 2s and going into the waitfor (note if you wan to see the start of the waitfor as well check the SP:StmtStarting but the completed one will give you the duration of how long it takes to run each part.

StackExchange Database Administrators Q#203636, answer score: 4