Microsoft SQL Server Tips Series: Automatic database quota warning emails

Here’s a message from one of our customers, and I thought it was a great idea:

“I have discovered the problem. The database was full. Can I suggest that in future you implement a warning mechanism when approaching maximum usage of things like Database, Bandwidth etc. This would be very useful, even if it was just an automatic email sent out. Thanks.”

We actually do already have a process in place to warn you if you exceed your monthly bandwidth, but we don’t have one in place for databases because it is difficult to automate the process on a grand scale.

It would mean we would have to scan every database and make sure the email addresses we have on file are current and active, and that’s not always the case.  It’s not a good use of computer resources or time, and not every customer needs it.  So I am going to provide you with a custom solution!

That’s the beauty of the DiscountASP.NET hosting platform.  We give you the tools you need to create your own solution.  You may not realize that if we were to automate some processes it could lead to less freedom (more restrictions) in the way you can build or manage your application.

It’s a difficult balancing act to implement solutions and features that are useful, effective, and efficient.  Anyway, this solution can be implemented individually with relatively a few lines of code.

First, you’ll need to launch SQL Server Management Studio and log into your DiscountASP.NET database to create a stored procedure.  Click on the New Query button and copy the following code into the window:

CREATE PROCEDURE [dbo].[IsDatabaseAlmostFull]
AS
BEGIN
  DECLARE @CurrentSizeMB int
  DECLARE @MaxSizeMB int
  DECLARE @PercentageFull int
  SET NOCOUNT ON
  SET @PercentageFull = 0.90 /* Change this value */
  SELECT @CurrentSizeMB = size*8/1024,
         @MaxSizeMB = max_size*8/1024
  FROM sys.database_files
  WHERE [file_id] = 1
  IF @CurrentSizeMB >= (@MaxSizeMB * @PercentageFull)
    RETURN 1 /* True */
  ELSE
    RETURN 0 /* False */
END

The only value you need to change is on line 08.  The @PercentageFull variable determines at what percent the database fills up that you will get notified.

In the above example, you will get an email notification when your database reaches 90% full.

Hit F5 to execute the T-SQL code.

You can go ahead and close SQL Server Management Studio and launch Visual Studio.

Then go to File -> New -> Website…  I’m more of a C# programmer, so under Templates, I’ll select Visual C# and choose ASP.NET Empty Web Site.  You can also name the folder if you’d like next to Web location.

Click the OK button to continue.

On the right hand side under the Solution Explorer window, right click the folder name and select Add -> Add New Item…  Select Visual C#, Web Form, give the file a name if you’d like with a .aspx extension and make sure the option Place code in separate file is checked.

Click on the Add button.

Under the Solution Explorer window, expand the file name with a .aspx extension to reveal the code-behind file which should end with a .aspx.cs extension.

Double click it to open the file.

Paste the following code in the code-behind file replacing only the connection string and mail settings (lines 14 – 30).  Select Build -> Build Solution to compile your code and save your settings.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Net.Mail;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
      String connectionString = @"Data Source=tcp:sql2k###.discountasp.net;Initial Catalog=SQL******_######_databasename;User ID=SQL******_#######_databasename_user;Password=*******;";

#region Mail Settings
// Settings used to authenticate and construct email message
// Hostname of your SMTP server
String smtpHostname = @"smtp.YourHostedDomainName.com";
// Set to 25 or 587 depending upon your email provider's instruction
int portNumber = 25;
// From address and account used to provide SMTP authentication
String fromAddress = @"[email protected]";
// Password for email account used to provide SMTP authentication
String emailPassword = @"*******";
// Address of email account that will receive the notification
String toAddress = @"[email protected]";
// Name of your database used to construct subject and body of email
String dbName = @"SQL******_######_databasename";
#endregion

 SqlConnection dbConnection = new SqlConnection();
 dbConnection.ConnectionString = connectionString;
 dbConnection.Open();

 SqlCommand command = new SqlCommand();
 command.CommandText = @"dbo.IsDatabaseAlmostFull";
 command.CommandType = CommandType.StoredProcedure;
 command.Connection = dbConnection;

 SqlParameter returnValue = command.Parameters.Add("RetVal", SqlDbType.Int);
 returnValue.Direction = ParameterDirection.ReturnValue;

 command.ExecuteNonQuery();

 int isFull = (int)returnValue.Value;

 dbConnection.Close();

 if (isFull == 1)
 {
 MailMessage confirmationMail = new MailMessage();
 confirmationMail.From = new MailAddress(fromAddress);
 confirmationMail.To.Add(toAddress);
 confirmationMail.Subject = @"Warning regarding " + dbName;
 confirmationMail.Body = @"Warning.  Database " + dbName + @" is nearing max capacity.";

 SmtpClient smtp = new SmtpClient(smtpHostname, portNumber);
 NetworkCredential credentials = new NetworkCredential(fromAddress, emailPassword);
 smtp.Credentials = credentials;
 smtp.Send(confirmationMail);
 }
 }
}

Now upload your files to your hosting account using an FTP client like FileZilla.

Then launch the Scheduled Task Manager in the DiscountASP.NET Control Panel and setup a scheduled task for every 15 minutes that will invoke the code.

That’s it.

You will now receive an email notification whenever your database reaches or exceeds the percentage value you set in the stored procedure.

For those of you who know what you’re doing and are lazy like me, you can download the code files here and just adjust the settings I’ve mentioned above using any text editor.  I’ve included the .aspx, .aspx.cs, and .sql file for your convenience.