USE MASTER
GO

IF OBJECT_ID('sp_ABCheckFreeSpace') IS NOT NULL
        DROP PROCEDURE sp_ABCheckFreeSpace 
GO

CREATE PROCEDURE sp_ABCheckFreeSpace (@Threshhold int = 300)

/*  Name:       sp_ABCheckFreeSpace

    Purpose:    Raise an error if the free space on a drive that contains the root-level
                directory name of MSSQL7 has less than the threshhold of free space.
    
    Returns:    Count of errors raised (0 if no problems).
    
    Sample Usage:
    sp_ABCheckFreeSpace 200
                    
    History:
      03/20/2001  VRI  Created.
*/

AS

SET NOCOUNT ON

DECLARE @FreeSpace int,
        @FreeSpaceChar varchar(15),  
        @Drive char(1),
        @FileExist int,
        @Mssql varchar(20),
        @ErrorCount int

SELECT @ErrorCount = 0

CREATE TABLE #Drives (Drive char(1), FreeSpace int)
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)

INSERT #Drives 
  EXEC master..xp_fixeddrives

WHILE EXISTS (SELECT * FROM #Drives)
BEGIN

  SELECT @Drive = Drive, 
         @FreeSpace = FreeSpace
    FROM #Drives

  DELETE #Drives
    WHERE Drive = @Drive
    
  SELECT @Mssql = @Drive + ':\MSSQL7',
         @FreeSpaceChar = convert(varchar(10), @FreeSpace)

  DELETE #FileExists
  INSERT #FileExists       
    EXEC master..xp_fileexist @Mssql
    
  IF @FreeSpace < @Threshhold AND EXISTS (SELECT * FROM #FileExists WHERE FileIsDir = 1)
  BEGIN
     RAISERROR('Drive %s on server %s has only %s MB left.', 19, 1, @Drive, @@Servername, @FreeSpaceChar) WITH LOG
     SELECT @ErrorCount = @ErrorCount + 1
  END   
     
END
RETURN @ErrorCount
GO

GRANT EXEC ON sp_ABCheckFreeSpace TO public
GO