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 |
[참고자료]
2013-09-13 / 강성욱 / http://sqlmvp.kr
'SQL Server > SQL Server Tip' 카테고리의 다른 글
| SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? (0) | 2015.07.22 |
|---|---|
| 저장된 Plan Cache 확인 및 활용 (0) | 2015.07.22 |
| 강제 매개변수화로 인한 성능 저하 사례 (0) | 2015.07.22 |
| 파라메터 스니핑과 데이터 스큐 (0) | 2015.07.22 |
| DBCC CHECKDB 버그 및 해결 방법 (0) | 2015.07.22 |