As discussed in my previous Getting Started with SQL Server Event Notifications article, SQL Server Event Notifications allow us to capture and handle events that occur inside the SQL Server database engine. Event Notifications execute for DDL Events and Trace Events and we can use to capture information about the event that fired, and automate the notification process. However, unlike DDL triggers and SQL Trace, which process synchronously, Event Notifications utilize Service Broker to asynchronously capture event information.

In this article, I'll explain how to automate email notifications whenever a transaction log or data file auto grows. My previously referenced Getting Started… article covers all the basics of using Event Notifications, so I won't go over that ground again here.

Creating the Event Notification Components

In my previous article, I showed how to create all of the required Event Notification components in a user database. If this is your requirement, please refer back to that article, as it requires some additional steps that I won't repeat here. For example, you'll need to use a certificate to sign the activation-stored procedure, and to enable Service Broker for the user database.

For simplicity, here, I am going to create the Event Notification components in the msdb database, as shown in Listing 1. This eliminates these additional steps, since the database mail stored procedures exist in msdb, and msdb is already broker-enabled (database mail uses Service Broker external activation through Query Notifications, to send email). Also, in my opinion, for basic event notifications in SQL Server, there isn't a significant security risk in creating these objects in msdb.

-- Using msdb prevents the need for certificate signing the 
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO
-- Drop the notification if it exists
IF EXISTS ( SELECT  *
            FROM    sys.server_event_notifications
            WHERE   name = N'CaptureAutogrowEvents' ) 
    BEGIN
        DROP EVENT NOTIFICATION CaptureAutogrowEvents ON SERVER;
    END
-- Drop the route if it exists
IF EXISTS ( SELECT  *
            FROM    sys.routes
            WHERE   name = N'AutogrowEventRoute' ) 
    BEGIN
        DROP ROUTE AutogrowEventRoute;
    END
-- Drop the service if it exists
IF EXISTS ( SELECT  *
            FROM    sys.services
            WHERE   name = N'AutogrowEventService' ) 
    BEGIN
        DROP SERVICE AutogrowEventService;
    END
-- Drop the queue if it exists
IF EXISTS ( SELECT  *
            FROM    sys.service_queues
            WHERE   name = N'AutogrowEventQueue' ) 
    BEGIN
        DROP QUEUE AutogrowEventQueue;
    END
--  Create a service broker queue to hold the events
CREATE QUEUE [AutogrowEventQueue]
WITH STATUS=ON;
GO
--  Create a service broker service receive the events
CREATE SERVICE [AutogrowEventService]
ON QUEUE [AutogrowEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
-- Create a service broker route to the service
CREATE ROUTE [AutogrowEventRoute]
WITH SERVICE_NAME = 'AutogrowEventService',
ADDRESS = 'LOCAL';
GO
-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureAutogrowEvents]
ON SERVER
WITH FAN_IN
FOR DATA_FILE_AUTO_GROW, LOG_FILE_AUTO_GROW
TO SERVICE 'AutogrowEventService', 'current database';
GO

Listing 1: Creating the Event Notification components in msdb

Testing Event Notification

With these objects created, we need to test out the basic functionality of the Event Notification to make certain we've configured our Service Broker components correctly and we are collecting events. To do this, we'll create a test database and populate a test table with enough data to make it trigger auto-growth of the data and log files, as shown in Listing 2.

USE [master];
GO
IF DB_ID('Test') IS NOT NULL 
    BEGIN
        ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        DROP DATABASE [Test];
    END
CREATE DATABASE [Test]
GO
BACKUP DATABASE [Test] 
TO DISK = N'c:\SQLskills\Backup\Test.bak'
WITH INIT;
GO
USE [Test];
GO
CREATE TABLE [Test]
    (
      RowID INT IDENTITY
                PRIMARY KEY ,
      datacol CHAR(4000) NOT NULL
                         DEFAULT ( '' )
    )
GO
INSERT  INTO [Test]
        DEFAULT VALUES;
GO 1000

Listing 2: Creating and populating the test table

We can now check our queue for events with the command shown in Listing 3.

USE [msdb];
GO
SELECT
  EventType = message_body.value('(/EVENT_INSTANCE/EventType)[1]',
                                       'varchar(128)') ,
  Duration = message_body.value('(/EVENT_INSTANCE/Duration)[1]',
                                'varchar(128)') ,
  ServerName = message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
                                  'varchar(128)') ,
  PostTime = CAST(message_body.value('(/EVENT_INSTANCE/PostTime)[1]',
                                     'datetime') AS VARCHAR) ,
  DatabaseName = message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]',
                                    'varchar(128)') ,
  GrowthPages = message_body.value('(/EVENT_INSTANCE/IntegerData)[1]',
                                   'int')
