Introduction

As DBAs, we are often placed in situations where we have to manage multiple environments with very limited resources. Managing these resources can be  a very time consuming process if nothing is automated, sometimes to the detriment of our other responsibilities. I've read a number of excellent articles recently extolling the virtues of PowerShell and other scripting languages, but sometimes, what we need is already right at our finger tips.

In this article I will show you how to create standard alerts for log space used, using the standard SQL Alerts readily available in SSMS, and custom alerts for Data file usage, using custom alert messages, stored procedures and SQL Server Agent Jobs. Once these alerts are in place, you no longer have to go through sleepless nights, waiting for your phone to start buzzing.

The examples below were carried out on SQL 2008 R2, but the same principles can be applied to earlier versions of SQL Server.

Pre-requisites

These alerts require that Database Mail is enabled and configured, so that notifications can reach you safely, but that is beyond the scope of this article. If you do not have Database Mail set up, this article should help you complete the task. You must also have an operator configured for the alerts to be sent to.

A Quick overview of SQL Server Alerts

I am not going to go into an in-depth overview of SQL Alerts, as there are plenty of articles available already and the topic is well documented.

However, In a nutshell, SQL Server Alerts are a means of recording  events / problems / specified conditions on a server and reacting in a pre-determined way. They allow an administrator to decide what events they need to be notified about, in any of the following 3 categories:

• SQL Server event alert, using pre defined or user created SQL event messages.
• SQL Server performance condition alert, using standard SQL Server Performance Counters.
• WMI event alert, using Windows WMI counters (in SQL 2005 onwards).

Using these categories an administrator can effectively manage the events that occur in their environment and handle them accordingly by either running custom Agent jobs, or simply notifying administrators via Email, Net-Send or Pager. Additionally, you can choose to have all Events entered into the Windows Event Log.

Alerts for Log Space

As the title of this article suggests, we are interested in managing the size of our data files, and more specifically the amount of free space available to them. I will start with the easiest first, the Transaction Log space alert. SQL Server has already provided everything you need for managing Transaction Log alerts, and everything can be configured in a few minutes.

Alerts are all managed from the SQL Server Agent, so go into SSMS and simply right click Alerts and select 'New Alert'. You will be prompted with the New Alert Dialog that consists of 3 screens of options (General, Response, Options).  

General

This dialog screen allows you to select what sort of Alert you are interested in and define your parameters. Configure your alert as below:

  The following parameters can be set.

• Name: Your choice of wording, but it should be something future DBAs and other administrators will understand in case of your absence.
• Type: SQL Server performance condition alert.
• Object: SQLServer:Databases
• Counter: Percent Log Used
• Instance: AdventureWorks
• Alert if counter: rises above
• Value: 80

The final 2 options (Alert if counter and Value)  are system dependent, you can configure these any way you want to meet the specific needs of your server and database. This will be my initial alert to notify me that there may be a problem on the system, but still give me time to investigate before the system comes crashing down.  

Response

For the purposes of our example, we are creating a simple alert that will send the local operator a notification via email when the conditions defined in the General dialog are met. Choose your operator from the list, and select E-mail as your choice of delivery. 

Options

This final screen allows you to configure the final notification email to your desired tastes. It is recommended that you select the option to 'Include alert error text in: Email'. This will give a clear description of why you are receiving the alert, when the event specified occurs and an Email is sent.

In our case, the following message will be included in our Email notification

I have chosen to add an additional message into the final notification. This will give me and any future administrators any recommended steps or advice, in dealing with this particular error.

The 'Delay between responses' option is again a matter of choice. In this case, as I have set the Log Full Percent option to 80%, I have left 4 hours between responses. You can configure additional alerts for 90% and 95% full, or whatever levels are required for your environment, which will have a delay of 30 minutes and 5 minutes respectively for me. This separation of alerts and notifications allows me to handle the escalation of the responses and act appropriately.

Click OK.

Once you have created your first alert, you can use it as a template for additional alerts by Right Clicking on your new Alert and scripting it,  this will allow you to deploy these alerts to multiple databases and systems using scripting or manually. The script below is generated after creating the alert above:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert 
 @name=N'AdventureWorks Log Full',
 @message_id=0,
 @severity=0,
 @enabled=1,
 @delay_between_responses=14400,
 @include_event_description_in=1,
 @notification_message=N'Please check any issues on the server preventing Transaction Log from reclaiming used space.',
 @category_name=N'[Uncategorized]',
 @performance_condition=N'SQLServer:Databases|Percent Log Used|AdventureWorks|>|80',
 @job_id=N'00000000-0000-0000-0000-000000000000'
