Execute the following
T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to create a low disk space monitoring stored procedure. The sproc can be scheduled with SQL Server Agent for periodic execution.
-- Create stored procedure to monitor free disk space
USE MASTER;
GO
CREATE PROC sprocMonitorFreeDisk
AS
BEGIN
CREATE TABLE #FreeHardDiskSpace (
ID INT IDENTITY ( 1 , 1 ),
Drive CHAR(1),
FreeMB INT,
Threshold INT)
INSERT INTO #FreeHardDiskSpace
(Drive,
FreeMB)
EXEC MASTER.dbo.xp_fixeddrives
UPDATE #FreeHardDiskSpace
SET Threshold = 1024
UPDATE #FreeHardDiskSpace
SET Threshold = 800
WHERE Drive = 'C'
UPDATE #FreeHardDiskSpace
SET Threshold = 2500
WHERE Drive = 'F'
DECLARE @i INT,
@Drives INT,
@Drive CHAR(1),
@FreeMB INT,
@Threshold INT,
@Parm1 CHAR(50),
@Parm2 CHAR(50)
SELECT @Drives = count(* )
FROM #FreeHardDiskSpace
SET @i = 1
WHILE (@i <= @Drives)
BEGIN
SELECT @FreeMB = FreeMB,
@Threshold = Threshold,
@Drive = Drive
FROM #FreeHardDiskSpace
WHERE ID = @i
SET @Parm1 = 'Free Space Alert on ' + @Drive + ' Drive'
SET @Parm2 = 'Free space on ' + @Drive + ' Drive is below ' +
convert(VARCHAR,@Threshold) + ' MB'
IF @FreeMB < @Threshold
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dina.smith@abc.com' ,
@subject = @Parm1 ,
@body = @Parm2
SET @i = @i + 1
END
END
GO
-- execute stored procedure
EXEC sprocMonitorFreeDisk
-- Free disk space check system procedure
EXEC MASTER.dbo.xp_fixeddrives
/*
drive MB free
C 447248
F 261628
*/
------------
|
