SQL Server File Growth Email Notification

05 Nov
November 5, 2012

Detecting and reporting when a SQL Server database data or log file experienced a growth operation can be very useful for many reasons, such as:

  • Analyzing and predicting disk space utilization on the server
  • Detecting optimum values to setup for database growth
  • Detecting any long running transactions that causes growth in log or data files.

In this post I present a simple method for querying information regarding database growth activity within a SQL Server instance, as well as setting up a simple email alert that gets triggered based on a configured growth threshold.

All SQL Server database file and log growth operations are logged in the default SQL Server trace file (log.trc), which can be accessed using T-SQL through the handy ::fn_trace_gettable function that returns a table of the information available in the default trace. It is important to note that Microsoft does recommend not using the ::fn_trace_gettable functions and instead opt in for the new Extended Events method of capturing trace information.

CREATE PROC [dbo].[usp_snapshot_DBGrowth]
    @ReportPeriod INT = -60
AS
BEGIN
   
DECLARE @PATH NVARCHAR(1000)

SELECT @PATH = SUBSTRING(PATH, 1, Len(PATH) - Charindex(CHAR(92), Reverse(PATH))) +
                      '\log.trc'
FROM   sys.traces
WHERE  id = 1

SELECT
    databasename,
    CONVERT(DATE,starttime) AS DateStamp,
    e.name,COUNT(*) AS GrowthCount,
    loginname
FROM ::fn_trace_gettable(@PATH,0)
    INNER JOIN sys.trace_events e ON eventclass = trace_event_id
    INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
WHERE
cat.category_id = 2 AND --database category
        e.trace_event_id IN (92,93) AND --db file growth
        starttime >= DATEADD(DAY,@ReportPeriod,GETUTCDATE())
GROUP BY
    databasename,CONVERT(DATE,starttime),e.name,loginname
ORDER BY databasename,CONVERT(DATE,starttime) DESC

END

The table returned by the ::fn_trace_gettable function is joined with two dimension tables (sys.trace_events & sys.trace_categories) in order to classify the trace and limit it to database file growth events

I have also written an XML version of this stored procedure that returns an HTML table, the result of this Scalar Function can be embedded directly into an HTML enabled email in a readable format.

