본문 바로가기

개발

[DB] MSSQL DBA GUIDE - 데이터베이스 관리

 

MSSQL DBA GUIDE - 데이터베이스 관리




데이터베이스 생성
 수칙1. 트랜잭션 로그 파일은 로그 전용 드라이브에 배치합니다. 

데이터 파일들과 트랜잭션 로그 파일은 서로 다른 디스크에 배치합니다. 
모든 데이터베이스는 최소 하나의 주 데이터 파일(Primary Data File)과 하나의 트랜잭션 로그 파일로 구성됩니다. 트랜잭션 로그 파일은 별도의 드라이브에 배치합니다.

 [따라하기] 주 데이터 파일은 D 드라이브에 배치하고 트랜잭션 로그 파일은 E 드라이브에 배치하는 데이터베이스 생성하기

확장명은 파일의 용도를 정확하게 구분 할 수 있도록 주 데이터 파일은 .mdf, 보조 데이터파일은 .ndf, 트랜잭션 로그 파일은 .ldf를 사용합니다.

USE master
GO
CREATE DATABASE sample                 /* 데이터베이스 이름 */
ON (
  NAME = sample_dat,                     /* 데이터 파일 이름 */
  FILENAME = 'd:\DBdata\sample_dat.mdf', /* 데이터 파일 위치 */
  SIZE = 100 MB,                         /* 데이터 파일 초기 크기 */
  MAXSIZE = 1 GB,                        /* 데이터 파일 최대 크기 */
  FILEGROWTH = 100 MB)                    /* 데이터 파일 증가량 */
LOG ON (
  NAME = sample_log,                      /* 로그 파일 이름 */
  FILENAME = 'e:\DBlog\sample_log.ldf',   /* 로그 파일 위치 */
  SIZE = 20 MB,                           /* 로그 파일 초기 크기 */
  MAXSIZE = 500 MB,                       /* 로그 파일 최대 크기 */
  FILEGROWTH = 50 MB)                      /* 로그 파일 증가량 */
GO

 수칙2. 트랜잭션 로그 파일을 저장할 디스크는 일반적으로 RAID10으로 구성합니다. 

트랜잭션 로그 파일의 경우에는 복제가 구성되어 있거나 트리거가 빈번하게 수행되는 경우가 아니라면 대부분의 IO가 쓰기 작업이므로, 쓰기 작업의 성능을 위하여 트랜잭션 로그 파일은 RAID 10에 저장할 것을 권고합니다. 참고로, 로그에서 대기가 발생하는지는 다음 명령어로 확인할 수 있습니다.

DBCC SQLPERF (WAITSTATS)
GO

 수칙3. 데이터베이스를 만들 때 향후 예상되는 최대 데이터 크기를 고려하여 충분한 크기로 생성합니다. 

주 데이터 파일, 트랜잭션 로그 파일 모두 충분한 크기로 생성합니다. 파일이 증가하는 동안에는 쓰기 작업은 대기 상태가 되기 때문에 잦은 확장은 성능에 좋지 않은 영향을 미칠 수 있습니다. 트랜잭션 로그를 자동 증가하도록 옵션을 설정하되, 되도록이면 로그의 사이즈가 증가될 필요가 없도록 합니다. 트랜잭션 로그의 초기 크기는 트랜잭션 로그 백업을 수행한 후, 다음 로그 백업이 수행되기 전까지 발생하는 작업들을 저장하기에 충분한 크기로 생성합니다. 트랜잭션 로그 파일에 대하여 여러 번의 자동 증가가 발생하게 되면, 여러 개의 가상 로그 파일들로 조각화되어, 로그 관련 작업의 성능에 좋지 않은 영향을 미칩니다. 가상 로그파일을 줄이는 방법은 [데이터 베이스 축소하기]를 참조하십시오. 
만약 데이터베이스의 초기 크기가 작아서 확장이 발생하고 있다면, 파일의 크기를 충분한 크기로 확장하기 바랍니다.

[따라하기] 데이터베이스 파일 확장하기
ALTER DATABASE Sample
MODIFY FILE
  (NAME = sample_dat,
  SIZE = 2GB)
GO

 수칙4. 파일이 증가할 수 있는 최대 크기를 지정하는 것을 권고합니다. 

