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] &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.