SQL: Set Up Email Alert When Partition Size Reaches Threshold

-- =============================================
-- Query to determine if a partition's size may
-- be reaching a critical maximum
-- Note: Currently uses SP_SEND_DBMAIL instead of XP_SENDMAIL
-- =============================================

DECLARE @DbName VARCHAR(25)
DECLARE @sql VARCHAR(100)
DECLARE @ThresholdCountMsg INT
DECLARE @Subject VARCHAR(100)

SET @DbName = DB_NAME(); --get name of current database to use in email alert

SET @Subject = 'Partitioning Threshold Alert On ' + @DbName + '!'

SET @ThresholdCountMsg = 14000000;  -- set partition row count

IF OBJECT_ID('tempdb..##yourPartitionSizeCheck') IS NOT NULL
BEGIN
DROP TABLE ##yourPartitionSizeCheck
END;
 

--use global temp table for email alert

CREATE TABLE ##yourPartitionSizeCheck
(
table_name varchar(20),
partition_number int,
row_count int
);

 

INSERT INTO ##yourPartitionSizeCheck

SELECT b.name AS 'table_name',
partition_number,
a.[rows] AS 'row_count'
FROM sys.partitions AS a INNER JOIN
sys.objects AS b ON a.object_id = b.object_id
INNER JOIN sys.schemas AS c On c.schema_id = b.schema_id              
WHERE LOWER(b.name) = 'yourPartitionedTableName'     
and a.index_id < 2
and a.[rows] > @ThresholdCountMsg; 

IF @@ROWCOUNT > 0

EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'YourSqlmail',
@recipients = 'your.email@yourcompany.com',
@subject = @Subject,
@body = 'The following partitions may be approaching performance degradation: ',   

@execute_query_database = @DbName,       

@query = 'SELECT table_name, partition_number, row_count FROM ##yourPartitionSizeCheck';