GO

As you can see, handling Log growth using SQL Alerts is a relatively easy task, and one which no DBA should do without. 

Alerts for Data File Free Space

Unfortunately, there is no such standard configurable alert for monitoring the free space in your data files, these need to be set up and configured manually. There are probably a number of ways this can be handled, but I chose the following steps:

1. Add new alert type.
2. Create alert based on new error number.
3. Create new procedure to monitor total space.
4. Create job to call procedure on a regular basis .

We will discuss each of these below.

1. Add new alert type

SQL Server allows you to manage your own custom events via the sp_addmessage stored procedure (full documentation in BOL).  For this example, the arguments we are interested in are:

@msgnum - This is the event ID we will use later on to raise our error and record it against this event. User-defined error messages can be an integer between 50,001 and 2,147,483,647. 

@severity - The severity allows us to manage how we want this custom alert classified in the Event log. In this case we are simply alerting the administrator to Data files(s) reaching a particular non-catastrophic size, so I selected a Severity Level in the 0-9 range ("Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9").

@msgtext - This is the error description that will be entered into the Event Log (if specified) and in the email notification. I am using a couple of parameters that will be specified when I raise the error in our Stored Procedure. In this case I will be sending in one numeric Parameter (%d) which represents the Percentage space used, and one string parameter (%s) which represents the database the event occurs in.

The full stored procedure call will look like this:

EXEC sp_addmessage 
  @msgnum=75001, 
  @severity=1,         
  @msgtext=N'Data files are %d percent full in database %s.'

2. Create Alert based on new error number

These steps roughly mirror what we did setting up the Alerts for our Logs, but this time, we will be using our custom error number as created above.

Begin by Right Clicking Alerts under SQL Server Agent and select 'New Alert'. When the New Alert dialog appears, use the options below to configure your alert.

The General Tab

Here is the general tab, and the options are described below.

Name: Again, choose an appropriate Name.

Type: This time we are choosing 'SQL Server event alert' as we are using a custom Server Event to trigger our Alert.

Database name: You do not have to configure this alert for one database this time. Selecting <all databases> allows the stored procedure below to fire the same event globally across your server. If you require different levels / database, you can amend the stored procedure accordingly.Error number: This is where we specify the number we defined when creating our custom error in step 1.

Response

This is the same as setting your log Response, you can define any chosen operator and the method of delivery.

Options

Again, this tab can be configured at your own discretion, dependent on how much information you require in your notification email.

Click Ok.

This alert is now live and monitoring your system for the SQL Event 75001, so now we have to watch for, and trigger that event if any database meet its conditions, we do this by creating a custom procedure and Agent Job.

3. Create new procedure to monitor total space.

This is the code for the stored procedure.

USE <DATABASENAME>
GO
CREATE PROCEDURE dbo.usp_DBA_checkfordatabasefreespace
AS
DECLARE @loop                        INT;
DECLARE @count                        INT;
DECLARE @db_name                VARCHAR(25);
DECLARE @exec_string        VARCHAR(500);
DECLARE @threshold                INT;
DECLARE @id                                INT;
DECLARE @free_space                INT
SET @threshold = 80;-- this is the value we want to set our free space threshold to
--create a table for holding all database free space
CREATE TABLE #space_used
(
id                                        INT        IDENTITY(1,1),
database_name                VARCHAR(25),
free_space_percent        INT
)
--get the names of all user databases
SELECT
IDENTITY(INT,1,1) as ID,
name
INTO
#temp
FROM
sys.databases
WHERE
database_id > 4;
SET @loop = 1;
SELECT @count = MAX(ID) FROM #temp;
--loop through each database and get the data file free space        
WHILE @loop <= @count
 BEGIN
