Now that Database Mail is set up and can be used it is important to monitor the process. In particular the following issues should be identified as they may have an impact on the effectiveness of any solution that depends on Database Mail: The service broker must be running The e-mails should not be failing to be delivered to the SMTP server Service Broker To check that the Database Mail service broker is running:

In the previous parts of this series, I have enabled Database Mail, created an account and created a profile. With everything now set up, it is important to test the ability to send an e-mail. At this point, I am assuming that the SMTP server has been set up and is working properly. SQL Server will record any messages sent returned from the SMTP server as we will see, but once the message has been delivered to the SMTP server it is no longer the responsibility of Database Mail.

Over the years I have come across many reasons for Database Mail to be activated, and a couple of reasons not to use it. The main reasons for activating it are the added flexibility that can be achieved in reporting the success or failure of maintenance jobs or any unattended processes and for monitoring and notification purposes. As a business tool for sending e-mail, it is sufficient for small numbers of e-mails but I would always recommend using an application that produces a well-formed e-mail with all of the appropriate attributes rather than using Database Mail.

In Database Mail, Part 1 - Set up I produced a stored procedure that enables Database Mail. The second stage is to create an account which determines the e-mail address that messages will be sent from and the SMTP server that will be used to send the e-mails. It is worth noting that if your organisation has primary and secondary SMTP servers then accounts can be set up for both of the servers, or as many as your organisation has.

In Part 1 of this series I produced a stored procedure to enable Database Mail and in Part 2 I produced a stored procedure to create an account. Now, to be able to use an account, and to send an e-mail you need to have a profile. A profile is related to one or more accounts and is the means by which you identify how the e-mail will be sent. The procedure that I have written will create a profile if one does not exist and will associate an account with it based on the @aRelationshipSequence.

A quick search on the internet for examples of SQL will return a multitude of examples, all of which seem to adhere to different conventions for laying out and formatting SQL. I have always advocated coding standards to allow all procedures to follow similar naming, layout and formatting conventions. The main benefit that I have found is that it is easy to understand the procedures. I was going to document all of my conventions but I found a blog entry by Aaron Bertrand that covers a lot of what I would have said.

A common requirement when managing the resources of a database server is to be able to monitor the space available of the disk drives. Although there is no obvious way to do this through T-SQL there is an undocumented extended procedure called xp_fixeddrives. As always with the undocumented procedures, it is not recommended to use this procedure in a production environment as Microsoft reserve the right to change or remove this procedure as pat of hot fixes, cumulative updates or service packs.

A common requirement is to be able to report on the backup status of each database in an instance. There are of course many tools out there that do just this, but what if your organisation has not purchased one of these tools, or the tools are not installed, for instance in a development environment? Here I present a stored procedure that will produce a table summarising the status of each database in an instance based on the type of backup that you are interested in.

One task that I have found very useful, especially on development servers that have a multitude of databases that are created and backed up in an ad-hoc manner, is to change the default location of the backups. This means that all servers can store ad-hoc backups in a location that conforms to corporate guidelines. It is not possible in SQL Management Studio to make this change. It is possible to make tis change directly in the registry but it is not repeatable.

I am at the point once again when my laptop has accumulated a load of stuff that I do not need, nor do I want. This includes remnants of Beta versions of software that is now in production and software for phones and mobile devices that I no longer use and cannot uninstall. The result is the regular re-build from the OS up. The choice of OS is easy, Windows 7 Ultimate.