Database Mail shutting down. Mail queue has been stopped.   Leave a comment

DB mail in our production server started acting up last week. We have SQL Server 2012 SP1. There were a couple of sets of symptoms and two separate steps were taken to get DB mail working again.

I hope you don’t spend 4 hrs like I did, to get a resolution.

Symptom 1:

Mails will not be sent anymore. Sending test mail will not work.  When DB mail status is checked using the following SP, it will return “STARTED”.

USE msdb
EXEC dbo.sysmail_help_status_sp

However, checking the status of mail items themselves using the following SP will show that they are “unsent”.

SELECT TOP 10 sent_status, *
ORDER BY send_request_date desc

Stopping DB mail using the following SP does not work anymore. The command takes forever to execute and nothing happens.

USE msdb
EXEC dbo.sysmail_stop_sp

If these are the symptoms,

  1. Kill the process “DatabaseMail.exe” from task manager of the server to stop DB mail.
  2. Check status using sysmail_help_status_sp and ensure it shows “STOPPED”.
  3. Start DB mail using the following command.
    USE msdb
    EXEC dbo.sysmail_start_sp
  4. Check status of mail items. The sent_status column should now show “sent”.

Symptom 2: Later

Posted July 27, 2015 by monk912 in Uncategorized

XQuery   Leave a comment

In 2005 version of SQL Server, XQuerying was introduced. It’s a powerful feature and when used together with a CTE, it helps to keep code simple and clean. I use XML extensively in my code and many a time, I find myself looking up code, searching the internet or trying things out myself. I hope this post will be a single point of reference to most of the usual XQuery coding.

Following is a simple script to read from an XML variable. Same code can be used to read from an XML column as well.

NOTE: I will append more XQuery code samples to this post.

DECLARE @xmlProduct XML = 
		<Name>Selle Italia Road</Name>
		<Description>Sleek saddle from Selle Italia</Description>
		<Name>Brooks Tourer</Name>
		<Description>Brooks leather saddle for touring</Description>
--Read from nodes
c.value('Name[1]','varchar(50)') [ProductName],
c.value('Code[1]','varchar(50)') [Code],
c.value('Category[1]','varchar(50)') [Category],
c.value('UnitPrice[1]','varchar(50)') + CHAR(32) + c.value('Currency[1]','varchar(50)') [Price],
c.value('Description[1]','varchar(50)') [Description]
@xmlProduct.nodes('//Catalog/Product') AS TAB(c)


Posted March 27, 2013 by monk912 in Database, SQL Server, TSQL

Tagged with , , , , , , , ,

Retrieve unsaved query   Leave a comment

I was working on a remote machine and while I was away, the machine was restarted. Lost the scripts I had not saved, or so I thought; and then I came up with this – was a life saver!

FROM sys.dm_exec_query_stats EQS
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) EST
WHERE EST.text LIKE '%text%'
ORDER BY last_execution_time DESC

Find tables which are most commonly used in Stored Procedures   Leave a comment

When you start working on a new project, one of the important tasks as a database developer is to get used to the table names. Here’s a script which will help you identify the most commonly used tables (assuming that the most commonly used are the most important).

SELECT TOP 50, COUNT(1) tcount
INNER JOIN sys.tables t ON r.ROUTINE_NAME LIKE ('%' + + '%')
ORDER BY tcount desc

Posted April 4, 2011 by monk912 in Database, SQL Server, TSQL

Tagged with , , , , ,


Get every new post delivered to your Inbox.