--get our working db
SELECT
@db_name = name
FROM
#temp
WHERE
ID = @loop;
--build the string for execution
SET @exec_string = '
USE ' + @db_name + ';
SELECT 
''' + @db_name + ''' as database_name,
AVG(100 * (CAST (((f.size/128.0 - CAST(FILEPROPERTY(f.name,  ''spaceused'') AS int)/128.0)/(f.size/128.0)) AS decimal(4,2)))) AS free_space_percent
FROM
sys.sysfiles f
WHERE
groupid != 0;-- data files'
--pull our space data back and insert into our holding table
INSERT #space_used
EXECUTE (@exec_string);
--next please                
SET @loop = @loop + 1
END
--clean up our un-needed table
drop table #temp;
--remove any entries that do not meet our threshold
DELETE FROM #space_used WHERE free_space_percent < @threshold;

--loop through all problem databases and raise error
WHILE EXISTS
(
SELECT
NULL
FROM
#space_used
)
BEGIN

--work through each database
SELECT TOP 1
@id = id,
@db_name = database_name,
@free_space = free_space_percent
FROM
#space_used;
--if we have databases that have reached our threshold, then we raise the alert
RAISERROR  (75001, 10,1,@free_space, @db_name) WITH LOG;
--remove the processed entry
DELETE FROM #space_used WHERE id = @id;
END
--clean up our un-needed table
drop table #space_used;

This procedure could be written a number of ways, and I leave it to your discretion as to how you pull the file data and how much of it is used, but the above example will get the job done. It follows these steps each time it runs:

• Store all user database data files in a temp table, along with their current free space using sys.sysfile data.
• Remove any entries from the temp table that do not meet our threshold. (Again, I chose 80% as an arbitrary value and defined it at the top of the stored procedure. You can choose whatever value you require. You can even set up a library of these stored procedure with different thresholds, or pass the threshold in as a parameter in the job below. In this case I will keep it simple for demonstrative purposes).
• For all remaining entries in our table, raise the appropriate error.

The important part of the stored procedure is the line:

RAISERROR  (75001, 10,1,@free_space, @db_name) WITH LOG;

This is what actually registers our custom Event with the SQL Server Agent, and allows it to handle the response as defined in our custom Alert. The @free_space and @db_name parameters passed into the RAISERROR represent the %d and %s parameters defined in our custom Alert Type.

I have chosen to aggregate all of my data files (if there are multiple), to get an overall full size for the entire database. You could choose to set up an alert for each data file on your system instead.

4. Create job to call procedure on a regular basis.

The job  we define below  will call our stored procedure on a regular basis, for this example I set the duration for 30 minutes, but based on your own experience with your environment you can define this how ever you want.

Right click jobs under SQL Server Agent and select 'New Job…', follow the steps below to set your job up and running.

Name: Call you job something meaningful and with a transparent name.

Owner: set to whoever you have managing your jobs.

Category: I set this as Database Maintenance job, as it is one of the defaults for this example.

Description: Put in a meaningful description! You would be amazed at how much time can be saved in the future if your jobs are labeled correctly. This way you, or future administrators won't have to decipher the jobs purpose for themselves.

Set the job to have one basic step, A call to your Stored Procedure.

Make sure your step follows the appropriate actions when it is run.

As mentioned above, set a schedule that is appropriate to your environment.

If you so choose, have the job send you an alert when it fails.

As we are deliberately raising an error in our stored procedure, the job will fail if any of the databases have data files that are over the desired threshold, so if you do set an alert on job failure, you will get 2 notifications on the system.

Conclusion

Making sure that you have control of the growth of your SQL databases, is one of the most important tasks to a committed DBA. There are few things worse than having proactive System Administrators or diligent business managers come up to you out of the blue and point out to you that the database is almost full and ask you what you plan to do about it. Even worse, having users call you in the middle of the night as you have a database that is unable to grow anymore and is refusing to do anything until it does.

These simple steps will allow you to take control of your environments and effectively manage growth throughout all of your systems.

References

http://msdn.microsoft.com/en-us/library/ms186747.aspx  - sp_addoperator

http://msdn.microsoft.com/en-us/library/ms178649(SQL.105).aspx - sp_addmessage

http://msdn.microsoft.com/en-us/library/ms164086(SQL.105).aspx - Database Engine Error Severities

http://www.sqlservercentral.com/articles/Stairway+Series/72455/ - Stairway to SQL Server Agent - Level 4: Configuring Database Mail

http://geekswithblogs.net/mnf/archive/2009/07/04/custom-alert-on-the-sql-database.aspx  - Custom Alerts on the SQL Database