DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to monitor the disk drives for low free space?

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 = '[email protected]' ,

            @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

*/

 

------------

 

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE