USE [MonitorDB] GO CREATE PROC [dbo].[Mail_DB_Sizediffs] @recipients VARCHAR(MAX) = 'someone@company.com' , @profile_name SYSNAME = '' AS /* SP to send mail alerts when database devices change in size, have been added or have been deleted. DB sizes are recorded by 'Record_DB_Device_Usage.ps1', which records data and log size & usage of all databases per server. */ SET NOCOUNT ON -- Create temporary table IF OBJECT_ID (N'tempdb.dbo.DB_Dev_Sizediff', N'U') IS NULL CREATE TABLE [tempdb].[dbo].[DB_Dev_Sizediff] ( [Server] [sysname] , [Database] [sysname] , [Device] [sysname] , [MB_Current] [int] , [MB_Previous] [int] , [MB_Growth] [int] , [Current_Date] [datetime] , [Previous_Date] [datetime] ) ELSE TRUNCATE TABLE tempdb.dbo.DB_Dev_Sizediff ; -- Create holding table IF OBJECT_ID (N'dbo.dbspace', N'U') IS NULL CREATE TABLE [dbo].[dbspace] ( [runid] [int] , [date] [smalldatetime] , [servername] [sysname] , [dbname] [varchar](128) , [devname] [sysname] , [type] [char](4) , [filegroup] [int] , [fileid] [int] , [mb_alloc] [int] , [mb_used] [int] ) ; -- Record database device usage as logged by the last run of 'Record_DB_Device_Usage' -- (highest value of [runid]) and the run immediately preceding it. WITH Current_Run AS ( SELECT d.runid , d.servername , d.dbname , d.devname , d.mb_alloc , d.mb_used , d.[date] FROM dbo.dbspace d WHERE d.runid = (SELECT MAX(runid) FROM dbo.dbspace) ) , Previous_Run AS ( SELECT d.runid , d.servername , d.dbname , d.devname , d.mb_alloc , d.mb_used , d.[date] FROM dbo.dbspace d WHERE d.runid = (SELECT MAX(runid) FROM dbo.dbspace WHERE runid < (SELECT MAX(runid) FROM dbo.dbspace)) ) INSERT INTO tempdb.dbo.DB_Dev_Sizediff SELECT CASE WHEN cr.servername IS NOT NULL THEN cr.servername ELSE pr.servername END AS 'Server' , CASE WHEN cr.dbname IS NOT NULL THEN cr.dbname ELSE pr.dbname END AS 'Database' , CASE WHEN cr.devname IS NOT NULL THEN cr.devname ELSE pr.devname END AS 'Device' , cr.mb_alloc , pr.mb_alloc , CASE WHEN cr.mb_alloc IS NOT NULL THEN cr.mb_alloc - ISNULL(pr.mb_alloc,0) ELSE 0 - pr.mb_alloc END AS 'MB_Growth' , cr.[date] , pr.[date] FROM Current_Run cr FULL OUTER JOIN Previous_Run pr ON cr.servername = pr.servername AND cr.dbname = pr.dbname AND cr.devname = pr.devname WHERE cr.mb_alloc != pr.mb_alloc OR cr.mb_alloc IS NULL OR pr.mb_alloc IS NULL -- If any results were recorded, send alert mail IF EXISTS (SELECT 1 FROM tempdb.dbo.DB_Dev_Sizediff) BEGIN DECLARE @subj VARCHAR(200) , @body NVARCHAR(MAX) , @xml NVARCHAR(MAX) SELECT @subj = 'Database device size changes' SET @body = '

(This mail was sent by the procedure ''' + DB_NAME() + '.' + OBJECT_SCHEMA_NAME (@@PROCID) + '.' + OBJECT_NAME(@@PROCID) + ''')

Listed below are are all database devices whose size changed recently.

Database Device Size Changes

' SET @xml = CAST( (SELECT LEFT(UPPER([Server]),20) AS 'td','' , LEFT([Database], 20) AS 'td','' , LEFT(Device, 25) AS 'td','' , LEFT([MB_Growth],8) AS 'td','' , ISNULL(CONVERT(CHAR(10),[MB_Current]),'-----') AS 'td','' , ISNULL(CONVERT(CHAR(10),[MB_Previous]),'-----') AS 'td','' , ISNULL(CONVERT(CHAR(16),[Current_Date],20),'--------------') AS 'td','' , ISNULL(CONVERT(CHAR(16),[Previous_Date],20),'--------------') AS 'td','' FROM [tempdb].[dbo].[DB_Dev_Sizediff] ORDER BY [Server], [Database] FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body = @body + @xml +'
Server Database Device MB Growth MB Current MB Previous Current Check Previous Check
' EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name , @recipients = @recipients , @subject = @subj , @body = @body , @body_format = 'HTML' END