Recently, I was asked why e-mails would mysteriously not be sent from one of the servers that I help maintain.  One of the greatest things about working with SQL Server is most everything that happens in the server is data and all I had to figure out is where the data was stored. 

In msdb there are a number of system views that present the data accumulated from the activity in Database Mail:

• dbo.sysmail_allitems
• dbo.sysmail_event_log
• dbo.sysmail_faileditems
• dbo.sysmail_mailattachments
• dbo.sysmail_sentitems
• dbo.sysmail_unsentitems

Microsoft has an article on troubleshooting Database Mail, but none of those seem to address the problem that I had at hand.  To solve the mystery that was brought to me at the beginning of this post, I created the view below and in a not so miraculous way the DBA was able to see the failed e-mails in the system and reason for those failures.

SELECT
    sfi.mailitem_id
    ,sfi.recipients
    ,sfi.subject
    ,sfi.send_request_date
    ,sfi.sent_status
    ,sfi.sent_date
    ,sel.description
FROM msdb.dbo.sysmail_faileditems sfi
    INNER JOIN msdb.dbo.sysmail_event_log sel ON sfi.mailitem_id = sel.mailitem_id
ORDER BY sfi.mailitem_id, sfi.sent_date

And for even more ease of use, I’ve attached a RDL with this information for those that want this to be pretty.

I mentioned in a previous post that I’d delivered a few presentations on SQL Server 2008.  Nothing really more interesting to say about that today except that the SSIS presentation that I did is attached to this posting.

If there are any questions on any of the content, feel free to ask and I’ll respond either in a comment or in another blog post.

In the last couple months I’ve delivered a few presentations on SQL Server 2008 and some of the news features surrounding SSRS and SSIS.  I had a couple requests for copies of the materials so this is just a post to get those out there.  I plan to put out another blog that goes into some of the details.  But for today, here are the links to the presentations and all of the related demonstrations.

If anyone has any questions on any of the content, feel free to ask and I’ll respond either in a comment or in another blog post.

I guess I am limited to a single attachment.  The ETL presentaiton on SSIS will have to be attached to another posting.

The Power of Reporting

I’ve been working with Database Professional a little bit more these days.  One of the databases that I’ve been working on for a while provides a number of diagnostic features that utilizes some msdb stored procedures.

Fortunately, going forward I can stop ignoring the systax errors that occurs because of those cross-database references.  The Visual Studio Team put together a code project that provides all of the necessary stubs for use in Database Professional.

It’ll take a little work to add this in… but completely worth the effort.

I’ve was reviewing wait stats the other day and kept finding this wait stat peaking out the other wait stats by a few hundred milliseconds.  Oh my!  Should I care or not… well long story short is that if all of the CLR assemblies are created in safe mode there is no problem.

Or at least according to SQL PSS who tend to know things…

If you have a SQL CLR application, you may notice that waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT from sys.dm_os_wait_stats are very high.

This is normal and can be safely ignored because these waits reflect internal CLR runtime waits under normal conditions.  Since SQL as a CLR host implements synchronization mechanism, it is able to track these kinds of waits and exposes them via DMV.

The only exception when you need to pay attention to these two wait types is that you actually use unsafe assemblies and use CLR Event objects (ManualResetEvent or AutoResetEvent) yourself.  In other words, if you use ManualResetEvent and you code results in waiting on the object, the wait time will also be reflected in CLR_MANUAL_EVENT type.  The same is true for AutoResetEvent which will be reflected in CLR_AUTO_EVENT type.  But using these two objects will require explicitly creating the assembly in unsafe mode. 

To summarize, if all your assemblies are created in safe mode and you see high waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT, you can safely ignore them.

Jack Li
Escalation Engineer

I’ve never been a fan of the SQL Server 2005 Integration Services task for transferring logins between servers.  It seems that I always misconfigure it or something gets missed.  And when I just want to move a single login its more effort to setup the task than it is worth.  What I’m really saying here is I’m not a fan of the task and I don’t plan to get "gooder" at using it.

Especially when there is a much better alternative… scipting the login with the password.  The link has a script that creates a couple stored procedures that, when executed, provides either a single or all logins with their properties.

The output script looks something like this:

– Login: ExistingUser
CREATE LOGIN [ExistingUser] 
WITH PASSWORD = 0×0100DE1894107F53A6ABF5436BA4F2A6BFFC0C51C156760E70F2 HASHED, 
SID = 0xDA25EBDBF88087D98B65D94B1DF3155B, 
DEFAULT_DATABASE = [master], 
CHECK_POLICY = OFF, 
CHECK_EXPIRATION = OFF

Very useful indeed…

A release candidate has finally arrived… SQL Server 2008 Release Candidate 0.  I’m still downloading it, but hopefully it will make all of my dreams come true and deliver pizza.  I’ve been told that it should at least have the rich text format textbox.

If you haven’t been keeping up with the changes for SQL Server 2008, there is a great series that is worth checking out.  The 24 Hours of SQL Server 2008 webcasts.  I’ve been watching these as they come out and I definitely have to disagree with a lot of those that claim this is a small release to SQL Server.

Reporting Services was overhauled  There are new auditing and performance features.  There a data profiling task.  The MERGE statement has finally arrived.   Encryption is more robust.

True, there isn’t a lot of ground breaking sutff in the release but there is enough little things to make the release worthwhile for most everyone.

I guess I’ve kind of sucked at blogging for the last six months. Times flies when you’re being busy. Hopefully I’ll catch up and complete all of the the blogs I’ve started. Hopefully I can finish a few of those up in the next month. Also, I hope to post some of the work I’ve done in SQL Server 2008 and the presentations from the last couple months.