[출처] DBGuide
데이터베이스 관리
시스템 데이터베이스 목록
데이터베이스를 만들기 위해서는 먼저 데이터베이스 이름, 크기, 데이터베이스 저장에 사용할 파일 및 파일 그룹, 소유자를 결정해야 합니다. 소유자는 데이터베이스를 만든 사용자입니다.
CREATE DATABASE 문을 실행하거나 SQL Server Management Studio를 사용하여 새로운 데이터베이스와 데이터베이스 저장에 필요한 파일들을 만들 수 있습니다. 새로운 데이터 베이스를 만들면 model 데이터베이스에 있는 모든 사용자 정의 개체가 새로 만든 데이터 베이스로 복사되며, 크기 매개 변수를 지정하지 않고 CREATE DATABASE
모든 SQL Server 2005 데이터베이스는 기본적으로 데이터 파일과 로그 파일로 구성됩니다. 데이터 파일에는 테이블, 인덱스, 저장 프로시저, 뷰 등의 개체와 데이터가 저장될 수 있으며, 로그 파일에는 데이터베이스의 트랜잭션을 복구하는 데 필요한 정보가 저장됩니다.
데이터베이스를 저장하는 데 사용되는 파일 형식은 주 파일, 보조 파일, 트랜잭션 로그등 3가지이며, 주 파일과 보조 파일은 데이터 파일이고, 트랜잭션 로그는 로그 파일입니다. 데이터 파일은 할당 및 관리를 간편하게 수행하기 위해 파일 그룹으로 그룹화할 수 있습니다.
다음은 데이터베이스를 저장하는 데 사용되는 3가지 파일 형식의 설명입니다.
다음은 데이터베이스 생성 시의 권고 사항입니다.
데이터 파일과 트랜잭션 로그 파일은 물리적으로 서로 다른 드라이브에 배치합니다.
모든 데이터베이스는 최소 하나의 주 데이터 파일(Primary Data File)과 하나의 트랜잭션 로그 파일로 구성되며, 하나의 데이터베이스에 로그 파일이 두 개 이상 있을 수 있습니다. 디스크 손상 시의 복구 가능성과 성능을 위하여 데이터 파일과 트랜잭션 로그 파일은 물리적으로 서로 다른 디스크에 배치합니다.
[따라하기] 데이터 파일과 로그 파일을 지정하여 데이터베이스 만들기
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'D:\DBData\saledat.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = Sales_log,
FILENAME = 'E:\DBData\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
GO파일 그룹을 사용하여 데이터베이스를 생성합니다.
사용자 정의 파일 그룹을 만들어 데이터 파일을 그룹화함으로써 관리, 데이터 할당 및 배치를 간편하게 수행할 수 있습니다. 주(PRIMARY) 파일 그룹의 주 데이터 파일에는 메타 데이터만 저장하고, 사용자 정의 파일 그룹에 사용자 개체들을 저장하는 것을 권고합니다. 사용자 정의 파일 그룹을 도입하게 되면, 기본 파일 그룹을 주 파일 그룹에서 사용자 정의 파일 그룹으로 변경합니다. 그리고 테이블이나 인덱스를 생성할 때 적절한 사용자 파일 그룹을 지정해야 하며, 파일 그룹을 지정하지 않은 상태에서 테이블 또는 인덱스를 생성하면 기본 파일 그룹으로 지정된 파일 그룹에 생성됩니다.
[따라하기] 파일 그룹이 있는 데이터베이스 만들기
- 데이터베이스를 생성합니다.
USE master; GO CREATE DATABASE Sample /* 데이터베이스 이름 */ ON Primary ( /* 주 파일 그룹 */ NAME = Sample_Pri_dat, /* 데이터 파일 이름 */ FILENAME = 'D:\DBdata\Sample_Pri_dat.mdf', /* 데이터 파일 위치 */ SIZE = 200 MB, /* 데이터 파일 초기 크기 */ MAXSIZE = 1 GB, /* 데이터 파일 최대 크기 */ FILEGROWTH = 20 MB), /* 데이터 파일 증가량 */ FILEGROUP SamplesFG1 ( /* 사용자 정의 파일 그룹 #1 */ NAME = Sample_FG1_dat, FILENAME = 'E:\DBdata\Sample_FG1_dat.ndf', SIZE = 200 MB, MAXSIZE = 1 GB, FILEGROWTH = 20 MB), FILEGROUP SamplesFG2 ( /* 사용자 정의 파일 그룹 #2 */ NAME = Sample_FG2_dat, FILENAME = 'F:\DBdata\Sample2_FG2_dat.ndf', SIZE = 200 MB, MAXSIZE = 1 GB, FILEGROWTH = 20 MB) LOG ON ( /* 로그 파일 */ NAME = Sample_log, FILENAME = 'G:\DBlog\Sample_log.ldf', SIZE = 10 MB, MAXSIZE = 50 MB, FILEGROWTH = 5 MB); GO - 주 데이터 파일에는 메타 데이터만 저장되도록, 기본 파일 그룹을 사용자 정의 파일 그룹 중 주로 사용하는 파일 그룹으로 변경합니다.
ALTER DATABASE Sample MODIFY FILEGROUP [SamplesFG1] DEFAULT; GO - 기본 파일 그룹을 확인합니다.
USE Sample; GO SELECT * FROM sys.filegroups WHERE is_default = 1; GO - 테이블을??룹을 지정하면 됩니다.
CREATE TABLE TestTbl (col1 int, col2 int) ON SamplesFG1; GO - 클러스터형 인덱스를 생성합니다. 클러스터형 인덱스는 기본적으로 데이터와 동일한 파일 그룹에 생성되므로 파일 그룹을 지정할 필요가 없습니다. 만일 이 예제에서 클러스터형 인덱스를 만드는 CREATE INDEX 문의 ON <파일 그룹> 절에 SamplesFG2를 지정하면 데이터도 SamplesFG2 파일 그룹으로 이동합니다.
CREATE CLUSTERED INDEX CX_col1 ON TestTbl (col1); GO - 비클러스터형 인덱스는 SamplesFG2 파일 그룹에 생성합니다.
CREATE NONCLUSTERED INDEX NX_col2 ON TestTbl (col2) ON SamplesFG2; GO - 인덱스 정보를 조회하면 다음과 같이 지정한 파일 그룹에 배치되어 있는 것을 확인할 수 있습니다.
데이터 파일은 내결함성이 제공되며 해당 시스템에 적합한 RAID에 배치합니다.
데이터 파일은 RAID 1+0 또는 RAID 5로 구성된 드라이브에 배치하는 것이 일반적입니다. DBA는 RAID와 각 옵션의 장단점에 대하여 명확히 이해하고 디스크 드라이브가 해당 시스템에 최적으로 구성될 수 있도록 하드 디스크 구성에 관여하는 것이 바람직합니다. 하드웨어 RAID가 소프트웨어 RAID에 비해 비용이 많이 들지만 성능을 위하여 하드웨어 RAID를 사용하는 것을 권고하며, 비용이 문제가 되지 않는다면 높은 고가용성과 쓰기 작업의 성능을 위하여 데이터베이스의 데이터 파일은 디스크 스트라이핑과 미러링의 결합인 RAID 1+0로 구성된 드라이브에 배치하는 것을 권고합니다. 유의할 사항은, 데이터베이스 파일과 운영 시스템의 페이징 파일을 동일한 디스크에 배치하는 것은 어떤 경우라도 피해야 합니다.
트랜잭션 로그 파일은 RAID 1로 구성된 드라이브에 배치합니다.
트랜잭션 로그를 별도의 미러된 드라이브에 배치하면 좋은 성능과 내결함성(Fault Tolerance)을 확보할 수 있습니다. 복제가 구성되어 있거나 트리거가 빈번하게 수행되는 경우가 아니라면, 트랜잭션 로그에는 주로 순차적인 쓰기 작업이 수행되며 롤백이 수행되는 경우에만 읽기 작업이 수행됩니다. 그러므로 쓰기 작업의 성능을 위하여 트랜잭션 로그 파일은 RAID 1에 저장 할 것을 권고합니다. RAID 5는 RAID 1에 비해 디스크 구입에 드는 비용는 적지만, 한 번의 쓰기 작업에 대하여 네 번의 I/O 작업이 필요하기 때문에 쓰기 작업의 성능이 RAID 1에 비해 좋지 않으며 만일 두 개 이상의 디스크 드라이브가 동시에 손상되면 전체 어레이가 사용할 수 없게 되는 단점이 있습니다.
데이터베이스를 만들 때 데이터 파일과 트랜잭션 로그 파일은 향후 예상되는 데이터 크기를 고려하여 충분한 크기로 생성합니다. 데이터 파일, 트랜잭션 로그 파일 모두 충분한 크기로 생성합니다.
파일이 증가하는 동안에는 새로운 익스텐트의 할당이 중단되므로 새로운 공간 할당을 필요로 하는 INSERT 작업이나 UPDATE 작업의 수행이 중단됩니다. 그러므로, 트랜잭션 로그를 자동 증가하도록 옵션을 설정하기는 하되, 파일이 자동 확장되지 않도록 충분한 크기로 생성합니다. 데이터 파일의 초기 크기는 용량 산정 작업을 통하여 적절한 크기를 결정하고, 트랜잭션 로그의 초기 크기는 트랜잭션 로그 백업을 수행한 후 다음 로그 백업이 수행되기 전까지 발생하는 로그를 저장 하기에 충분한 크기로 생성합니다. 트랜잭션 로그 파일에 대하여 여러 번의 자동 증가가 발생하게 되면, 여러 개의 가상 로그 파일들로 조각화되어 로그 관련 작업의 성능에도 좋지 않은 영향을 미칩니다. 만일 데이터베이스가 이미 만들어져 운영 중인데, 데이터베이스의 초기 크기가 작아서 잦은 확장이 발생하고 있다면 파일의 크기를 충분한 크기로 확장해 주어야 합니다. 참고로, 빈번한 자동 확장으로 인하여 이미 가상 로그 파일의 수가 많아진 경우에는 [데이터베이스 축소]를 참조하여 가상 로그 파일의 수를 줄여 줄 것을 권고합니다.
[따라하기] 데이터베이스 파일 확장하기
ALTER DATABASE Sample
MODIFY FILE
(NAME = Sample_dat,
SIZE = 500 MB);
GO파일이 증가할 수 있는 최대 크기를 지정할 수 있습니다.
데이터 파일이 최대 크기에 도달하거나 드라이브에 파일 공간을 모두 사용한 경우에는 데이터 파일에 대한 쓰기 작업이 오류가 발생합니다. 지속적으로 데이터가 증가하는 데이터베이스의 경우에는 최대 크기에 도달해서 오류가 발생하지 않도록 최대 크기를 지정하지 않는 것이 좋고, 비정상적으로 특정 데이터베이스 파일의 크기가 증가하여 디스크 공간을 모두 사용함으로써 다른 데이터베이스들에까지 영향을 미치는 것을 방지하고자 한다면 최대 크기를 지정하는 것이 좋습니다.
파일의 최대 크기를 지정하려면 CREATE DATABASE 문의 MAXSIZE 매개 변수를 사용하거나 SQL Server Management Studio의 데이터베이스 속성 창의 파일 탭에서 자동 증가 열에 있는 (…) 버튼을 클릭하여 제한된 파일 증가 옵션을 선택한 후 최대 파일 크기를 설정할 수 있습니다.
[따라하기] 데이터베이스 파일의 최대 크기 설정하기
ALTER DATABASE Sample
MODIFY FILE
(NAME = Sample_dat,
MAXSIZE = 1 GB);
GO[따라하기] 데이터베이스 파일의 최대 크기 확인하기
EXEC sys.sp_helpdb Sample;
GO
-- 또는
EXEC Sample.sys.sp_helpfile;
GO
-- 또는
SELECT * FROM Sample.sys.database_files;
GO파일이 자동으로 증가하도록 설정하는 경우에는 자동 증가 크기를 적절하게 설정합니다.
파일의 크기가 매우 작거나 매우 큰 경우에는 파일 증가 단위를 적절한 크기의 MB 단위로 설정하는 것을 권고합니다. SQL Server 2005에서는 파일 증가 단위를 지정하지 않으면 데이터 파일은 기본값이 1MB 단위로, 로그 파일은 10% 단위로 증가하도록 설정됩니다.
이러한 기본 설정을 그대로 두면, 저장하고자 하는 데이터의 크기가 매?생하게 되는 문제가 발생합니다. 10% 단위로 증가하도록 설정된 로그 파일의 경우, 로그 파일의 크기가 매우 크다면 10%에 해당하는 파일의 크기도 커지게 됩니다. 그러므로, 자동 증가가 완료되기까지 소요시간이 오래 걸리고, 이로 인하여 트랜잭션 로그를 발생시키는 작업들이 대기 또는 실패하는 문제가 발생할 수 있습니다.
반대로 로그 파일의 크기가 매우 작은 경우 10%씩 자동 확장하면, 증가되는 크기가 작아서 자동 증가가 빈번하게 발생하게 됩니다. 로그 파일의 경우에 작은 크기의 자동 확장이 빈번하게 발생하면 여러 개의 작은 가상 로그 파일(VLF)들로 단편화가 발생하게 되어 성능을 저하 시킬 수 있으며, 이로 인하여 데이터베이스 시작뿐 아니라 로그 백업 및 복원 작업이 느려질 수 있습니다. 저자의 경험에 비추어 보면 데이터베이스 파일의 초기 크기를 적절하게 설정하는 것도 중요하지만, 만일의 경우에 발생할 자동 확장이 적절한 크기로 적절한 시간 안에 완료될 수 있도록 자동 증가 값을 %가 아닌 MB 단위로 지정하는 것을 권고합니다. 가상 로그 파일의 수가 지나치게 많은 경우에는 가상 로그 파일의 수를 줄이는 작업을 수행 하는 것이 좋습니다. 작업 방법은 [트랜잭션 로그 파일 축소]를 참조하십시오.
[따라하기] 데이터베이스의 데이터 파일 증가율을 100MB로 변경하기
ALTER DATABASE Sample
MODIFY FILE
(NAME = Sample_dat,
FILEGROWTH = 100MB);
GOtempdb는 I/O가 빠른 디스크 드라이브에 배치할 것을 권고합니다.
tempdb는 I/O가 빠른 쪽에 배치하는 것이 성능을 위해 좋습니다. 성능을 위하여 tempdb를 여러 디스크에 스트라이핑하거나 자주 사용되는 사용자 데이터베이스와 물리적으로 격리된 디스크에 배치하는 방안도 고려해 볼 수 있습니다. 특히 tempdb를 매우 많이 사용하는 대규모 시스템이라면 tempdb를 별도의 디스크 셋에 배치하면 성능 향상 효과를 얻을 수 있습니다.
SQL Server 시스템의 운영 체제 드라이브는 RAID 1로 구성하며, 페이징 파일은 데이터 베이스의 데이터, 로그, tempdb가 있는 곳에는 배치하지 않아야 합니다. 이렇게 구성함으로써 디스크 오류에 신속하게 대응하여 복구할 수 있습니다. 이 때, 부트 디스크는 미러를 이용하여 부팅 가능해야 합니다. 시스템 백업을 동일 서버에 저장해야 한다면 데이터 파일이나 로그 파일이 없는 별도의 디스크에 저장합니다.
데이터베이스 생성이 실패하는 원인에는 여러 가지가 있지만 주로 다음과 같은 문제로인하여 새로운 데이터베이스의 생성이 실패합니다.
- model 데이터베이스가 사용 중인 경우
model 데이터베이스에 대한 배타적 잠금을 확보하지 못하면 새로운 데이터베이스 를 만들 수 없습니다. model 데이터베이스를 사용하는 프로세스의 수행이 완료되 기를 기다렸다가 수행이 완료된 다음에 데이터베이스를 생성하거나, model 데이터베이스를 사용중인 프로세스를 강제로 중지한 후에 데이터베이스 생성 작업을 재시도합니다. - 데이터베이스 파일의 물리적인 위치를 잘못 지정한 경우
CREATE DATABASE 문에서 지정한 폴더가 실제로 존재하는 폴더인지 확인합니다. 만일 지정한 폴더가 없다면 폴더를 만들어 주고 재시도합니다. - 디스크 공간이 부족한 경우
지정한 드라이브에 지정한 크기로 물리적 파일을 생성하는데 충분한 여유 공간이 있는지 확인합니다. - CREATE DATABASE 문을 실행한 사용자에게 새로운 데이터베이스를 생성할 수 있는 권한이 없는 경우
새로운 데이터베이스를 생성하기 위해서는 sysadmin 역할 또는 dbcreator 역할의 구성원이어야 합니다. 이 역할의 구성원에게 작업을 요청하거나, 주기적으로 작업 이 필요하다면 해당 사용자를 dbcreator 역할의 구성원으로 등록하면 됩니다. - 동일한 이름의 데이터베이스가 이미 존재하는 경우
sp_helpdb를 수행하거나 sys.databases 카탈로그 뷰를 참조하여 확인합니다. 만약 이미 존재하는 데이터베이스가 불필요하다면 ALTER DATABASE 문을 사용하여 기존 데이터베이스를 다른 이름으로 변경하거나 DROP DATABASE 문을 사용하여 동일한 이름의 데이터베이스를 삭제한 후에 재시도합니다. - 동일한 이름의 파일이 이미 존재하는 경우
존재하지 않는 파일 이름을 지정하고 재시도합니다. - 트랜잭션 내에서 CREATE DATABASE 문을 실행한 경우
CREATE DATABASE 문은 자동 커밋 모드(기본 트랜잭션 관리 모드)에서 실행해야 하며 명시적 또는 암시적 트랜잭션에서는 허용되지 않습니다.
[따라하기] 30초후, 완료되지 않은 트랜잭션을 롤백하고 단일 사용자 모드로 변경하기
USE Test;
GO
ALTER DATABASE Test
SET SINGLE_USER
WITH ROLLBACK AFTER 30; -- 30초가 경과한 후에 롤백
GO
USE master;
GO
DROP DATABASE Test;
GO데이터베이스 파일 이동
ALTER DATABASE 문을 사용하여 파일 정보를 변경하거나 데이터베이스 분리 및 연결 시스템 저장 프로시저를 사용하여 데이터베이스를 이동할 수 있습니다. 데이터베이스를 이동할 때는 분리 및 연결 작업보다 ALTER DATABASE를 이용하는 것이 좋습니다. ALTER DATABASE 문의 FILENAME 절에 새 파일 위치를 지정하면 리소스 데이터베이스 파일을 제외한 시스템 데이터베이스와 사용자 데이터베이스의 파일을 이동할 수 있습니다. 이 절차를 수행하려면 ALTER DATABASE 문을 실행할 데이터베이스 파일의 논리 이름을 알고 있어야 하며, sys.master_files 카탈로그 뷰에서 name 열을 조회하면 논리 파일이름을 확인할 수 있습니다.
[따라하기] ALTER DATABASE 문을 사용하여 사용자 데이터베이스 파일 이동하기
C 드라이브에 주 데이터 파일과 트랜잭션 로그 파일이 있는 데이터베이스를 주 데이터 파일은 D 드라이브, 트랜잭션 로그 파일은 E 드라이브로 이동하는 예제입니다.
- 데이터베이스 파일의 논리 이름을 확인합니다.
SELECT file_id, name, physical_name FROM sys.master_files WHERE database_id = db_id('Sample'); GO - 사용자들에게 공지하여 작업하고자 하는 데이터베이스에 대한 연결을 해제하도록 합니다. 만일 해제되지 않는 연결이 남아 있어서 문제가 되는 경우에는 다음과 같은 방법을 사용하여 연결을 해제할 수 있습니다.
USE Sample; GO ALTER DATABASE Sample SET SINGLE_USER WITH ROLLBACK AFTER 60; GO - 데이터베이스를 오프라인 상태로 만듭니다.
ALTER DATABASE Sample SET OFFLINE; GO - 데이터베이스 파일들을 새로운 위치로 이동합니다.
- 데이터베이스 파일 위치 정보를 변경합니다.
ALTER DATABASE Sample MODIFY FILE ( NAME = Sample_dat, FILENAME = 'D:\DBdata\Sample_dat.mdf'); GO ALTER DATABASE Sample MODIFY FILE ( NAME = Sample_log, FILENAME = 'E:\DBlog\Sample_log.ldf'); GO - 데이터베이스를 온라인 상태로 변경합니다.
ALTER DATABASE Sample SET ONLINE; GO
만일 데이터베이스를 다른 서버로 이동하고자 하는 경우에는 분리 및 연결 작업을 사용합니다.
[따라하기] 분리 및 연결 작업을 사용하여 사용자 데이터베이스 이동하기
- 해당 데이터베이스에 연결되어 있는 연결을 모두 비 연결 상태로 만들고, 단일 사용자 모드로 설정합니다. 다음은 600초 후에 모든 작업들을 롤백시키고, 연결을 끊는 예제 입니다.
USE Sample; GO ALTER DATABASE Sample SET SINGLE_USER WITH ROLLBACK AFTER 600; GO - 해당 데이터베이스의 모든 데이터 파일과 트랜잭션 로그 파일의 경로를 확인합니다.
EXEC sp_helpdb Sample; GO - 데이터베이스와 파일을 분리합니다.
USE master; GO EXEC sp_detach_db 'Sample', 'true'; GO - 데이터베이스 파일들을 원하는 위치에 복사합니다.
- 새로운 위치의 파일을 지정하여 데이터베이스와 연결합니다.
CREATE DATABASE Sample ON PRIMARY (FILENAME='d:\DBdata\Sample_dat.mdf') LOG ON (FILENAME='e:\DBlog\Sample_log.ldf') FOR ATTACH ; GO
리소스 데이터베이스 파일을 이동하면 SQL Server 가 시작되지 않으므로 주의합니다. 또한 리소스 데이터베이스는 master 데이터베이스와 동일한 위치에 존재해야 하므로, master 데이터베이스를 이동한 경우에는 리소스 데이터베이스도 master 데이터베이스 와 동일한 위치로 이동해야 합니다.
데이터베이스 축소
DBCC SHRINKDATABASE 또는 DBCC SHRINKFILE를 사용하여 수동으로 데이터베이스를 축소할 수 있습니다. 데이터베이스의 특정 데이터 파일이나, 트랜잭션 로그 파일의 크기를 축소하는 경우에는 DBCC SHRINKFILE을 사용합니다. 데이터베이스의 AUTO_SHRINK 옵션을 TRUE로 설정하면 파일에서 사용되지 않는 공간이 25% 이상이 되면 파일이 자동으로 축소됩니다. 그렇지만, 축소가 필요할 때에는 DBCC SHRINKDATABASE 문 또는 DBCC SHRINKFILE 문을 사용하여 수동으로 데이터베이스를 적절한 크기로 축소하는 것을 권고 합니다.
파일을 지정하지 않고 특정 데이터베이스의 모든 데이터와 로그 파일을 축소하고자 하는 경우에는 DBCC SHRINKDATABASE를 사용합니다.
[따라하기] 데이터베이스 전체 크기 중10% 여유공간이 남도록 데이터베이스 축소하기
DBCC SHRINKDATABASE (Sample, 10); GO특정 데이터베이스의 특정 파일을 축소하고자 하는 경우에는 DBCC SHRINKFILE을 사용 합니다. DBCC SHRINKFILE 명령어를 사용하면 특정 파일의 크기만 축소할 수 있으며, 데이터 파일이나 로그 파일을 초기 크기보다 더 작게 축소하고자 하는 경우에는 DBCC SHRINKFILE 명령어를 사용합니다.
[따라하기] Sample 데이터베이스의 Sample_dat 파일을 10MB로 축소하기
USE Sample; DBCC SHRINKFILE (Sample_dat, 10); GO트랜잭션 로그 파일 축소하기
DBCC SHRINKDATABASE와 DBCC SHRINKFILE에서 사용할 수 있는 TRUNCATE 옵션은 데이터 파일에만 적용되며 로그 파일에는 적용되지 않습니다. 로그 파일에 대하여 이 옵션을 사용한다고 해서 로그가 삭제되는 것은 아닙니다. 로그 파일은 즉시 크기가 줄어들지 않으며 트랜잭션 로그를 백업하거나 삭제할 때 크기가 줄어듭니다. 트랜잭션 로그 파일의 크기 축소는 가상 로그 파일의 크기 단위로 이루어집니다. 만일 로그 파일의 공간이 부족하여 빈번하게 자동 증가가 발생하면, 로그 파일이 많은 수의 가상 로그 파일들로 조각화되어 데이터베이스 시작뿐 아니라 로그 백업 및 복원 작업이 느려질 수 있다는 점을 DBA는 유의해야 합니다. 자동 확장을 예방하기 위해서는 사전에 로그 파일의 크기를 충분한 크기로 만들어 두어야 하며, 만일 이미 가상 로그 파일의 수가 지나치게 많아진 경우에는 가상 로그 파일 수를 줄여주는 작업을 수행하고 트랜잭션 로그 파일을 적절한 크기로 변경할 것을 권고합니다.
[따라하기] 가상 로그 파일 개수 줄이기
Sample 데이터베이스의 Sample_log 로그 파일의 가상 로그 파일을 제거하여, 트랜잭션 로그 파일을 축소합니다.
- 가상 로그 파일의 개수를 확인합니다. 결과 행의 수가 가상 로그 파일의 수입니다.
USE Sample; GO DBCC LOGINFO; GO - 트랜잭션 로그 백업을 수행합니다. 로그 백업을 받을 수 없는 경우에는 로그를 잘라냅니다. 로그를 잘라낸다고 해서 실제 로그 파일의 크기가 줄어들지는 않습니다. 그러나 로그를 잘라내면 논리 로그의 크기가 줄어들고 논리 로그 부분을 포함하지 않는 가상 로그가 비활성으로 표시합니다. 로그 축소 작업이 수행되면 요청된 크기만큼 로그 파일이 충분히 축소될 수 있도록 비활성 가상 로그를 제거하게 되므로, 사전에 로그를 백업하거나 잘라내는 것이 좋습니다.
BACKUP LOG Sample TO DISK='D:\DBBackup\Sample_Log.bak'; GO -- 또는 BACKUP LOG Sample WITH NO_LOG; GO - 트랜잭션 로그 파일의 크기를 가능한 한 작은 크기로 축소합니다.
DBCC SHRINKFILE (Sample_log, TRUNCATEONLY); GO - 로그 파일의 크기를 적절한 크기로 변경합니다.
ALTER DATABASE Sample MODIFY FILE ( NAME = 'Sample_log' , SIZE = 30MB); GO
데이터베이스 옵션 설정
SQL Server Management Studio에서 데이터베이스 옵션을 확인하고 설정하는 것이 가능 하지만, SQL 명령어를 사용하여 데이터베이스 옵션을 확인하거나 변경할 경우가 종종 발생합니다.
ALTER DATABASE 문을 사용하면 데이터베이스 옵션을 설정할 수 있습니다. 데이터베이스 옵션을 설정하면 수정 사항이 즉시 반영됩니다. 새로 만들어지는 모든 데이터베이스에 적용 되는 데이터베이스 옵션의 기본값을 변경하려면 model 데이터베이스에서 해당 데이터베이스 옵션을 변경합니다.
sp_dboption을 사용하여 데이터베이스의 옵션을 변경하는 것이 가능하지만 sp_dboption 은 하위 버전과의 호환을 위하여 지원되는 기능이며, SQL Server 2005 온라인 설명서에 의하면 이 기능은 SQL Server 다음 버전에서 제거된다고 기술되어 있습니다. 그러므로 데이터베이스 옵션을 변경하고자 하는 경우에는 sp_dboption 대신 ALTER DATABASE 문을 사용할 것을 권고합니다. 만일 현재 sp_dboption을 사용하는 응용 프로그램이 있다면 응용 프로그램도 수정하기 바랍니다.
데이터베이스 옵션에 대한 현재 설정을 검색하려면 sys.databases 카탈로그 뷰 또는 DATABASEPROPERTYEX를 사용합니다.
[따라하기] 데이터베이스 복구 모델를 대량 로그 복구 모델로 변경하기
USE master; GO ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED; GO[따라하기] 데이터베이스를 읽기 전용으로 변경하기
USE master; GO ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE AdventureWorks SET READ_ONLY; GO ALTER DATABASE AdventureWorks SET MULTI_USER; GO[따라하기] 자동 통계 갱신 데이터베이스 옵션 비활성화하기
USE master; GO ALTER DATABASE Adventureworks SET AUTO_UPDATE_STATISTICS OFF; GO[따라하기] 통계 자동 업데이터가 필요한 경우 컴파일 전에 통계가 업데이트되기를 기다리지 않도록 데이터베이스 옵션 설정하기
USE master; GO ALTER DATABASE Adventureworks SET AUTO_UPDATE_STATISTICS ON; GO ALTER DATABASE Adventureworks SET AUTO_UPDATE_STATISTICS_ASYNC ON; GO[따라하기] RCSI 데이터베이스 옵션 설정하기
USE master; GO ALTER DATABASE Adventureworks SET READ_COMMITTED_SNAPSHOT ON; GO SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = N'Adventureworks'; GO[따라하기] 스냅숏 트랜잭션 격리 수준 허용하기
USE master; GO ALTER DATABASE Adventureworks SET ALLOW_SNAPSHOT_ISOLATION ON; GO SELECT name, snapshot_isolation_state, snapshot_isolation_state_desc AS description FROM sys.databases WHERE name = N'Adventureworks'; GO데이터베이스 소유자 변경
데이터베이스가 만들어질 때 설정된 소유자를 추후 변경할 수 있습니다. master, model 또는 tempdb 시스템 데이터베이스의 소유자는 변경할 수 없습니다.
[따라하기] Sample 데이터베이스의 소유자를 'dbadmin'으로 변경하기
USE Sample; GO EXEC sp_changedbowner 'dbadmin'; GO데이터베이스 이름 변경
ALTER DATABASE MODIFY NAME 문을 사용하면 데이터베이스의 이름을 변경할 수 있습니다. 다른 사용자가 데이터베이스에 연결하지 않은 상태라면 ALTER DATABASE 문만 수행하면 이름이 변경되지만, 다른 사용자가 데이터베이스에 연결을 맺은 상태에서는 이름 변경이 실패합니다. 이 경우에는 데이터베이스의 이름을 변경하기 전에 데이터베이스를 단일 사용자 모드나 오프라인 모드로 변경한 상태에서 이름을 변경하면 됩니다.
sp_renamedb를 사용하여 데이터베이스의 이름을 변경할 수도 있지만, 온라인 설명서에 의하면 sp_renamedb는 다음 버전의 Microsoft SQL Server에서 제거된다고 기술되어 있습니다. 그러므로 이후로 데이터베이스 이름 변경 스크립트를 작성할 때에는 sp_renamedb 대신 ALTER DATABASE MODIFY NAME을 사용해야 하며, sp_renamedb 를 사용하는 응용 프로그램도 수정하기 바랍니다.
[따라하기] Sample 데이터베이스의 이름을 Sample_Rename으로 변경하기
- 모든 사용자들에게 Sample 데이터베이스에 대한 연결을 해제하도록 통보합니다. SQL Server Management Studio에서 해당 데이터베이스에 연결하고 있는 상황이 발생하지 않도록 SQL Server Management Studio를 닫습니다. 만일 데?용자 연결이 계속 해제되지 않는 상태라면 사용자 프로세스들을 강제로 종료시킬 수 있습니다.
- 데이터베이스를 단일 사용자 모드로 설정합니다.
USE master ALTER DATABASE Sample SET SINGLE_USER; GO - 데이터베이스 이름을 변경합니다.
ALTER DATABASE Sample MODIFY NAME = Sample_Rename; GO - 데이터베이스를 다중 사용자 모드로 원복합니다. 데이터베이스를 단일 사용자 모드로 그대로 둠으로써 응용프로그램에서 오류가 발생하는 경우가 종종 있습니다.
ALTER DATABASE Sample_Rename SET MULTI_USER; GO - 변경된 데이터베이스를 사용하는데 문제가 없는지 확인합니다.
데이터베이스 무결성 체크
지정한 데이터베이스에서 모든 개체의 할당과 구조적, 논리적 무결성을 검사합니다.
[구문]
DBCC CHECKDB
[ (
'database_name' | database_id | 0
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) ]
[ WITH {
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ] | [ , [ DATA_PURITY ] ]
}
]DBCC 기능을 사용하여 SQL Server의 상태를 확인할 수 있습니다. DBCC CHECKDB는 디스크에서 메모리로 할당된 각 페이지를 읽어야 확인할 수 있습니다. 시스템에 작업이 많은 경우 DBCC CHECKDB를 실행하면 다음 두 가지 이유 때문에 DBCC 성능이 저하될 수 있습니다.
첫 번째 이유는 사용 가능한 메모리가 부족하여 SQL Server 데이터베이스 엔진이 DBCC CHECKDB의 내부 데이터 중 일부를 tempdb 데이터베이스로 스풀링하기 때문입니다. tempdb 데이터베이스는 디스크에 위치하므로 데이터가 디스크에서 기록될 때 I/O 작업의 병목 상태로 인해 성능이 저하됩니다. 두 번째 이유는 DBCC CHECKDB가 디스크에서 데이터를 읽는 방식을 최적화하려고 하기 때문입니다. 또한 동일한 디스크를 사용하는 작업이 집중되는 경우 최적화가 상당히 저하되어 실행 속도가 느려지게 됩니다. 그러므로, DBCC CHECKDB는 운영 서버에서 서비스 중에 실행하지 않을 것을 권고합니다. 데이터베이스를 테스트 서버에 복원하여 DBCC CHECKDB를 실행하면 서비스에 지장없이 DBCC CHECKDB를 실행할 수 있습니다.
SQL Server 2000에서는 DBCC CHECKDB를 실행하면 기본적으로 테이블 레벨의 스키마 잠금을 사용하여 그로 인한 블로킹이 발생하고 로그 정보를 읽기 때문에 트랜잭션 로그 잘라 내기 작업이 블로킹될 수 있었습니다. SQL Server 2005는 데이터베이스 엔진에서 만든 내부 읽기 전용 데이터베이스 스냅숏에서 작동하기 때문에 이 명령이 실행될 때 블로킹(차단) 및 동시성 문제를 방지할 수 있습니다.
DBCC CHECKDB가 내부적으로 수행하는 작업을 간략히 정리하면 다음과 같습니다. 즉, DBCC CHECKDB를 실행하면 DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKTABLE이 자동으로 실행되므로 별도로 실행할 필요가 없습니다. 그렇지만 DBCC CHECKDB는 비활성화된 인덱스는 검사하지 않습니다.
- 데이터베이스에 대해 DBCC CHECKALLOC을 실행합니다.
- 데이터베이스의 모든 테이블 및 뷰에 대해 DBCC CHECKTABLE을 실행합니다.
- 데이터베이스에 있는 Service Broker 데이터의 유효성을 검사합니다.
- 데이터베이스에 대해 DBCC CHECKCATALOG를 실행합니다.
- 데이터베이스에 있는 모든 인덱싱된 뷰의 내용에 대한 유효성을 검사합니다.
SQL Server 2005의 sys.dm_exec_requests 카탈로그 뷰를 조회하면 DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE 명령의 현재 실행 단계와 진행률에 대한 정보를 확인할 수 있습니다. percent_complete 열은 명령의 완료 비율을 나타내고 command 열은 명령의 현재 실행 단계를 나타냅니다.
[따라하기] Sample 데이터베이스 내 모든 개체의 할당과 구조적 무결성 검사
DBCC CHECKDB ('Sample'); GO[따라하기] 데이터베이스가 SUSPECT 모드인 경우 DBCC CHECKDB 실행하기
데이터 파일 또는 로그 파일이 손상되어 데이터베이스가 SUSPECT 상태인 경우, 데이터베이스를 EMERGENCY 모드로 설정하고 DBCC CHECKDB를 실행합니다.
- 데이터베이스를 EMERGENCY 모드로 설정합니다.
ALTER DATABASE Sample SET EMERGENCY; GO - 데이터베이스를 SINGLE_USER 모드로 설정합니다.
ALTER DATABASE Sample SET SINGLE_USER; GO - 보고된 모든 오류를 복구합니다. 이러한 복구를 수행하면 일부 데이터가 손실될 수 있으므로 유의합니다.
DBCC CHECKDB (Sample, REPAIR_ALLOW_DATA_LOSS) GO - 데이터베이스를 MULTI_USER 모드로 설정합니다.
ALTER DATABASE Sample SET MULTI_USER; GO