데이터베이스를 생성할 때, 여러 개의 그룹을 만들어 이용하는 경우가 많습니다. 예를 들어, INDEXGROUP 이라는 파일 그룹을 만들어 인덱스는 모두 해당 파일 그룹에 생성한다든지, 덜 중요한 데이터는 다른 파일 그룹에 저장한다든지..
하지만, 다음과 같은 경우를 생각할 수 있습니다.
|
CASE1) 인덱스(NonClustered Index)를 모두 다른 하나의 파일 그룹에 두기로 했는데, 실수로 그냥 PRIMARY에 뒀다.! CASE2) 목적에 따라 테이블을 파일 그룹별로 분리시키기로 했는데, 시간이 지나다 보니 제대로 안되었다.! CASE3) 파일 그룹에 어떤 개체들이 들어있는지 알아야 겠다.! CASE4) 데이터 공간이 부족해서 에러가 나부렸따.. 그런데, 파일도 많고, 파일 그룹도 많고.. 파일 그룹별로 사용량을 보고 싶다~! |
이러한 경우에 이용하기 위하여 다음과 같은 스크립트를 작성해 보았습니다..(물론, 도움말 및 기타 사이트 참고해서.^^)
혹시 위의 경우 외에도 참고하시기 바라며, 잘못된 부분이 있으시면 리플달아주세요~~
(예를 들어..걍 sysindexes 테이블 보믄 되지~!! 같은 것.^^ -> 다 짜고보니, 그냥 그렇게 봐도 되겠네 생각 듭디다..ㅎㅎ)
CREATE DATABASE TESTDB
ON
(NAME=TESTDB_DATA1, FILENAME='C:\TESTDB_DATA1.MDF', SIZE = 10MB,
FILEGROWTH=10MB, MAXSIZE = 30MB),
FILEGROUP [SECONDARY]
(NAME=TESTDB_DATA2, FILENAME='C:\TESTDB_DATA2.NDF', SIZE = 10MB,
FILEGROWTH=10MB, MAXSIZE=50MB),
(NAME=TESTDB_DATA, FILENAME='C:\TESTDB_DATA3.NDF', SIZE = 10MB,
FILEGROWTH=10MB)
LOG ON
(NAME=TESTDB_LOG, FILENAME='C:\TESTDB_LOG.LDF', SIZE = 50MB, FILEGROWTH=10MB)
GO
SP_HELPDB TESTDB
GO
/*
NAME FIELD FILENAME FILEGROUP SIZE MAXSIZE
-------------------------------------------------------------------------------------------
TESTDB_DATA1 1 C:\TESTDB_DATA1.MDF PRIMARY 10240 KB 30720 KB
TESTDB_LOG 2 C:\TESTDB_LOG.LDF NULL 51200 KB Unlimited
TESTDB_DATA2 3 C:\TESTDB_DATA2.NDF SECONDARY 10240 KB 51200 KB
TESTDB_DATA 4 C:\TESTDB_DATA3.NDF SECONDARY 10240 KB Unlimited
*/
USE TESTDB
GO
CREATE TABLE TABLEA
(
SEQ INT IDENTITY,
COL1 CHAR(1000) DEFAULT(REPLICATE('A', 999)),
CONSTRAINT PK_TABLEA PRIMARY KEY(SEQ)
) ON [PRIMARY]
GO
CREATE INDEX IDX__1 ON TABLEA(SEQ) ON SECONDARY
GO
CREATE TABLE TABLEB
(
SEQ INT IDENTITY,
COL1 CHAR(1000) DEFAULT(REPLICATE('B', 999)),
CONSTRAINT PK_TABLEB PRIMARY KEY(SEQ)
) ON SECONDARY
GO
SET NOCOUNT ON
GO
DECLARE @I AS INT
SET @I = 1
WHILE @I<=50000
BEGIN
INSERT TABLEA DEFAULT VALUES
INSERT TABLEB DEFAULT VALUES
INSERT TABLEB DEFAULT VALUES
SET @I = @I + 1
END
GO
/*
메시지1105, 수준17, 상태2, 줄5
'PRIMARY' 파일 그룹이 꽉차서'TESTDB' 데이터베이스의'TABLEA' 개체에 공간을 할당할 수 없습니다.
*/
SET NOCOUNT OFF
GO
--파일그룹별 개체정보
SELECT
'FileGroup Name' = sfg.GroupName,
'Object Name' = CASE si.IndID
WHEN 1 THEN so.Name
ELSE si.Name END,
'Object Type' = CASE
WHEN si.IndID < 2 THEN 'Table'
ELSE 'Index'END,
'Table Name'= CASE
WHEN si.IndID > 1 THEN so.Name
ELSE ' ' END,
'System FileName' = sf.Name
FROM sysfilegroups sfg
INNER JOIN sysfiles sf ON sfg.groupid = sf.groupid
INNER JOIN sysindexes si ON sfg.groupid = si.groupid
INNER JOIN sysobjects so ON si.id = so.id
WHERE so.type = 'U'
AND si.Name NOT LIKE '#_%' ESCAPE '#'
AND so.Name NOT IN ('dtproperties')
ORDER BY 1, 3 DESC
GO
--파일 그룹별, 파일 수, 사용한 크기, 현재 크기, 최대 크기 출력
SELECT
a.FileGroupName,
b.[FileCount],
a.[UsedSize(MB)],
b.[FileNowSize(MB)],
CASE WHEN C.MinCheck = -1 THEN 'Unlimited' ELSE CAST(b.[MaxSize(MB)] AS VARCHAR(100)) END as [MaxSize(MB)]
FROM (
SELECT --파일 그룹별 크기(실제 사용 크기)
'FileGroupName' = sfg.GroupName,
sfg.GroupId,
SUM(si.used * 8.0/1024) AS [UsedSize(MB)]
FROM sysfilegroups sfg
INNER JOIN sysindexes si ON sfg.groupid = si.groupid
INNER JOIN sysobjects so ON si.id = so.id
WHERE so.type = 'U'
AND si.Name not like '#_%' ESCAPE '#'
AND so.Name not in ('dtproperties')
GROUP BY sfg.GroupName, sfg.GroupId
) a
INNER JOIN
(
SELECT --파일 그룹별 크기(잡혀있는 크기)
GroupId,
SUM(size*8/1024.0) AS [FileNowSize(MB)],
SUM(maxsize*8/1024.0) AS [MaxSize(MB)],
COUNT(*) AS [FileCount]
FROM sysfiles
WHERE GroupId>0
GROUP BY GroupId
) b ON a.GroupId = b.GroupId
LEFT JOIN
(
SELECT ?파일 그룹 중MaxSize = Limited 인 경우확인
GroupId, MIN(maxsize) as MinCheck
FROM sysfiles
WHERE GroupId>0
GROUP BY GroupId
) c ON A.GroupId = c.GroupId
GO
'연구개발 > DBA' 카테고리의 다른 글
| 데이터베이스 유지 관리 계획 백업 및 복원 (0) | 2009.07.25 |
|---|---|
| SQL 서버를 활용한 성능 카운터 수집 (0) | 2009.07.25 |
| SQL Server 2005 인스턴스 간에 로그인 및 암호 전송 (0) | 2009.07.25 |
| sys.extended_properties (0) | 2009.07.21 |
| parsename (0) | 2009.07.20 |