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 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
-- 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
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
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)', 'varchar(128)') , Duration = message_body.value('(/EVENT_INSTANCE/Duration)', 'varchar(128)') , ServerName = message_body.value('(/EVENT_INSTANCE/ServerName)', 'varchar(128)') , PostTime = CAST(message_body.value('(/EVENT_INSTANCE/PostTime)', 'datetime') AS VARCHAR) , DatabaseName = message_body.value('(/EVENT_INSTANCE/DatabaseName)', 'varchar(128)') , GrowthPages = message_body.value('(/EVENT_INSTANCE/IntegerData)', 'int') FROM ( SELECT CAST(message_body AS XML) AS message_body FROM [AutogrowEventQueue] ) AS Tab;
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)', 'varchar(128)') , @Duration = @message_body.value('(/EVENT_INSTANCE/Duration)', 'varchar(128)') , @ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)', 'varchar(128)') , @PostTime = CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)', 'datetime') AS VARCHAR) , @DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)', 'varchar(128)') , @GrowthPages = @message_body.value('(/EVENT_INSTANCE/IntegerData)', '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
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
This article provides a very simple way to enable near real time notifications any time a database file auto grows in the server!