데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

Xp_fixeddrives 세부 정보 확인하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서 디스크 드라이브의 여유 공간을 확인하는 방법에는 여러 가지가 있지만 가장 쉽게 접근하는 방법으로는 xp_fixeddrives 함수를 사용하는 것이다.

 

xp_fixeddrives

 

 

Xp_fixeddrives 함수를 사용하여 드라이브의 여유 공간을 확인 하는데는 매우 편리한건 사실이다. 하지만 여기에 나머지 추가 정보를 더 확인 할 수 있으면 더 좋겠다는 생각이 든다.

 

MSSQLTips.com에 포스트된 Kun Lee 글에서 xp_fixeddrives에 추가 정보를 확인 할 수 있는 스크립트를 공개하였다.

 

[Kun lee 요구사항]

  • 전체 디스크 크기 정보가 필요하다
  • 볼륨 이름을 표시해야 한다
  • 마운트 포인트 정보가 필요하다
  • 새 열이 없는 한 동일한 열 이름을 유지해야 한다.
  • 디스크 및 데이터베이스 정보를 표시해야 한다
  • 비정상 트랜잭션 로그 파일 크기를 해결하는데 도움이 되는 데이터베이스 복구 옵션을 표시해야 한다.

 

 

[사전 요구 사항]

  • Xp_cmdshell이 활성화 되어 있어야 한다.
  • WMI 스크립트가 켜져 있어야 한다
  • Windows Server 2003 이상 (테스트를 이 버전에서 했음)

 

[sp_fixeddrives 스크립트]

use master

GO

 

if isnull(object_id('dbo.sp_fixeddrives'),0) = 0

    exec('create procedure dbo.sp_fixeddrives as print ''temporary procedure to hold location so we can use ALTER in the script''')

GO

 

/* Author : Kun Lee

 

Change Log

    Kun Lee 20130723 Changed the Ouput to match as close as possible with xp_fixeddrivs

    Kun Lee 20130813 Fix the known bug on Windows 2003 doesn't return Volume name when lenght is longer than x number of charactors

    

-- Make sure xp_cmdshell is turned on

-- You should be able to run WMI scrip via xp_cmdshell

 

EXAMPLE

EXEC sp_fixeddrives - Print out just fixed drive info with free space

EXEC sp_fixeddrives 1 -- Print out more detail per DB

*/

 

ALTER PROCEDURE dbo.sp_fixeddrives

    @format tinyint = 0

AS

BEGIN

 

SET ARITHIGNORE ON

SET NOCOUNT ON

 

DECLARE @SQL NVARCHAR(1000)

 

CREATE TABLE #DrvLetter (

Drive VARCHAR(500),

)

CREATE TABLE #DrvInfo (

Drive VARCHAR(500) null,

[MB free] DECIMAL(20,2),

[MB TotalSize] DECIMAL(20,2),

[Volume Name] VARCHAR(64)

)

 

 

 

INSERT INTO #DrvLetter

EXEC xp_cmdshell 'wmic volume where drivetype="3" get caption, freespace, capacity, label'

 

DELETE

FROM #DrvLetter

WHERE drive IS NULL OR len(drive) < 4 OR Drive LIKE '%Capacity%'

    OR Drive LIKE '%\\%\Volume%'

 

 

DECLARE @STRLine VARCHAR(8000)

DECLARE @Drive varchar(500)

DECLARE @TotalSize REAL

DECLARE @Freesize REAL

DECLARE @VolumeName VARCHAR(64)

 

