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

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

데이터베이스 여유공간 확인

 

  • 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

No. Subject Author Date Views
1690 SQL Server 성능 대시보드 jevida(강성욱) 2016.09.13 2201
1689 SSMS를 이용한 데이터베이스 복사 하기 jevida(강성욱) 2016.09.13 10850
1688 SQL Server에서 DB2 링크드 서버 연결 방법 jevida(강성욱) 2016.09.13 3249
1687 SQL Server 인덱스 활성 / 비활성 하기 jevida(강성욱) 2016.09.13 2308
1686 파티션 오프셋과 SQL Server에 대한 디스크 할당 단위 크기 jevida(강성욱) 2016.09.13 1675
1685 Read the End of a Large Error Log (에러로그 끝 부분 읽기) jevida(강성욱) 2016.09.13 1298
» 데이터베이스 여유공간 확인 jevida(강성욱) 2016.09.13 1445
1683 FILE GROUP 간 데이터 이동 jevida(강성욱) 2016.09.13 1601
1682 문서화 되지 않은 sys.fn_PhysLocFormatter 함수 jevida(강성욱) 2016.09.13 943
1681 SQL Server 관리자 전용 연결(DAC) jevida(강성욱) 2016.09.13 3314
1680 파일 접근 권한으로 인한 DB복원 실패 작업 해결하기 jevida(강성욱) 2016.09.13 1894
1679 DBCC CHECKDB 실행과 히스토리 관리 jevida(강성욱) 2016.09.13 1839
1678 MDF를 이용한 데이터베이스 복원 jevida(강성욱) 2016.09.13 1173
1677 EventID 3041 Error jevida(강성욱) 2016.09.13 1389
1676 데이터베이스 미러 백업 jevida(강성욱) 2016.09.13 1311
1675 SNAPSHOT을 이용한 SQL Server 복원 jevida(강성욱) 2016.09.13 2244
1674 복사 전용 백업(COPY ONLY BACKUP) jevida(강성욱) 2016.09.13 3271
1673 SQL Server 기본 백업 폴더 변경하기 jevida(강성욱) 2016.09.13 3985
1672 sp_helpindex 의 향상된 버전 minsouk 2016.05.17 4559
1671 Spatial Index (공간 인덱스) 생성, 수정, 삭제 jevida(강성욱) 2016.01.11 9200





XE Login