-1

I would like to set up an alert when the disk space used by a table reaches a certain size.

I am aware we can set up an alert for file group\log file\database reaching a certain threshold. It would be interesting to know if we can do the same for a table.

  • 8
    There are many scripts online to track table size and sp_send_dbmail is well documented. What did you try? – SqlWorldWide Jun 27 '17 at 18:52
  • Haven't tried anything yet but I guess need a start, I could use the trick – Rak Jun 27 '17 at 18:59
  • First is to find a query that gives you the information you want to interrogate. Then build something that evaluates if the table size is greater than your threshold. If that evaluates to true, have it send an email. – Kris Gruttemeyer Jun 27 '17 at 19:05
1

As Kris mentioned, you could create a stored procedure and run it as a job

Here is a sample script that will accept Table name and Threshold(in KB) and send an email if table size exceeds its threshold

  CREATE PROCEDURE proc_CheckTableSize @TableName varchar(30),@ThresholdInKb bigint
AS
BEGIN
DECLARE @TableSize bigint
SET @TableSize = (
SELECT 

    SUM(a.total_pages) * 8 AS TotalSpaceKB


FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
    AND t.NAME= @TableName
GROUP BY 
    t.Name, s.Name, p.Rows
)

IF @TableSize > @ThresholdInKb
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients = 'Email@email.com',
@subject = 'Table exceeded threshold'
END

Hope it helps

Not the answer you're looking for? Browse other questions tagged or ask your own question.