MSSQL 물리적 드라이브 가용량 보기 (DBA 관리용 시스템 프로시저)

/************************************************************************************************

-- sp_freedisksize0

-- 물리적 드라이브 가용량을 표시한다.

************************************************************************************************/

USE MASTER

GO

IF OBJECT_ID('sp_freedisksize0') IS NOT NULL

   DROP PROCEDURE sp_freedisksize0

GO

CREATE PROC sp_freedisksize0    

AS

  SET NOCOUNT ON

  DECLARE @hr INT

  DECLARE @fso INT

  DECLARE @drive CHAR(1)

  DECLARE @odrive INT

  DECLARE @TotalSize VARCHAR(20)

  DECLARE @free INT

  DECLARE @query VARCHAR(1300)

  DECLARE @MB BIGINT 

  SET @MB = 1048576

  SET @query= 'master.dbo.xp_fixeddrives'

  CREATE TABLE #drives 

  ( drive CHAR(1) PRIMARY KEY

  , FreeSize INT NULL

  , TotalSize INT NULL

  )

  INSERT #drives(drive,FreeSize) EXEC @query

  EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

  DECLARE cur_freespace CURSOR FAST_FORWARD

  FOR SELECT drive 

      FROM #drives

      ORDER by drive

  OPEN cur_freespace

  FETCH NEXT FROM cur_freespace 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 cur_freespace INTO @drive

  END

  CLOSE cur_freespace

  DEALLOCATE cur_freespace

  EXEC @hr=sp_OADestroy @fso

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

  SELECT drive "Drive"

       , totalsize "TotalSize(MB)"

       , freesize "FreeSize(MB)"

       , CAST(CAST(freesize as DEC(10,2)) / CAST(totalsize AS DEC(10,2)) 

              * 100 AS DEC(10,2)) "FreeSize(%)"

  FROM #drives

  DROP TABLE #drives

  RETURN(0) --sp_freedisksize0

  SET NOCOUNT OFF

GO

  

GRANT EXECUTE ON sp_freedisksize0 TO PUBLIC

GO