Ray HuangHere’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.

spIsDatabaseAlmostFull

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.

EmptyWebSite

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.

WebForm

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.

SolutionExplorer

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 = @"postmaster@YourHostedDomainName.com";
// Password for email account used to provide SMTP authentication
String emailPassword = @"*******";
// Address of email account that will receive the notification
String toAddress = @"user@YourHostedDomainName.com";
// 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.

UploadFiles

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.

ScheduledTask

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.

 

5 Responses to “Microsoft SQL Server Tips Series: Automatic database quota warning emails”

  1. Anthony says:

    Hi Ray,

    Another option that you haven’t mentioned is to simply use the API which can give you, among other things, your various quotas (including SQL ones).

    Regarding the “warning process”, what I don’t understand is that you say that “we don’t have one in place for databases because it is difficult to automate the process on a grand scale.”. How difficult is it for a technical company like DiscountASP to check the size of each database on each server? After all, according to your own solution, it’s just a matter of executing a stored procedure in each database on a daily basis and then email the customers if necessary.

    You also say “we would have to make sure the email addresses we have on file are current and active, and that’s not always the case” but just in the paragraph before you write “we actually do already have a process in place to warn you if you exceed your monthly bandwidth”. The problem, surely, is the same: if you can warn us (by email) if we exceed our monthly bandwidth, how come you cannot warn us (by email) if we exceed our SQL Usage? If the email address you have on file is not valid then the problem is the same in both cases.

    Finally, you are a business so your goal is to make money: an email to each customer approaching his limit (regardless of what it is: bandwidth, sql, etc) would not only be appreciated by the customer but also would also allow you to include a link to the email so that they can buy more space/bandwidth/etc. As a business, you should aim to make the lives of your customers easier especially if it allows you to make more money at the same time.

    Image these 2 scenarios:
    1- you wake up one day and you realise your website is down. You check and you realise, after a while, it’s because you have reached some of your quotas. The customer service tells you that it was your responsibility to monitor it. Anyway you pay for more space/bandwidth/etc but are you happy? Probably not.
    -2 you wake up one day and noticed that your web hosting company has emailed you to warn you that you’re about to reach your quota in the next few days. There is a link in the email to the control panel to buy more space/bandwidth/etc You pay for more space/bandwidth/etc Are you happy? Yes because your website would have been done a few days later if your hosting company had not warned you.

    Anthony.

  2. Amadou says:

    Since many days i’m looking for hosting solutiuons (.net mvc and sql server 2012). My requirement is about using SPs to send notification from database, with database mail (sp_send_dbmail). Please can i have the same feature with our hosting.

    • mjp says:

      Hi Amadou. I’m afraid we don’t allow sending mail from the database servers. Over at Everleap if you have Managed SQL service (http://www.everleap.com/cloud-hosting/managed-sql/) we can do customizations that we can’t do on shared database servers. You might want to take a look at that and see if it could work for you.

      • Amadou says:

        Thanks for your reply. This can be a good option to have managed sql hosting solution. Is it possible to use my own sql server entreprise license ?

        • mjp says:

          I remember discussing that, but honestly I don’t remember what the outcome was (Windows Azure Pack doesn’t work the way traditional servers work, so things that might seem trivial on a traditional server may have some inherent complications on WAP). If you email info@everleap.com they can tell you whether we can use the license.

Leave a Reply

iBlog by PageLines