sqlnIGHTS.COM

My Sql Server info repository

Wait Types

Broker_Recieve_Waitfor

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’.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s