파일의 최대 크기를 지정하면, 파일의 크기가 증가하여 디스크 여유 공간이 전혀 없는 상태가 되는 것을 방지할 수 있습니다. 파일의 최대 크기를 지정하려면 CREATE DATABASE 문의 MAXSIZE 매개 변수를 사용하거나 엔터프라이즈 관리자의 등록 정보 대화 상자의 파일증가 제한(MB) 옵션을 사용하면 됩니다. 
만약 기존의 데이터베이스 파일의 최대 크기가 UNLIMITED로 설정되어 있다면, 최대 크기를 설정하기 바랍니다.

[따라하기] 데이터베이스 파일의 최대 크기 확인 및 설정하기
EXEC sp_helpdb Sample
-- 또는
EXEC Sample..sp_helpfile
GO
-- 결과 중 maxsize 정보를 확인 후 다음 명령어를 수행합니다.
ALTER DATABASE Sample
MODIFY FILE
  (NAME = sample_dat,
  MAXSIZE = 3GB)
GO

 수칙5. 파일이 자동으로 증가하도록 설정하는 경우에는 자동 확장 증가 크기를 적절하게 설정합니다. 

파일의 크기가 매우 작거나 매우 큰 경우에는 파일 자동 확장 증가분을 퍼센트 단위가 아닌 MB 단위로 지정하는 것을 권고합니다. 파일의 자동 확장 증가분을 지정하지 않으면 디폴트 값이 10% 확장으로 설정되는데, 데이터베이스의 크기가 큰 경우에는 새로운 데이터를 저장할 공간이 없어서 자동 확장이 이루어질 때 소요시간이 오래 걸림으로 인하여 트랜잭션 로그를 발생시키는 작업들이 대기 또는 실패하는 문제가 발생할 수 있습니다. 예를 들어, 데이터 파일의 크기가 100GB인 경우에 파일의 자동 확장 증가분이 10%로 설정되어 있다면, 자동 확장이 발생할 경우 10GB의 파일 확장을 수행합니다. 
10GB의 확장작업은 상당한 시간이 걸리는 작업이므로 정상적인 서비스를 하지 못하는 문제를 유발할 수 있습니다. 반대로 파일의 크기가 매우 작은 경우에는 10%씩 증가하면 확장되는 크기가 작아서 빈번하게 재확장이 발생합니다. 
로그 파일의 경우에 작은 크기의 확장이 여러 번 발생하면 여러 개의 작은 가상 로그 파일(VLF)들로 단편화가 발생하게 되어 성능을 저하시킬 수 있으므로 유의하기 바랍니다. 가상 로그 파일의 수는 일반적으로 25개 미만으로 유지하는 것을 권고하며, 가상 로그 파일의 수가 지나치게 많은 경우에는 가상 로그 파일의 수를 줄이는 작업을 수행하는 것이 좋습니다. 
작업 방법은 [트랜잭션 로그 파일 축소하기]를 참조하십시오.

[따라하기] 데이터베이스의 데이터 파일 증가율 100MB로 변경하기
ALTER DATABASE Sample
MODIFY FILE
  (NAME = sample_dat,
  FILEGROWTH = 100MB)
GO

 수칙6. 파일 그룹을 사용하여 데이터를 배치합니다. 

주 데이터 파일에는 메타 데이터만 저장하고, 사용자 오브젝트들은 사용자 정의 파일 그룹에 저장하며, 디폴트 파일 그룹을 주 파일 그룹이 아닌 사용자 정의 파일 그룹으로 변경할 것을 권고합니다. 참고로 데이터베이스는 주 파일 그룹과 사용자 정의 파일 그룹으로 구성되며 주 파일이 있는 파일 그룹이 주 파일 그룹이 되고 주 파일 그룹에는 모든 시스템 테이블이 저장됩니다. 
데이터베이스에 오브젝트를 만들 때 파일 그룹을 지정하지 않으면 오브젝트들은 디폴트 파일 그룹에 저장되며 디폴트로 주 파일 그룹이 디폴트 파일 그룹이 됩니다.