WHILE EXISTS(SELECT 1 FROM #DrvLetter)

BEGIN

SET ROWCOUNT 1

SELECT @STRLine = drive FROM #DrvLetter

 

-- Get TotalSize

SET @TotalSize= CAST(LEFT(@STRLine,CHARINDEX(' ',@STRLine)) AS REAL)/1024/1024

--SELECT @TotalSize

 

-- Remove Total Size

SET @STRLine = REPLACE(@STRLine, LEFT(@STRLine,CHARINDEX(' ',@STRLine)),'')

-- Get Drive

 

SET @Drive = LEFT(LTRIM(@STRLine),CHARINDEX(' ',LTRIM(@STRLine)))

--SELECT @Drive

 

SET @STRLine = RTRIM(LTRIM(REPLACE(LTRIM(@STRLine), LEFT(LTRIM(@STRLine),CHARINDEX(' ',LTRIM(@STRLine))),'')))

 

SET @Freesize = LEFT(LTRIM(@STRLine),CHARINDEX(' ',LTRIM(@STRLine)))

--SELECT @Freesize/1024/1024

 

SET @STRLine = RTRIM(LTRIM(REPLACE(LTRIM(@STRLine), LEFT(LTRIM(@STRLine),CHARINDEX(' ',LTRIM(@STRLine))),'')))

SET @VolumeName = @STRLine

--

 

INSERT INTO #DrvInfo

SELECT @Drive, @Freesize/1024/1024 , @TotalSize, @VolumeName

 

DELETE FROM #DrvLetter

END

 

SET ROWCOUNT 0

 

 

 

-- POPULATE TEMP TABLE WITH LOGICAL DISKS

-- This is FIX/Workaround for Windows 2003 bug that WMIC doesn't return volume name that is over X number of charactors.

SET @SQL ='wmic /FailFast:ON logicaldisk where (drivetype ="3" and volumename!="RECOVERY" AND volumename!="System Reserved") get deviceid,volumename /Format:csv'

if object_id('tempdb..#output1') is not null drop table #output1

CREATE TABLE #output1 (Col1 VARCHAR(2048))

INSERT INTO #output1

EXEC master..xp_cmdshell @SQL

DELETE #output1 where ltrim(col1) is null or len(col1) = 1 or Col1 like 'Node,DeviceID,VolumeName%'

 

 

if object_id('tempdb..#logicaldisk') is not null drop table #logicaldisk

CREATE TABLE #logicaldisk (DeviceID varchar(128),VolumeName varchar(256))

 

DECLARE @NodeName varchar(128)

SET @NodeName = (SELECT TOP 1 LEFT(Col1, CHARINDEX(',',Col1)) FROM #output1)

 

-- Clean up server name

UPDATE #output1 SET Col1 = REPLACE(Col1, @NodeName, '')

 

INSERT INTO #logicaldisk

SELECT LEFT(Col1, CHARINDEX(',',Col1)-2), SUBSTRING(COL1, CHARINDEX(',',Col1)+1, LEN(col1))

FROM #output1

 

 

UPDATE dr

SET dr.[Volume Name] = ld.VolumeName

    FROM #DrvInfo dr RIGHT OUTER JOIN #logicaldisk ld ON left(dr.Drive,1) = ld.DeviceID

WHERE LEN([Volume Name]) = 1

 

 

IF @format = 0

BEGIN

SELECT CASE

        WHEN LEN(drive) = 3 THEN LEFT(drive,1)

        ELSE drive

    END AS drive,

    [MB free],    [MB TotalSize], [Volume Name]

 

FROM #DrvInfo

ORDER BY 1

END

 

ELSE IF @format = 1

BEGIN

 

    CREATE TABLE #DBInfo2

    ( ServerName VARCHAR(100),

    DatabaseName VARCHAR(100),

    FileSizeMB INT,

    LogicalFileName sysname,

    PhysicalFileName NVARCHAR(520),

    Status sysname,

    Updateability sysname,

    RecoveryMode sysname,

    FreeSpaceMB INT,

    FreeSpacePct VARCHAR(7),

    FreeSpacePages INT,

    PollDate datetime)

 

    DECLARE @command VARCHAR(5000)

 

    SELECT @command = 'Use [' + '?' + '] SELECT

    @@servername as ServerName,

    ' + '''' + '?' + '''' + ' AS DatabaseName,

    CAST(sysfiles.size/128.0 AS int) AS FileSize,

    sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,

    CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,

    CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,

    CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,

    CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +

         'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,

    CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,

    ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))

    AS decimal(4,2))) AS varchar(8)) + ' + '''' + '''' + ' AS FreeSpacePct

    FROM dbo.sysfiles'

    INSERT INTO #DBInfo2

     (ServerName,

     DatabaseName,

     FileSizeMB,

     LogicalFileName,

     PhysicalFileName,

     Status,

     Updateability,

     RecoveryMode,

     FreeSpaceMB,

     FreeSpacePct)

    EXEC sp_MSForEachDB @command

 

 

    SELECT

     db.DatabaseName as DBName,

     db.LogicalFileName as DBLogicalFileName,

     db.PhysicalFileName as DBPhysicalFileName,

     db.RecoveryMode as DBRecoveryMode,

     db.FileSizeMB AS DBFileSizeMB,

     db.FreeSpaceMB as DBFreeSpaceMB,

     db.FreeSpacePct as DBFreeSpacePct,

     CASE

        WHEN LEN(dr.drive) = 3 THEN LEFT(dr.drive,1)

        ELSE dr.drive

    END AS Drive,

    dr.[MB free] as DriveFreeSpaceMB,

    dr.[MB TotalSize] as DriveTotalSizeMB,

    CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) as DriveFreeSpacePct,

    dr.[Volume Name]

    FROM #DBInfo2 db

        JOIN #DrvInfo dr ON LEFT(db.PhysicalFileName,LEN(dr.drive)) = LEFT(dr.drive,LEN(dr.drive))

    WHERE db.DatabaseName not in (

                SELECT DatabaseName

                FROM #DBInfo2 DB

                    JOIN (SELECT drive FROM #DrvInfo WHERE LEN(drive) > 3) DR on LEFT(db.PhysicalFileName, LEN(drive)) = DR.drive)

    UNION ALL

    SELECT

     db.DatabaseName,

     db.LogicalFileName,

     db.PhysicalFileName,

     db.RecoveryMode,

     db.FileSizeMB,

     db.FreeSpaceMB as DBFreeSpaceMB,

     db.FreeSpacePct as DBFreeSpacePct,

     CASE

        WHEN LEN(dr.drive) = 3 THEN LEFT(dr.drive,1)

        ELSE dr.drive

    END AS drive,

    dr.[MB free] AS DriveFreeSpaceMB,    dr.[MB TotalSize] as DriveTotalSizeMB,

    CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) as DriveFreeSpacePct,

    dr.[Volume Name]

    

    FROM #DBInfo2 db

        JOIN #DrvInfo dr ON LEFT(db.PhysicalFileName,LEN(dr.drive)) = LEFT(dr.drive,LEN(dr.drive))

    WHERE LEN(dr.drive) > 3

    ORDER BY

     db.DatabaseName

 

    DROP TABLE #DBInfo2

END

 

DROP TABLE #logicaldisk

DROP TABLE #DrvLetter

DROP TABLE #DrvInfo

 

END

 

go

 

 

[xp_cmdshell 활성화]

exec sp_configure 'show advanced options', 1

go

 

RECONFIGURE WITH OVERRIDE

GO

 

exec sp_configure 'xp_cmdshell', 1

go

 

RECONFIGURE WITH OVERRIDE

GO

 

 

[xp_fixeddrives 와 sp_fixeddrives 비교]

Xp_fixeddrives

sp_fixeddrives

 

 

[sp_fixeddrives 상세 정보]

exec dbo.sp_fixeddrives 1

 

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3037/getting-more-details-with-an-enhanced-xpfixeddrives-for-sql-server/

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1843
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3451
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2311
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1571
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1745
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4615
» Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1752
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1563
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1530
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1540
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7867
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2222
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1243
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1772
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1788
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1371
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1473
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1864
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1653
1834 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1393





XE Login