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
GO
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, *
FROM dbo.sysmail_allitems WITH(NOLOCK READUNCOMMITTED)
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
GO
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
    GO
    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 = 
'<Catalog>
	<Product>
		<Name>Selle Italia Road</Name>
		<Code>SIR-1</Code>
		<Category>Saddle</Category>
		<Description>Sleek saddle from Selle Italia</Description>
		<UnitPrice>50</UnitPrice>
		<Currency>USD</Currency>
	</Product>
	<Product>
		<Name>Brooks Tourer</Name>
		<Code>BT-T</Code>
		<Category>Saddle</Category>
		<Description>Brooks leather saddle for touring</Description>
		<UnitPrice>120</UnitPrice>
		<Currency>USD</Currency>
	</Product>
</Catalog>
'
--Read from nodes
SELECT
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]
FROM 
@xmlProduct.nodes('//Catalog/Product') AS TAB(c)

Output:
XQueryOutput

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!

SELECT TOP 10 
EST.text
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
t.name, COUNT(1) tcount
FROM INFORMATION_SCHEMA.routines r
INNER JOIN sys.tables t ON r.ROUTINE_NAME LIKE ('%' + t.name + '%')
GROUP BY t.name 
ORDER BY tcount desc

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

Tagged with , , , , ,

Follow

Get every new post delivered to your Inbox.