[따라하기] 파일 그룹이 있는 데이터베이스 생성하기
USE master
GO
CREATE DATABASE sample2
ON Primary (                        /* PRIMARY 파일 그룹 */
  NAME = sample2_Pri_dat,
  FILENAME = 'D:\DBdata\sample2_pri_dat.mdf',
  SIZE = 200 MB,
  MAXSIZE = 1 GB,
  FILEGROWTH = 20 MB),
FILEGROUP Sample2FG1 (              /* 두 번째 파일 그룹 */
  NAME = sample2_FG1_dat,
  FILENAME = 'E:\DBdata\sample2_FG1_dat.ndf',
  SIZE = 200 MB,
  MAXSIZE = 1 GB,
  FILEGROWTH = 20 MB),
FILEGROUP Sample2FG2 (              /* 세 번째 파일 그룹 */
  NAME = sample2_FG2_dat,
  FILENAME = 'F:\DBdata\sample2_FG2_dat.ndf',
  SIZE = 200 MB,
  MAXSIZE = 1 GB,
  FILEGROWTH = 20 MB)
LOG ON (                            /* 로그 파일 */
  NAME = sample2_log,
  FILENAME = 'G:\DBlog\sample2_log.ldf',
  SIZE = 10 MB,
  MAXSIZE = 50 MB,
  FILEGROWTH = 5 MB)
GO  

[따라하기] 디폴트 파일 그룹 확인 및 변경하기
USE Sample2
SELECT * FROM sysfilegroups WHERE status = 16
GO
/* 'Primary' 파일 그룹이 디폴트 파일 그룹이면 1이 반환되고 그렇지 않으면 0이
반환됩니다. */
SELECT FILEGROUPPROPERTY('Primary', 'IsDefault')
GO
/* 디폴트 파일 그룹을 'Sample2FG1' 로 변경합니다. */
ALTER DATABASE Sample2
MODIFY FILEGROUP [Sample2FG1] DEFAULT
GO

 수칙7. tempdb는 I/O가 빠른 쪽에 배치할 것을 권고합니다. 

Tempdb는 I/O가 빠른 쪽에 배치하는 것이 성능을 위해 좋습니다. Tempdb를 여러 디스크에 스트라이핑하면 더욱 좋습니다. 또한 tempdb를 자주 쓰는 사용자 데이터베이스와 물리적으로 격리된 디스크에 배치할 것을 권고합니다. 특히 tempdb를 매우 많이 사용하는 대규모 시스템이라면 tempdb를 별도의 디스크 세트에 배치하면 더 나은 성능 향상을 기대할 수 있습니다. 유의할 사항은, 데이터베이스 데이터와 운영 시스템의 페이징 파일을 동일한 디스크에 배치하는 것은 어떤 경우라도 좋은 방법이라고 할 수 없습니다.

[참고] 그 외 파일 배치하기
운영 시스템은 RAID 1로 구성된 어레이에 있어야 합니다. 페이징 파일은 운영 시스템이 있는 드라이브에서 훨씬 잘 동작하며, 데이터베이스에 별도의 디스크를 할당할 수 있게 하기 위해서 같은 위치에 있어도 관계 없습니다. 페이징 파일을 옮겨야 할 필요가 있다면, 데이터베이스의 데이터, 로그, tempdb가 있는 곳에는 위치시키지 않아야 합니다. 이렇게 해야 디스크 오류에 빠르게 대응하여 복구할 수 있습니다.
이 때, 부트 디스크는 미러를 이용하여 부팅 가능해야 합니다. 
시스템 백업을 동일 서버에 저장해야 한다면 반드시 데이터나 로그 파일이 없는 다른 디스크에 저장해야 합니다.
파일 그룹의 파일 수는 SQL Server의 성능과 관련이 없으므로, 파일은 관리하기 쉽게 배치합니다.

[참고] CREATE DATABASE가 실패하는 경우 문제 해결하기
새로운 데이터베이스의 생성이 실패하는 원인에는 여러 가지가 있지만 주로 다음과 같은 문제로 인하여 새로운 데이터베이스의 생성이 실패합니다. 

A. model 데이터베이스가 사용 중일 때
model 데이터베이스를 사용하는 프로세스의 수행이 완료되기를 기다렸다가 재수행하거나, model 데이터베이스를 사용 중인 프로세스를 강제로 중지한 후에 재수행합니다. 

