데이터베이스 여유공간 확인
- Version : SQL Server 2005, 2008, 2008R2, 2012
데이터베이스를 운영하다 보면 데이터가 증가함에 따라 할당된 용량이 가득 차거나 또는 자동 증가 옵션으로 인하여 꾸준히 증가하는 현상을 경험 할 수 있다. DBA는 데이터 파일 사이즈의 증가에 대하여 꾸준히 모니터링 해야 한다.
다양한 스크립트를 통하여 데이터베이스의 여유 공간과 현재 데이터베이스의 파일 옵션 환경을 확인 하자.
다음 스크립트를 실행하면 드라이브 별로 전체 디스크의 용량을 확인 할 수 있다.
xp_fixeddrives |
위의 스크립트에서는 드라이브의 전체크기 밖에 알 수 없다. 다음의 스크립트를 통하여 드라이브 별로 전체 공관과 남은 공간을 확인하자. 'xp_cmdshell'을 사용하므로 sp_configure 에서 'xp_cmdshell' 옵션을 활성화 후 실행 하여야 한다.
EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE;
BEGIN TRY drop table #output END TRY BEGIN CATCH END CATCH
declare @svrName varchar(255) declare @sql varchar(400) set @svrName = @@SERVERNAME set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"' CREATE TABLE #output (line varchar(1000)) insert #output EXEC xp_cmdshell @sql select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)' ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)' ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)' ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)' from #output where line like '[A-Z][:]%' order by drivename |
디스크이 드라이브 여유공간을 확인 하였으면 각 데이터베이스에 할당된 공간과 사용공간, 여유공간 그리고 파일 증가 옵션 정보를 확인하자.
SELECT DB_NAME(database_id) AS DBName, [Name] as [FileName], physical_name AS PhysicalFilePath, type_desc AS FileType, CONVERT(NVARCHAR(50), ((size * 8)/1024)) + 'MB' AS FileSize, MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED' WHEN max_size = 0 THEN 'NO_GROWTH' WHEN max_size <> -1 OR max_size <> 0 THEN CONVERT(NVARCHAR(50), (max_size * 8) / 1024) ELSE 'Unknown' END, SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED' WHEN max_size <> -1 OR max_size = 268435456 THEN CONVERT(NVARCHAR(50), ((max_size - size) * 8) / 1024) ELSE 'Unknown' END, Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE' WHEN growth > 0 and is_percent_growth = 0 THEN CONVERT(NVARCHAR(50), ((growth * 8)/1024)) WHEN growth > 0 and is_percent_growth = 1 THEN CONVERT(NVARCHAR(50), growth) ELSE 'Unknown' END, GrowthType = CASE WHEN is_percent_growth = 1 THEN '%' WHEN is_percent_growth = 0 THEN 'MB' ELSE 'Unknown' END FROM master.sys.master_files WHERE state = 0 AND type_desc IN ('LOG', 'ROWS') ORDER BY database_id, file_id |
DBA는 데이터의 증가에 대하여 꾸준히 모니터링 하여 스토리지 전략 및 파일 관리 전략을 잘 구성하여야 한다.
일 데이터 또는 주 데이터의 증가량을 측정하여 향후 서비스에 필요한 공간을 미리 예측 할 수 있다.
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
