* 이 포스팅은 개인의 학습 내용을 정리한 것입니다.
2. 데이터베이스의 구조
1) SQL Server 7.0 이후에서 변경된 사항
- 페이지 크기가 2K -> 8K로 증가 : 페이지 및 익스텐트 크기 함께 증가함
-> 익스텐트 : 연속된 8개의 페이지.
- 행에 저장 가능한 크기, 최대문자열 저장크기가 8060바이트(최대문자열은 8000)로 증가.
- 최저 잠금 단위가 페이지->행으로 변경.
3. 데이터베이스 생성
(1) DB생성 구문
|
CREATE DATABASE <db명> [ ON [ <filespec> [,..n] ] [ ,<filegroup> [,..n] ] ] [ LOG ON { <filespec> [,...n] } ] [ COLLATE collation_name ] [ FOR LOAD | FOR ATTACH ] <filespec > ::= [PRIMARY] ( [ NAME = logical_file_name, ] FILENAME = 'os_file_name' [, SIZE = size] [,MAXSIZE = { max_size | UNLIMITED } ] [, FILEGROWTH = growth_incremeWindows ])[,...n] <filegroup> ::= FILEGROUP filegroup_name <filespec> [,...n] |
1) DB명 : 이름 중복되면 안됨. 식별자 규칙을 따라 만들어져야 한다
2) ON : DB에서 데이터가 저장되는 파일을 명시적으로 정의.
3) LOG ON : DB의 로그가 저장되는 로그파일을 명시적으로 정의한다.
4) FOR LOAD : 이전버전과의 호환성을 위해 지원하는 키워드. 7.0부터는 RESTORE를 이용한 DB복구가 가능함
5) FOR ATTACH : 기존의 파일에 DB를 연결하도록 지정 sp_attach_db 프로시저를 사용할 수 있음
6) collation_name : 기본 데이터 정렬을 지원
i) Binary : 바이너리값으로 저장. 대소문자와 액센트를 구분. 가장 빠른 정렬
ii) Case-sensitive : 대소문자를 구분.
iii) Accent-sensitive : 액센트를 가지는 문자에 대해 구분함
iv) Width-sensitive : 전자와 반자 문자를 구분하여 사용할 수 있다.
v) Kana-sensitive : 히라가나와 가타가나를 구분하여 사용가능.
위 항목은 축약어로 처리된다. 의미는 파악하기 쉬움
|
Korean_Wansung_BIN : Binary Korean_Wansung_CI_WS : Case-insensitive, Width-sensitive |
S로 끝나는 경우 sensitive(구분함), I의 경우 insensitive(구분안함).
7) PRIMARY : 주 파일을 정의하도록 지정
8) NAME : <filespec>에 정의된 파일의 논리적 이름을 지정 (FOR ATTACH에선 불필요함)
9) FILENAME : 실제로 생성된 파일의 이름
10) SIZE : DB파일의 크기. 최소 512KB이고, 기본 1MB
11) MAXSIZE : DB파일의 최대 크기
12) UNLIMITED : 파일크기에 제약을 두지 않을 때,
13) FILEGROWTH : 파일이 커질 때, 증가되는 용량.
-파일 크기를 변경하거나 파일을 추가할 경우 ALTER DATABASE~ 를 이용하여 변경하면 된다.
4. DB축소
(1) 자동 파일 축소 : DB옵션에 autoshrink를 설정하면 된다. 이 작업은 DB사용 공간 중 비어있는 공간 중 25%를 감소하고, 30분 간격으로 동작한다.
(2) DBCC SHRINKFILE구문을 이용한 축소
|
DBCC SHRINKFILE ( {file_name | file_id} [, target_size] [,{EMPTYFILE|NOTRUNCATE|TRUNCATEONLY}]) |
1) 지정한 파일 중 비어있는 공간에 대하여 파일을 축소한다. targetsize의 단위는 MB, 이 값이 없을 경우 기본파일크기로 축소를 한다. 또한, targetsize < 최소데이터크기인 경우 targetsize가 아닌 최소데이터 크기까지만 축소
2) EMPTY : 파일의 모든 데이터를 같은 파일 그룹의 다른 파일로 이동
3) NOTRUNCATE : 해제된 파일 공간을 가지고 있음. 사용된 페이지를 앞부분으로 재배치하는 효과만 있다.
4) TRUNCATEONLY : 마지막으로 할당된 익스텐트까지만 사용하고 나머지 공간을 축소함
- 위 구문을 이용하여 축소하고 나면, 축소된 결과를 반환한다. 만약 targetsize가 더 크다면, 축소할 수 없다는 메시지가 발생한다.
(3) DBCC SHRINKDATABASE
- SHRINKFILE이 각각의 파일에 대하여 축소 작업을 수행하지만, SHRINKDATABASE는 데이터와 로그파일 모두를 축소한다. SHRINKDATABASE에서는 DB생성시 지정한 최소 크기 이하로 축소작업을 수행할수는 없다.
|
DBCC SHRINKDATABASE ( database_name [,target_percent][,{NOTRUNCATE|TRUNCATEONLY}] |
1) target_percent : 축소후 DB에 남겨둘 여유공간의 비율이다
5. DB 변경 및 삭제
(1) DB파일그룹 변경
|
ALTER DATABASE <DB명> MODIFY FILEGROUP [filegroup명] DEFAULT |
(2) DB명 변경
|
ALTER DATABASE <DB명> SET SINGLE_USER WITH ROLLBACK IMMEDIATE EXEC sp_renamedb <DB명>, <새로운DB명> EXEC sp_dboption <새로운DB명>, 'SingleUser', FALSE |
- DB명을 변경하기 위해서는 SINGLE_USER모드여야 한다. 그래서 모드를 전환하고, (트랜잭션을 롤백할 경우를 대비하여 WITH ROLLBACK IMMEDIATE를 추가하였음). sp_renamedb 프로시저를 사용하여 DB명을 변경한다.
(3) DB제거
|
DROP DATABASE <DB명>[, <DB명2>,...<DB명n>] |
6. DB유지관리를 위한 DBCC사용
- DBCC는 여러 구문이 존재한다.
|
명령문범주 |
수행하는 작업 |
DBCC문 |
|
유지관리 |
DB, Index,파일그룹에 대한 유지관리 |
DEREINDEX, DBREPAIR, SHRINKDATABASE, INDEXDEFRAG, UPDATEUSAGE, SHRINKFILE, |
|
기타 |
행수준잠금설정, 메모리에서 DLL제거등 |
dllname(FREE), HELP, PINTABLE, UNPINTABLE, TRACEOFF, TRACEON, ROWLOCK |
|
상태 |
상태검사 |
INPUTBUFFER, OPENTRAN, PROCCACHE, OUTPUTBUFFER, SHOWCONTIG, SQLPERF SHOW_STATISTICS, TRACESTATUS, USEROPTIONS |
|
유효성검사 |
DB,Table,Index,Catalog,파일그룹,시스템테이블에 대한 유효성 검사, DB페이지 할당 |
CHECKALLOC, CHECKCATALOG, CHECKCONSTRAINTS, CHECKDB, CHECKFILEGROUP, CHECKINDENT, CHECKTABLE, NEWALLOC |
(1) CHECKDB
- 모든 페이지의 연결과 크기, 할당과 구조적 무결성을 검사한다. 항목은 아래와 같다.
# 인덱스와 데이터페이지가 제대로 연결되어있는지 검사
# 인덱스 정렬 순서가 제대로 되어있는지 검사
# 포인터가 일치하는지 검사
# 각 페이지의 데이터가 적절한지 검사
# 페이지 오프셋이 적절한지 검사
CHECK DB 구문
|
DBCC CHECKDB ( 'database_name' [, NOINDEX | { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) [ WITH { [ ALL_ERRORMSGS] [, NO_INFOMSGS][, TABLOCK] [,ESTIMATEONLY][,PHYSICAL_ONLY]}] |
1) NOINDEX : 사용자 테이블의 비 클러스터형 인덱스에 대한 검사를 skip. 수행시간을 감소
|
*클러스터형 인덱스 : 해당 키 값을 바탕으로 테이블(or뷰)의 행을 정렬하고 저장. 데이터행이 정렬되어 저장될때만 클러스터형 인덱스가 됨 *비클러스터형 인덱스 : 데이터의 행으로부터 독립적. |
2) REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
- REPAIR_FAST : 비클러스터 인덱스를 복구하는 것 처럼, 사소하고 시간이 적게 걸리는 복구작업
- REPAIR_REBUILD : REPAIR_FAST및, 인덱스 재작성 같은 시간이 소요되는 복구를 포함
- REPAIR_ALLOW_DATA_LOSS : REPAIR_REBUILD및, 할당오류나 행, 페이지 오류, 손상된 개체 삭제를 수정하기 위한 행과 페이지의 할당 및 취소가 포함됨. 데이터의 일부 손실이 발생할 수 있음
3) ALL_ERRORMSGS : 개체당 오류 수제한 없이 표시(아닐경우 객체당 200개 한정)
4) NO_INFOMSGS : 모든 정보 메시지와 사용한 공간 보고서를 표시하지 않음
5) TABLOCK : 공유테이블 잠금을 사용하여 더 빠른 수행을 함
6) ESTIMATEONLY : 검사는 수행하지 않고, checkdb의 실행에 필요한 tempDB의 예상크기와 옵션을 표시
7) PHYSICAL_ONLY : DB의 실제 일관성 검사의 오버헤드를 줄이기 위한 목적. 조각난 페이지나 HW오류를 찾음
(2) CHECKTABLE
CHECKDB와 비슷하다. (실행 구문도 비슷). 그러나 DB가 아닌 테이블이 대상
|
DBCC CHECKTABLE ( '테이블명(or 뷰 명)' [, NOINDEX | { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS][, TABLOCK] [,ESTIMATEONLY][,PHYSICAL_ONLY]}] |
(3) CHECKALLOC, NEWALLOC : 디스크 공간의 할당 구조의 일관성을 검사
|
DBCC CHECKALLOC ( 'DB명' [, NOINDEX | { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS][, TABLOCK] [,ESTIMATEONLY]}] |
(4) CHECKCATALOG : 시스템 테이블의 일관성과 테이블간 일치하지 않는 내용을 검사
|
DBCC CHECKCATALOG ( 'DB명') [ WITH NO_INFOMSGS] |
(5) 그외의 DBCC구문
1) CHECKINDENT : 자동증가속성의 초기값을 재설정 할 수 있다.
2) CLEANTABLE : 삭제된 가변길이 열의 공간을 반환
3) DBREINDEX : 테이블의 특정 또는 전체 인덱스를 재 작성한다.
4) DBREPAIR : 손상된 DB를 삭제한다.
5) DROPCLEANBUFFERS : 버퍼풀에서 빈 버퍼를 모두 제거.
6) FREEPROCCACHE : 프로시저 캐시를 모두 비움
7) INDEXDEFRAG : 인덱스를 조각모음함. 이 기능은 사라질 예정. ALTER INDEX를 사용
(http://msdn.microsoft.com/ko-kr/library/ms177571.aspx)
8) INPUTBUFFER : 마지막으로 보낸 쿼리를 조회한다.
9) OUTPUTBUFFER : SPID의 현재 출력 버퍼를 반환 (16진 및 ASCII)
10) OPENTRAN : 지정한 DB에서 가장 오래된 활성 트랜젝션에 대한 정보를 표시
11) PINTABLE / UNPINTABLE : 페이지가 메모리에서 유지되도록 함. 이 테이블의 데이터가 많이 크면 메모리 문제가 발생할 수 있으므로, 가능한한 작게 표시한다.
12) PROCCACHE : 프로시저 캐시에 대한 정보를 반환
13) ROWLOCK : 6.5이전버전에서 행을 잠글 때 사용했음.
14) SHOW_STATISTICS : 특정 대상의 현재 배포 통계를 반환한다.
15) SHOWCONTIG : 테이블이나 뷰의 인덱스의 조각화 정보를 표시
16) SQLPERF : 트랜잭션 로그 공간의 사용에 대한 통계를 제공
17) TRACEON / TRACEOFF / TRACESTATUS : 추적플러그의 설정/해제/상태를 표시
18) UPDATEUSAGE : 카탈로그,뷰의 부정확한 페이지와 행 개수를 보고하고 수정함
19) USEROPTIONS : 현재 설정된 SET옵션을 반환
8. 데이터베이스 옵션 설정
- 각 데이터베이스별로 옵션을 설정하여 특징을 결정할 수 있다. 크게 5가지의 범주로 구분된다.
(1) 자동옵션 : 자동으로 동작되는 옵션을 제어
1) AUTO_CLOSE : ON일경우. 사용자가 종료하고, 모든 프로세스가 완료될 때, 리소스를 해제한다.
2) AUTO_CREATE_STATISTICS : ON일 경우. 조건자에서 사용되는 열에 대해 자동으로 통계가 생성됨.
3) AUTO_UPDATE_STATISTICS : ON일경우. 테이블의 데이터가 변경되면 통계값도 변경한다.
4) AUTO_SHRINK : ON일경우. 주기적으로 검사하여 빈 공간을 축소한다.
(2) 커서옵션 : 동작과 범위를 제어
1) CURSOR_CLOSE_ON_COMMIT : ON일때, 트랜잭션 커밋 시, 커서가 자동으로 닫힘.
2) CURSOR_DEFAULT LOCAL|GLOBAL : 커서의 범위를 로컬과 전역으로 설정
(3) 복구옵션 : DB의 복구 모델을 제어
1) RECOVERY FULL : 모든 작업에 대한 로그. 전체 복구가 가능하다
2) RECOVERY BULK_LOGGED : SELECT INTO, CREATE INDEX, 대량 데이터 로드에 대한 로깅이 최소화됨. 로그 공간은 적지만 FULL에 비해 복구시 손실 위험이 더 크다.
3) RECOVERY SIMPLE : 마지막 전체 DB의 백업본으로만 복구가 가능하다.
4) TORN_PAGE_DETECTION : 완료되지 않은 I/O작업을 검색할 수 있다. (조각난 데이터)
(4) 상태옵션
1) OFFLINE | ONLINE : OFFLINE일 경우 DB를 닫고 오프라인으로 표시됨(사용을 못하게 됨)
2) READ_ONLY | READ_WRITE : READ_ONLY는 읽기전용일 때 사용
3) SINGLE_USER | RESTRICTED_USER | MULTI_USER
- SINGLE : 한명의 사용자만 접근, RESTRICTED : 제한된 사용자(DBO급), MULTI : 여러사용자 허용
(5) WITH <termination>
-위 절은 DB가 상태가 전환될 때, 완료되지 않은 트랜잭션을 종료하는 방법을 지정한다. 예를 들어 ALTER DATABASE에서, 다음과 같은 구문이 올 수 있다.
1) ROLLBACK AFTER <초> : 지정한 시간(초)를 대기한 후, 나머지 완료되지 않은 트랜잭션은 롤백
2) ROLLBACK IMMEDIATE : 수행 즉시, 완료되지 않은 트랜잭션을 롤백
3) 이 옵션이 없으면 다른 트랜잭션이 커밋되거나 롤백될 경우까지 대기.
(6) Nonlogged Operation
- 로그를 남기지 않는 동작들. 이 동작은 로그가 없어, 복구가 불가하다. 이러한 동작 수행 후에는 백업을 하는것이 좋음. Bulk로드, SELECT INTO, WRITETEXT, UPDATETEXT문 등이 있다.
-출처 [SQL Server advanced programming]
댓글