본문 바로가기

MSSQL/Performance

운영체제 Disk 사용량 보기

이번에는 Transact-SQL로 운영체제의 총 크기 및 현재 사용하고 있는 크기를 보여주기 위한 SQL문을 소개하고자 한다. 해당 SQL문은 해외 MS-SQL MVP가 만든 듯 합니다.

sp_OAMethod와 sp_OAGetProperty에 사용되는 method들을 찾아보려 하였지만, 정리가 되어 있는것이 없는건지 저의 인터넷 검색 능력이 떨어지는것인지, 쩝


use master

go

 

if object_id('dbo.usp_fixeddrives') is not null

    drop procedure dbo.usp_fixeddrives

go 

 

create procedure dbo.usp_fixeddrives

as

 

/*

*********************************************************************

Author     :                 Bouarroudj Mohamed

E-mail       :                 mbouarroudj@sqldbtools.com

Date                           :                 March 2005

Description : xp_fixeddrives wrapper

 

Note : you can add handling error as following (see BOL):

 

    exec @hr = sp_OAMethod @ObjectToken,'GetDrive', @odrive OUT, @drive 

    if @hr <> 0

    begin

        -- Obtains OLE Automation error information

        exec sp_OAGetErrorInfo ...

        handle error

    end

*********************************************************************

*/

 

set nocount on

 

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

-- Declarations

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

 

declare

    @ObjectToken     int,

    @drive           char(1),

    @odrive          int,

    @TotalSize       varchar(20),

    @MB              bigint

 

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

-- Initializations

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

 

set @MB = 1048576

 

create table #Driveslist

(

    Drive         char(1) NOT NULL,

    FreeSpaceMB   int NULL,

    TotalSizeMB   int NULL

)

 

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

-- Processing

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

 

insert #Driveslist(Drive, FreeSpaceMB)

exec master.dbo.xp_fixeddrives

 

exec sp_OACreate 'Scripting.FileSystemObject', @ObjectToken OUT  --Creates an instance of the OLE object on an instance of SQL Server.

 

declare DriveslistCur cursor local fast_forward

for

select Drive from #Driveslist

 

open DriveslistCur

 

fetch next from DriveslistCur into @drive

 

while @@FETCH_STATUS = 0

begin

    -- Calls a method GetDrive

    exec sp_OAMethod @ObjectToken, 'GetDrive', @odrive OUT, @drive 

       

    -- Gets a property TotalSize

    exec sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

 

    update #Driveslist

    set TotalSizeMB = @TotalSize / @MB

    where drive = @drive

       

    fetch next from DriveslistCur into @drive

end

 

close DriveslistCur

deallocate DriveslistCur

 

exec sp_OADestroy @ObjectToken

 

select

    drive,

    TotalSizeMB as 'Total(MB)',

    FreeSpaceMB as 'Free(MB)',

    CAST(TotalSizeMB/1024.0 as decimal(10,2)) 'Total(GB)',

    CAST(FreeSpaceMB/1024.0 as decimal(10,2))'Free(GB)',

    CAST((FreeSpaceMB/(TotalSizeMB * 1.0)) * 100.0 as int) as 'Free(%)'

from #Driveslist

order by drive

 

drop table #Driveslist

 

return

go

 

exec dbo.xp_fixeddrives

go

exec dbo.usp_fixeddrives

go



-----------------------------------------------------
DBA 이승연
Microsoft Certified Trainer (MCT) SQL Server
GRAVITY co.,Ltd. Global Business Dept / Tech Support
Tel 02-2132-7351 / Fax 02-2132-7330
MOBILE 010-9138-7789

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