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?
It's been running for over a day. It should have deleted 400k records, and has deleted 10k of them.
dd hh:mm:ss.mss 01 06:32:28.126
program_name SQLAgent - TSQL JobStep (Job 0xA887960BA5034D4F89EEBDA2CB934921 : Step 2)