Clearing db mail queue in sql server
My DB server seems to be stuck in some kind of a trance. When I run a query, it runs fine. When I run the same query as part of @query tag in maildb, it gets stuck.
When I remove the query, and send an empty mail, it gets stuck in mail queue.
EXEC [MSDB].dbo.sp_send_dbmail @profile_name='Email Test',
@recipients='[email protected]',
@subject='Testing auto notification sql jobs. Please ignore',
@body_format = 'TEXT',
@body='hello'
This above line gets stuck for no reason. I sent a mail using "Send a test mail" option in "Database Mail", and that worked perfect. So, clearly, there is nothing wrong with the actual email sending process.
delete from msdb.dbo.sysmail_unsentitems;
This line is just running a default option from SQL server trying to clear the queue, and even this gets stuck.
I have no idea how to resolve this problem. I tried restarting dbmail service, but that didn't work. Sadly, I can't restart the whole sql agent service (perks of being on production).
I would like to know any thing that might get my server out of this crazy.
Answer
Edit:
Never mind. I found the issue. Table was locked for some reason, which in turn locked the mail service. So, I simply killed all the process in Activity Monitor that were started by me. Resolved the lock on table, and it works well again.