SQL Server File Growth Email Notification
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.
@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.
(
@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.
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
Leave a Reply
Want to join the discussion?Feel free to contribute!