ALTER FUNCTION [dbo].[usf_snapshot_DBGrowth_XML]
(
    @ReportPeriod INT = -60
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
   
    DECLARE @RESULT NVARCHAR(MAX)

    DECLARE @xmlHeaderdata XML
    SELECT @xmlHeaderdata =
    (
    SELECT
      (SELECT 'database_name' AS th FOR xml path(''), TYPE),
      (SELECT 'datestamp'   AS th FOR xml path(''), TYPE),
      (SELECT 'name'  AS th FOR xml path(''), TYPE),
      (SELECT 'growth_count'  AS th FOR xml path(''), TYPE),
      (SELECT 'login_name'  AS th FOR xml path(''), TYPE)
    FOR xml path('tr'), TYPE
    )
   
    DECLARE @PATH NVARCHAR(1000)
    SELECT @PATH = SUBSTRING(PATH, 1, Len(PATH) - Charindex(CHAR(92), Reverse(PATH))) +
                      '\log.trc'
    FROM   sys.traces
    WHERE  id = 1

    DECLARE @xmldata XML   
    SELECT @xmldata =
    (      
    SELECT
      (SELECT databasename     AS 'td' FOR xml path(''), TYPE),
      (SELECT DateStamp           AS 'td' FOR xml path(''), TYPE),
      (SELECT name           AS 'td' FOR xml path(''), TYPE),
      (SELECT GrowthCount           AS 'td' FOR xml path(''), TYPE),
      (SELECT loginname           AS 'td' FOR xml path(''), TYPE)
    FROM (SELECT
        databasename,
        CONVERT(DATE,starttime) AS DateStamp,
        e.name,
        COUNT(*) AS GrowthCount,
        REPLACE(CAST(loginname COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR(200)), CHAR(0), ' ') AS loginname
    FROM ::fn_trace_gettable(@PATH,0)
        INNER JOIN sys.trace_events e ON eventclass = trace_event_id
        INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
    WHERE
    cat.category_id = 2 AND --database category
            e.trace_event_id IN (92,93) AND --db file growth
            starttime >= DATEADD(DAY,@ReportPeriod,GETUTCDATE())
    GROUP BY
        databasename,CONVERT(DATE,starttime),e.name,loginname) tmpCTE
    ORDER BY DateStamp DESC
    FOR xml path('tr'), TYPE
    )

    SELECT @RESULT = '<table>'+CONVERT(NVARCHAR(MAX),@XmlHeaderdata) +  CONVERT(NVARCHAR(MAX),@xmldata) + '</table>'

    RETURN @RESULT

END

Now that we have the a method of identifying database growth, all we have to do to create a notification system around SQL Server growth events is wrap up everything in a Stored Procedure that can be called periodically from SQL Server Job Agent, each time the procedure runs it will check to see if the database experienced any file growth above the preset threshold value, if so an alert is sent with a growth information for the last 60 days for analysis.

-- TRACKING TABLE
CREATE TABLE [dbo].[mon_TrackAlerts](
    [DateStamp] [datetime] NOT NULL,
    [ProcedureName] [VARCHAR](100) NOT NULL,
 CONSTRAINT [PK_mon_TrackAlerts] PRIMARY KEY CLUSTERED
(
    [DateStamp] ASC,
    [ProcedureName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER PROC [dbo].[usp_mon_alert_DBFileGrowth]
    @GrowthCountTrigger INT = 1,
    @AlertPeriod INT = -1

AS
BEGIN

SET NOCOUNT ON;

DECLARE @PATH NVARCHAR(1000)
SELECT @PATH = SUBSTRING(PATH, 1, Len(PATH) - Charindex(CHAR(92), Reverse(PATH))) +
                      '\log.trc'
FROM   sys.traces
WHERE  id = 1

IF EXISTS(SELECT databasename,e.name,COUNT(*) AS GrowthCount
FROM ::fn_trace_gettable(@path,0)
    INNER JOIN sys.trace_events e ON eventclass = trace_event_id
    INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
WHERE
cat.category_id = 2 AND --database category
      e.trace_event_id IN (92,93) AND --db file growth
      starttime >= DATEADD(DAY,@AlertPeriod,GETDATE())
GROUP BY
    databasename,e.name
HAVING COUNT(*) > @GrowthCountTrigger)

BEGIN
   
    IF NOT EXISTS (SELECT * FROM dbo.[mon_TrackAlerts] WHERE DateStamp >= DATEADD(HOUR,-6,GETUTCDATE()) AND ProcedureName = OBJECT_NAME(@@PROCID))
    BEGIN
        DECLARE @tableVar NVARCHAR(MAX)

        SELECT @tableVar = dbo.usf_snapshot_DBGrowth_XML(DEFAULT)
   
        SET @tableVar = '<p>Attached is the analysis of the file growth on this instance</p><br/>' + @tableVar

        EXEC msdb..sp_send_dbmail
        @recipients='Diagnostics@Company.com',
        @body_format = 'HTML',
        @subject='Database file growth threshold reached',
        @body= @tableVar

       
        INSERT INTO dbo.[mon_TrackAlerts] (DateStamp,ProcedureName) VALUES (GETUTCDATE(),OBJECT_NAME(@@PROCID))

    END

END

END

I have also built a tracking table called mon_TrackAlerts that limits alerts to every 6 hours so you don’t get bombarded with emails every time the Job Agent runs, you can choose not to implement this though.

It might also be helpful to attach additional information to this, such as database file sizes, currently available disk space or even currently running queries.

I initially didn’t think this information is worthy of tracking, but after setting this up on every server in the federation I manage and started receiving all the alerts, its interesting to see how much behind the scenes stuff going on that could explain any abnormal behavior your instances are experiencing.

SQL Server File Growth Email Notification rated 4 out of 5 by 1 readers

SQL Server File Growth Email Notification , 4.0 out of 5 based on 1 ratings
          0 votes
Tags: , , ,
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>