B. 데이터베이스 파일의 물리적인 위치를 잘못 지정했을 때
지정한 폴더가 실제로 있는지 확인합니다. 
지정한 드라이브에 충분한 여유 공간이 있는지 확인합니다. 

C. CREATE DATABASE를 수행한 사용자에게 새로운 데이터베이스를 생성할 수 있는 권한이 없을 때
새로운 데이터베이스를 생성하기 위해서는 sysadmin 또는 dbcreator 역할의 구성원이어야 합니다. 이 역할의 구성원에게 작업을 요청하거나, 주기적으로 작업이 필요하다면 해당 사용자를 dbcreator 역할에 추가하면 됩니다. 

D. 동일한 이름의 데이터베이스가 이미 존재할 때
sp_helpdb를 수행하거나 master..sysdatabases 테이블을 참조하여 확인합니다. 만약 기존의 데이터베이스가 불필요하다면 sp_renamedb 를 사용하여 기존의 데이터베이스를 다른 이름으로 변경하거나 삭제한 후에 다시 시도합니다. 

E. 동일한 이름의 파일이 이미 존재할 때
존재하지 않는 파일 이름을 지정하고 다시 시도합니다.


 데이터베이스 삭제하기
 [구문] 
USE master
GO
DROP DATABASE database_name[ ,...n ]
GO
[유의사항]

DROP DATABASE를 수행하면 모든 데이터베이스 파일들도 디스크에서 삭제됩니다. 만약 다시 복구하고자 하는 경우에는 백업본을 복원해야 합니다. 그러므로, 만약 다시 참조할 필요가 있는 데이터베이스를 삭제하고자 하는 경우에는 sp_detach_db를 사용하여 SQL Server에서 데이터베이스 정보만 삭제하고 파일들은 디스크에 남겨 둘 것을 권고합니다.

[데이터베이스 파일 위치 변경하기]를 참조하십시오. 
데이터베이스를 삭제하면 master 데이터베이스의 시스템 테이블이 업데이트 되므로 데이터베이스가 삭제된 후에는 master 데이터베이스를 백업할 것을 권고합니다. master 데이터베이스를 복원할 필요가 있을 때, 마지막 master 백업 이후 삭제된 데이터베이스가 시스템 테이블에 남아 있으면 그로 인하여 오류가 발생할 수 있습니다.

[참고] DROP DATABASE가 실패하는 경우 문제 해결하기
삭제하고자 하는 데이터베이스를 다른 프로세스에서 연결 중이면 데이터베이스를 삭제할 수 없습니다. 데이터베이스 사용 중이어서 삭제할 수 없는 경우에는, 해당 데이터베이스를 사용하는 프로세스들이 완료되기를 기다렸다가 삭제하거나 아니면 다음과 같이 데이터베이스를 단일 사용자 모드로 변경한 다음에 삭제하거나 또는 spid를 확인하여 KILL 명령어로 프로세스들을 중지한 다음에 재시도합니다.

USE master
GO
ALTER DATABASE Sample
SET SINGLE_USER
WITH ROLLBACK AFTER 30 -- 30초가 경과한 후에 롤백
GO

 데이터베이스 이전하기
 [따라하기] 사용자 데이터베이스 이전하기 

주 데이터 파일과 트랜잭션 로그 파일을 다른 드라이브로 이전하는 예제입니다.


 

1. 데이터베이스에 연결되어 있는 연결을 모두 비 연결 상태로 만들고, 단일 사용자 모드로 설정합니다. 다음은 5초 후에 모든 작업들이 ROLLBACK되고, 연결을 끊는 예제입니다.

USE master
GO
ALTER DATABASE Sample
SET SINGLE_USER
WITH ROLLBACK AFTER 5
GO

[참고] EXEC sp_dboption database_name, 'single user' , true GO
이 명령어를 수행하는 세션 외에 다른 세션에서 해당 데이터베이스에 연결을 맺고 있는 경우에는, 데이터베이스를 단일 사용자 모드로 변경할 수 없습니다. 이 방법은 DBA가 해당 데이터베이스에 연결되어 있는 모든 세션들을 강제로 중지하거나 또는 사용자들 이 스스로 해당 데이터베이스에 대한 연결을 해제한 다음에 사용할 수 있습니다.


