I was monitoring my sql server instance when I noticed within my alerts that a session had been waiting for 6,710,683 ms. The database was msdb and the status was Suspended. It wasn’t really concerning me as it wasn’t causing any blocking and had only consumed 41ms of cpu time. However I wanted to understand what was causing this.
I ran sp_whoisactive (a stored procedure written by Adam Mechanic) to get some more info. It revealed the session had been running for around 4 hours, and under the ‘program name’ from the output of sp_whoisactive it had DatabaseMail listed.
On my server I have configured my jobs to email me if any fail and also I receive alerts via email if there is any blocking, deadlocks, high cpu etc etc.
I noted that I had not had any email alerts from this server for about 4 hours so had an idea that this wait type was related to that. I deliberately edited one of my scheduled jobs to fail by simply editing the stored procedure it calls to a non existing name.
After the job failed, I received my email and then re-ran sp_whoisactive. The long running suspended session had now gone and had been replaced by another spid with the same program name of ‘DatabaseMail’.