MSSQL 디스크 용량 확인

USE [master]
GO
CREATE FUNCTION [dbo].[ufn_LogicalDiskDrives]()
RETURNS @DriveList Table
  (
   [DriveLetter]  CHAR(1)
  ,[VolumeName]  VARCHAR(255)
  ,[FileSystem]  VARCHAR(50)
  ,[TotalSize] BIGINT
  ,[AvailableSpace]   BIGINT
  ,[FreeSpace] BIGINT 
  )
AS
BEGIN
--Written by Percy Reyes
  DECLARE @DriveLetter_ASCII_Code  INT
  DECLARE @FileSystemInstance  INT
  DECLARE @DriveCount  INT
  DECLARE @DriveCollection  INT
  DECLARE @Drive  INT
  DECLARE @Property  NVARCHAR(100)    
  DECLARE @DriveLetter  VARCHAR(1)  
  DECLARE @TotalSize BIGINT
  DECLARE @AvailableSpace  BIGINT
  DECLARE @FreeSpace  BIGINT  
  DECLARE @FileSystem VARCHAR(128)
  DECLARE @VolumeName VARCHAR(128)
  DECLARE @IsReady VARCHAR(5)
  
  --Creating a File System Object for getting files or disk info.
  exec sp_OACreate 'Scripting.FileSystemObject', @FileSystemInstance OUT
  --Getting the collection of drives
  exec sp_OAGetProperty @FileSystemInstance,'Drives', @DriveCollection OUT
  --Getting the count of drives from collection
  exec sp_OAGetProperty @DriveCollection,'Count', @DriveCount OUT

  --starting from Drive "A" (ASCII 65) 
  SET @DriveLetter_ASCII_Code = 65
  --to "Z" (ASCII 90)
  WHILE @DriveLetter_ASCII_Code <= 90
  BEGIN
  ---Creating the instance drive from Drive Collection 
        SET @Property = 'item("'+CHAR(@DriveLetter_ASCII_Code)+'")'
        exec sp_OAGetProperty @DriveCollection,@Property, @Drive OUT
  -- Getting the drive letter property
        exec sp_OAGetProperty @Drive,'DriveLetter', @DriveLetter OUT

  IF @DriveLetter = CHAR(@DriveLetter_ASCII_Code)
        BEGIN   
        -- Getting more properties from each drive   
     exec sp_OAGetProperty @Drive,'VolumeName', @VolumeName OUT 
     exec sp_OAGetProperty @Drive,'FileSystem', @FileSystem OUT
     exec sp_OAGetProperty @Drive,'TotalSize', @TotalSize OUT 
              exec sp_OAGetProperty @Drive,'AvailableSpace', @AvailableSpace OUT
              exec sp_OAGetProperty @Drive,'FreeSpace', @FreeSpace OUT          
              exec sp_OAGetProperty @Drive,'IsReady'  , @IsReady OUT; 
            
   IF @IsReady='True'
     INSERT INTO @DriveList ( [DriveLetter],[TotalSize], [AvailableSpace],[FreeSpace],[FileSystem] ,[VolumeName] )
     VALUES( @DriveLetter,@TotalSize,@AvailableSpace,@FreeSpace,@FileSystem,@VolumeName)
                             
            END
   -- forward next drive
            SET @DriveLetter_ASCII_Code = @DriveLetter_ASCII_Code +1
  END 
  
  EXEC sp_OADestroy @Drive 
  EXEC sp_OADestroy @DriveCollection

  RETURN
END

 

새 기능을 사용할 수 있도록 "Ole 자동화 절차" 구성 설정을 활성화해야 합니다. 그렇지 않으면 실패합니다.

 

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE
GO

 

ufn_LogicalDiskDrives 테스트

SELECT * FROM [dbo].[ufn_LogicalDiskDrives]() 

 

새 저장 프로시저 sp_fixeddrives는 모든 데이터베이스에서 호출할 수 있도록 시스템 개체로 표시됩니다. 이 저장 프로시저는 ufn_LogicalDiskDrives 함수에서 반환된 결과를 사용하고 총 데이터 파일 크기 및 총 로그 파일 크기 정보를 최종 결과에 추가합니다. 다음은 T-SQL 생성 코드입니다.

 

select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter,  DF.DataSize_GB, LF.LogSize_GB from  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB
   from sys.master_files
   where type_desc='ROWS'
   group by left(physical_name, 1), type_desc 
   ) DF
  full outer join 
  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024  AS DECIMAL(10,2)) LogSize_GB
   from sys.master_files 
   where type_desc='LOG'
   group by left(physical_name, 1), type_desc
   ) LF
  on DF.DriveLetter=LF.DriveLetter

 

결과는 다음과 같습니다.

마지막으로 저장 프로시저 sp_fixeddrives의 T-SQL 생성 코드는 다음과 같습니다.

 

USE [master]
GO
CREATE PROC dbo.sp_fixeddrives
WITH ENCRYPTION
AS
BEGIN
 SET NOCOUNT ON 
 -----Reporting Drive details ....
 select LDD.DriveLetter ,[VolumeName] ,[FileSystem]  
 ,CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) [Capacity_GB]
 ,CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2)) [FreeSpace_GB]
 ,CAST((CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2))/CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) ) *100 AS DECIMAL(10,2)) [Free %]
 , ISNULL( DBFiles.DataSize_GB,0) DataSize_GB,ISNULL( DBFiles.LogSize_GB,0) LogSize_GB
 from master.[dbo].[ufn_LogicalDiskDrives]() LDD
 full outer join 
 (
 select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter,  DF.DataSize_GB, LF.LogSize_GB from
  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB
   from sys.master_files
   where type_desc='ROWS'
   group by left(physical_name, 1), type_desc 
   ) DF
  full outer join 
  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024  AS DECIMAL(10,2)) LogSize_GB
   from sys.master_files 
   where type_desc='LOG'
   group by left(physical_name, 1), type_desc
   ) LF
  on DF.DriveLetter=LF.DriveLetter
  
 )  DBFiles
 ON DBFiles.DriveLetter =LDD.DriveLetter
 SET NOCOUNT OFF
END

 

저장 프로시저를 시스템 개체로 표시합니다.

 

USE [master]
GO
EXEC sys.sp_MS_marksystemobject 'sp_fixeddrives'

 

다음은 코드를 실행한 후의 출력입니다.

EXEC sp_fixeddrives