2. 해당 데이터베이스의 모든 데이터 파일과 트랜잭션 로그파일의 경로를 확인합니다.

USE master
EXEC sp_helpdb Sample
GO

3. 데이터베이스와 파일을 분리합니다.

EXEC sp_detach_db 'Sample', 'true'
GO

4. 데이터베이스 파일들을 원하는 위치에 복사합니다. 
    sample_dat.mdf, sample_log.ldf 파일을 각각 f:\DBdata, g:\DBlog 폴더로 복사합니다.

5. 변경된 위치의 파일 경로를 지정하여 데이터베이스를 서버에 추가합니다.

EXEC sp_attach_db 'sample'
, 'F:\DBData\sample_dat.mdf'
, 'G:\DBLog\sample_log.ldf'
GO

 Tempdb 위치 변경하기
 [따라하기] Tempdb를 디스크 상의 다른 위치로 이전하기 

아래 예제는 tempdb에만 적용할 수 있으며, 사용자 데이터베이스를 이동하고자 하는 경우
에는 sp_detach_db와 sp_attach_db를 사용하기 바랍니다. 이에 대한 자세한 내용은 [데이
터베이스 이전하기]를 참조하십시오.


 

1. tempdb 데이터베이스의 논리 파일 이름을 확인합니다.

USE tempdb
GO
EXEC sp_helpfile
GO
/* 결과
tempdev 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf PRIMARY
   102400 KB     Unlimited     10%     data only
templog 2
C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf NULL
    20480 KB     Unlimited     5120 KB     log only
*/

2. ALTER DATABASE 명령어를 사용하여 파일의 위치를 변경합니다.

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE
  (NAME = tempdev,
  FILENAME = 'E:\DBData\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog,
FILENAME = 'F:\DBData\templog.ldf')
GO

3. SQL Server 를 중지한 후 다시 시작합니다.


4. SQL Server 서비스가 시작된 다음에, 다음의 확인작업을 수행합니다.

USE tempdb
GO
EXEC sp_helpfile
GO

5. 기존의 tempdb 파일들을 삭제합니다.


 데이터베이스 파일 변경하기
 ■ 파일 크기 확장하기 

[따라하기] Sample 데이터베이스 sample_dat 논리 파일을 200MB로 확장하기
ALTER DATABASE sample
MODIFY FILE
(NAME = sample_dat,
SIZE = 200MB)
GO

■ 파일 증가 규칙 변경하기 

[따라하기] Sample 데이터베이스 sample_dat 논리 파일의 증가율을 5MB로 변경하기
ALTER DATABASE sample
MODIFY FILE
(NAME = sample_dat,
FILEGROWTH = 5MB)
GO

■ 새로운 파일 그룹 추가하기 

[따라하기] 파일 그룹 추가하기 
Sample 데이터베이스에 sample_Fg 파일 그룹을 추가한 후, 그 파일그룹에 Sample_New 파일을 추가합니다. 주 데이터 파일이 아닌 데이터 파일의 확장자는 .ndf를 사용합니다.
ALTER DATABASE Sample
ADD FILEGROUP Sample_FG
GO
ALTER DATABASE Sample
ADD FILE
  (NAME = Sample_New,
  FILENAME = 'f:\DBdata\Sample_New.ndf',
  SIZE = 10MB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 3MB)
TO FILEGROUP Sample_FG
GO

 데이터베이스 축소하기
 

IsAutoShrink 데이터베이스 옵션을 true로 설정하여 데이터베이스가 축소되도록 할 수는 있지만 성능적인 측면에서 IsAutoShrink 옵션은 비활성화하고, 데이터베이스 축소가 필요한 시점에 DBCC SHRINKDATABASE 명령어나 DBCC SHRINKFILE 명령어를 사용하여 수동으로 데이터베이스 파일의 크기를 축소할 것을 권고합니다. 데이터베이스의 특정 데이터 파일 또는 트랜잭션 로그 파일을 축소하는 경우에는 DBCC SHRINKFILE을 사용합니다.

■ 파일 지정 없이 축소하기 

