Stored Procedure stuck at waitfor delay '00:00:02'
This could be expected behaviour especially if you're deleting based on an index (aka where [primarykeyfield] &lt; 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.