FROM    ( SELECT    CAST(message_body AS XML) AS message_body
          FROM      [AutogrowEventQueue]
        ) AS Tab;

Listing 3: Selecting events from the queue

You should see a number of auto-growth event notifications, similar to the following:

Creating and Testing the Activation Stored Procedure

Now we're sure that we're collecting events, all we have to do create the internal activation stored procedure that will process the events in the queue, and generate an email notification to the DBA staff, using database mail, as shown in Listing 4.

--  Create the Activation Stored Procedure to Process the Queue
IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[SQLskills_ProcessAutogrowEvents]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) 
    DROP PROCEDURE [dbo].[SQLskills_ProcessAutogrowEvents];
GO
CREATE PROCEDURE [dbo].[SQLskills_ProcessAutogrowEvents]
    WITH EXECUTE AS OWNER
AS 
    DECLARE @message_body XML;
    DECLARE @message_sequence_number INT;
    DECLARE @dialog UNIQUEIDENTIFIER;
    DECLARE @email_message NVARCHAR(MAX);
    WHILE ( 1 = 1 ) 
        BEGIN
            BEGIN TRANSACTION;
-- Receive the next available message FROM the queue
WAITFOR
   (
      RECEIVE TOP(1) -- just handle one message at a time
         @message_body=CAST(message_body AS XML)
         FROM dbo.AutogrowEventQueue
   ), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away
-- If we didn't get anything, bail out
            IF ( @@ROWCOUNT = 0 ) 
                BEGIN
                    ROLLBACK TRANSACTION;
                    BREAK;
                END 
            DECLARE @EventType VARCHAR(128);
            DECLARE @ServerName VARCHAR(128);
            DECLARE @PostTime VARCHAR(128);
            DECLARE @DatabaseName VARCHAR(128);
            DECLARE @Duration VARCHAR(128);
            DECLARE @GrowthPages INT;
            SELECT              @EventType =                 @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
                                     'varchar(128)') ,
              @Duration =                 @message_body.value('(/EVENT_INSTANCE/Duration)[1]',
                                     'varchar(128)') ,
              @ServerName =                 @message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
                                     'varchar(128)') ,
              @PostTime =                CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)[1]',
                                                         'datetime')                                                         AS VARCHAR) ,
              @DatabaseName =                 @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]',
                                     'varchar(128)') ,
              @GrowthPages =                 @message_body.value('(/EVENT_INSTANCE/IntegerData)[1]',
                                     'int');
-- Generate formatted email message
            SELECT  @email_message = 'The following autogrow event                                      occurred:'
                    + CHAR(10) + CAST('ServerName: ' AS CHAR(25))
                    + @ServerName + CHAR(10) + CAST('PostTime: '                                                     AS CHAR(25))
                    + @PostTime + CHAR(10)
                    + CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName
                    + CHAR(10) + CAST('Duration: ' AS CHAR(25))                    + @Duration
                    + CHAR(10) + CAST('GrowthSize_KB: ' AS CHAR(25))
                    + CAST(( @GrowthPages * 8 ) AS VARCHAR(20));
-- Send email using Database Mail
            EXEC msdb.dbo.sp_send_dbmail                @profile_name = 'DBA Notification Account',
                                          -- your defined email profile 
                @recipients = 'DBA@gmail.com', -- your email
                @subject = 'AutoGrow Event Notification',
                @body = @email_message;
--  Commit the transaction.  At any point before this, we could roll 
--  back. The received message would be back on the queue AND the 
--  response wouldn't be sent.
            COMMIT TRANSACTION;
        END
GO

Listing 4:

Next, we need to test the stored procedure by executing it to validate that the emails get generated properly for the existing events in the queue.

Enabling the Queue for automated activation

Having verified that the stored procedure executes correctly, and the emails get sent to the DBA staff, we can then enable our Queue for automated activation, so that the events get processed immediately and we get our notifications.

--  Alter the queue to use the activation procedure
ALTER QUEUE [AutogrowEventQueue]
   WITH STATUS=ON, 
      ACTIVATION 
         (STATUS=ON,
          PROCEDURE_NAME = [SQLskills_ProcessAutogrowEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);
GO

Listing 5: Setting the queue to use the activation stored procedure

Summary

This article provides a very simple way to enable near real time notifications any time a database file auto grows in the server!