[따라하기] 파일 지정 없이 Sample 데이터베이스 전체 크기 중에서 10%의 여유공간이 남도록 파일 크기를 축소합니다.
DBCC SHRINKDATABASE (Sample, 10)
GO

■ 특정 파일 축소하기 

[따라하기] Sample 데이터베이스의 sample_dat 파일을 10MB로 축소합니다.
USE Sample
GO
DBCC SHRINKFILE (sample_dat, 10)
GO

■ 가상 로그 파일 축소하기 
트랜잭션 로그 파일이 여러 번 자동 증가가 발생한 경우, 여러 개의 가상 로그 파일들로 조각화되어, 로그 관련 작업의 성능에 좋지 않은 영향을 미칩니다. 로그 파일의 크기가 매우 큰 경우가 아니라면 가상 로그 파일이 25개 이상일 경우, 가상 로그 파일을 제거하고, 트랜잭션 로그 파일을 적절한 크기로 변경합니다.

[따라하기] Sample 데이터베이스의 Sample_log 로그 파일의 가상 로그 파일을 제거하여, 트랜잭션 로그 파일을 축소합니다. 
 1. 가상 로그 파일 정보를 확인합니다. 결과 행의 수가 가상 로그 파일의 수입니다.
USE Sample
GO
DBCC LOGINFO
GO

2. 트랜잭션 로그 백업을 수행합니다. 로그 백업을 받을 수 없는 경우에는 로그를 삭제합니다. 
BACKUP LOG Sample TO DISK='D:\DBBackup\Sample_Log.bak'
GO
-- 또는
BACKUP LOG Sample WITH NO_LOG
GO

3. 트랜잭션 로그 파일의 크기를 가능한 한 작은 크기로 축소합니다.
EXEC sp_helpfile
GO
DBCC SHRINKFILE (Sample_log, TRUNCATEONLY)
GO

4. 로그 파일의 크기를 적절하게 변경합니다.
ALTER DATABASE Sample
MODIFY FILE
  ( NAME = 'Sample_log'
  , SIZE = 30)
GO

 데이터베이스 옵션 설정하기
 [따라하기] 데이터베이스 옵션 확인하기
SELECT DATABASEPROPERTYEX ('pubs', 'IsAutoUpdateStatistics')
GO

[참고] 새로운 데이터베이스를 만들 때 FOR ATTACH가 지정된 경우를 제외하면 model 데이터베이스의 데이터베이스 옵션 설정을 상속받습니다. 예를 들어, 디폴트로 model 데이터베이스의 IsAutoUpdateStatistics 옵션이 TRUE이기 때문에, 모든 데이터베이스들의 IsAutoUpdateStatistics 옵션이 TRUE로 설정됩니다. ALTER DATABASE를 사용하여 model 데이터베이스의 옵션을 변경하면, 그 이후에 새로 만들어지는 모든 데이터베이스에 변경된 옵션이 적용됩니다. 사용자 데이터베이스의 옵션에 대한 표준안이 정해지면 model 데이터베이스의 옵션을 변경해 두면 편리합니다.

/* model 데이터베이스의 옵션 설정 */
USE model
GO
ALTER DATABASE model
SET AUTO_UPDATE_STATISTICS OFF,
RECOVERY BULK_LOGGED
GO
/* model 데이터베이스의 변경된 옵션정보 확인 */
SELECT DATABASEPROPERTYEX ('model', 'IsAutoUpdateStatistics')
SELECT DATABASEPROPERTYEX ('model', 'Recovery')
GO

 데이터베이스 소유자 변경하기
 [따라하기] sample 데이터베이스의 소유자를‘dbadmin’으로변경하기
USE Sample
EXEC sp_changedbowner 'dbadmin'
GO

 데이터베이스 이름 변경하기

다른 사용자가 데이터베이스를 사용하지 않아야 성공적으로 이름이 변경됩니다.

 [따라하기] sample 데이터베이스의 이름을 sample_rename으로 변경하기
EXEC sp_renamedb Sample, Sample_Rename
GO
-- 이후의 테스트를 위하여 다시 원래 이름으로 변경합니다.
EXEC sp_renamedb Sample_Rename, Sample
GO