Disk Free Space Check

USE [DB_Maint]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID(N'[dbo].[usp_Disk_Free_Space_Check]') IS NOT NULL
BEGIN
  DROP PROCEDURE [dbo].[usp_Disk_Free_Space_Check]
END
GO

CREATE PROC [dbo].[usp_Disk_Free_Space_Check] (@dbmail_profile sysname = NULL, @dbmail_recipient sysname = NULL, @deleteEntry int = 360)

AS
BEGIN
  SET NOCOUNT ON
  BEGIN TRY


    /*                       
                      Desc:1. Calcualates each drive's growth rate and report them as Alerts.
                            2. If free disk space <=30, it will throw alert.
    3. If free disk space <=20, it will throw alert.
    4. If free disk space <=10, it will throw WARNING!! alert.
    
                          RUNNING Instructions :=
      exec [usp_Disk_Free_Space_Check]
      @dbmail_profile= 'Operators', 
      @dbmail_recipient = 'youremail@domain.com' ,
      @deleteEntry = 360; -- No Of Days!
    */

/* 
    -----------------------------------------------------------
    Step 1: Create the table.
    
    USE [DB_Maint]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Capacity_DiskSpaceTracking](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Drive] varchar(10) NOT NULL,
    [ServerName] nvarchar(4000) NULL,
    [Free(MB)] varchar(1000) NULL,
    [Total(MB)] varchar(1000) NULL,
    [Free(%)] varchar(1000) NULL,
    [Date_Entered] datetime NULL,
    [login_sname] [sysname] NOT NULL DEFAULT (suser_sname())
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
     
     
    */
    -- xp_fixeddrives


    DECLARE @hr int
    DECLARE @fso int
    DECLARE @drive char(1)
    DECLARE @odrive int
    DECLARE @TotalSize varchar(20)
    DECLARE @MB bigint;

    SET @MB = 1048576 -- 1 GB

    CREATE TABLE #drives (
      drive char(1) PRIMARY KEY,
      FreeSpace int NULL,
      TotalSize int NULL
    )
    INSERT #drives (drive, FreeSpace)
    EXEC master.dbo.xp_fixeddrives
    EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
                           @fso OUT
    IF @hr  0
      EXEC sp_OAGetErrorInfo @fso
    DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR
    SELECT
      drive
    FROM #drives
    ORDER BY drive
    OPEN dcur
    FETCH NEXT FROM dcur INTO @drive
    WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC @hr = sp_OAMethod @fso,
                             'GetDrive',
                             @odrive OUT,
                             @drive
      IF @hr  0
        EXEC sp_OAGetErrorInfo @fso
      EXEC @hr = sp_OAGetProperty @odrive,
                                  'TotalSize',
                                  @TotalSize OUT
      IF @hr  0
        EXEC sp_OAGetErrorInfo @odrive

      UPDATE #drives
      SET TotalSize = @TotalSize / @MB
      WHERE drive = @drive
      FETCH NEXT FROM dcur INTO @drive
    END

    CLOSE dcur
    DEALLOCATE dcur

    EXEC @hr = sp_OADestroy @fso
    IF @hr  0
      EXEC sp_OAGetErrorInfo @fso

    SELECT
      @@servername AS ServerName,
      drive,
      FreeSpace AS 'Free(MB)',
      TotalSize AS 'Total(MB)',
      CAST((FreeSpace / (TotalSize * 1.0)) * 100.0 AS int) AS 'Free(%)',
      GETDATE() AS Date_Entered INTO #result_set
    FROM #drives

    -- Print Intermediate Results
    --select * from #result_set


    INSERT INTO [DB_Maint].[DBO].[Capacity_DiskSpaceTracking] ([ServerName],
    [drive],
    [Free(MB)],
    [Total(MB)],
    [Free(%)],
    [Date_Entered])
      SELECT
        ServerName,
        drive,
        [Free(MB)],
        [Total(MB)],
        [Free(%)],
        Date_Entered
      FROM #result_set


    --INSERT INTO Capacity_DiskSpaceTracking
    --(DriveLetter, Label, FreeSpaceMB,UsedSpaceMB,TotalSpaceMB,FreeSpacePercentage)
    --SELECT DriveLetter
    --, Label
    --, FreeSpace 
    --, (TotalSpace - FreeSpace) AS [UsedSpace MB]
    --, TotalSpace 
    --, ((CONVERT(NUMERIC(9,2),FreeSpace) / CONVERT(NUMERIC(9,2),TotalSpace)) * 100) AS [Percentage Free]
    --FROM ##_DriveInfo
    --ORDER BY [DriveLetter] ASC


    -- Display Results.

    SELECT
      *
    FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]


    DECLARE @servername nvarchar(100),
            @drive1 nvarchar(2),
            @freeMB int,
            @totalMB int,
            @free int,
            @date_entered nvarchar(50)

    DECLARE db_crsr_T CURSOR FOR
    SELECT
      [ServerName],
      [drive],
      [Free(MB)],
      [Total(MB)],
      [Free(%)],
      [Date_Entered]
    FROM #result_set

    OPEN db_crsr_T
    FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
    WHILE @@fetch_status = 0
    BEGIN

      IF @free  10
      BEGIN
        DECLARE @msg1 nvarchar(1000),
                @subject nvarchar(4000)

        SELECT
          @subject = 'DISK SPACE ALERT !! ' + '  ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 30% Free Space '
        -- + ' ' + 'in Database: ' + @DBNAME;


        --declare @body1 nvarchar(max)
        SET @msg1 = N'<H1> <Font Color="red"> DRIVE FREE SPACE CHECK: </font> </H1>' +
        N'<H3> <Font Color="red"> Server: ' + @@servername + '</H3></font>' +
        '<BR>' + '<BR>';

        SET @msg1 = @msg1 + 'Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile, -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
                                     @recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...
                                     @body = @msg1,
                                     @subject = @subject,
                                     @body_format = 'HTML',
                                     @importance = 'HIGH';
      END

      IF @free < 10
      BEGIN
        DECLARE @msg2 nvarchar(1000),
                @subject2 nvarchar(4000);

        SELECT
          @subject2 = 'DISK SPACE ALERT !! ' + '  ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 10% Free Space ';

        SET @msg2 = N'<H1> <Font Color="red"> DRIVE FREE SPACE CHECK:  WARNING !!! One Or more Drive has <10% free space !!! </font> </H1>' +
        N'<H3> <Font Color="red"> Server: ' + @@servername + '</H3></font>' +
        '<BR>' + '<BR>';

        SET @msg2 = @msg2 + '<Font Color="red">WARNING!! </font> Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile,  -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
                                     @recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...
                                     @body = @msg2,
                                     @subject = @subject2,
                                     @body_format = 'HTML',
                                     @importance = 'HIGH';
      END

      FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
    END

    CLOSE db_crsr_T
    DEALLOCATE db_crsr_T


    DROP TABLE #drives
    DROP TABLE #result_set

    -- Cleanup from physical table.

    DECLARE @NumRecords varchar(20)

    PRINT @deleteEntry;

    -- Retain records for 60 days Old. Keep @deleteEntry = 60
    DELETE FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]
    WHERE [Date_Entered]  0
    BEGIN
      --DELETE FROM Capacity_DiskSpaceTracking WHERE DATEDIFF(DAY, TimeCollected, GETDATE()) > @deleteEntry
      DBCC CHECKIDENT ([DB_Maint.dbo.Capacity_DiskSpaceTracking], RESEED, 0);
    END

  END TRY
  BEGIN CATCH
    DECLARE @ErrorNumber int;
    DECLARE @ErrorSeverity int;
    DECLARE @ErrorState int;
    DECLARE @ErrorLine int;
    DECLARE @ErrorProcedure nvarchar(4000);
    DECLARE @ErrorMessage nvarchar(4000);

    SELECT
      @ErrorNumber = ERROR_NUMBER(),
      @ErrorSeverity = ERROR_SEVERITY(),
      @ErrorState = ERROR_STATE(),
      @ErrorLine = ERROR_LINE(),
      @ErrorProcedure = ERROR_PROCEDURE();

    SELECT
      @ErrorMessage =
      N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
      + 'Message: ' + ERROR_MESSAGE();

    SELECT
      @ErrorMessage AS [Error_Message];

    SELECT
      @ErrorProcedure AS [Error_Procedure];

    PRINT 'Error '
    + CONVERT(varchar(50), ERROR_NUMBER())
    + ', Severity '
    + CONVERT(varchar(5), ERROR_SEVERITY())
    + ', State '
    + CONVERT(varchar(5), ERROR_STATE())
    + ', Procedure '
    + ISNULL(ERROR_PROCEDURE(), '-') + ', Line '
    + CONVERT(varchar(5), ERROR_LINE());

    PRINT ERROR_MESSAGE();
  END CATCH

  SET NOCOUNT OFF
END

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO