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.
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”.
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.
If these are the symptoms,
- Kill the process “DatabaseMail.exe” from task manager of the server to stop DB mail.
- Check status using sysmail_help_status_sp and ensure it shows “STOPPED”.
- Start DB mail using the following command.
- Check status of mail items. The sent_status column should now show “sent”.
Symptom 2: Later
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>
<Description>Brooks leather saddle for touring</Description>
--Read from nodes
c.value('UnitPrice','varchar(50)') + CHAR(32) + c.value('Currency','varchar(50)') [Price],
@xmlProduct.nodes('//Catalog/Product') AS TAB(c)
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
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
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