SELECT name AS DB이름
, create_date AS 생성일
, compatibility_level AS 호환성수준
, collation_name AS 정렬방식
, user_access_desc AS 액세스허용
, state_desc AS 상태
, recovery_model_desc AS 복구모델
, snapshot_isolation_state_desc AS 스냅샷격리수준
, log_reuse_wait_desc AS 로그재사용대기
FROM sys.DATABASES
1-1 호환성수준
버전이 바뀌면서 이전 버전에서 제공하던 기능이나 쿼리문을 더 이상 사용하지 못하게 되는 경우가 있다. 호환성 수준을 낮추면 이전 버전에서 사용하던 기능을 계속해서 사용할 수 있다.
1-2 상태(OFFLINE | ONLINE | EMERGENCY)
OFFLINE으로 설정하면 데이터베이스가 닫히고 종료되어 사용할 수 없다. ONLINE으로 설정하면 데이터베이스가 열려 데이터베이스를 사용할 수 있다. EMERGENCY로 설정하면 데이터베이스가 읽기 전용으로 표시되면, sysadmin 고정 서버 역할의 맴버만 연결할 수 있다. 로그 파일이 손상되어 '주의 대상'으로 표시된 데이터베이스를 EMERGENCY로 설정하면 읽을 수 있다. 기본값은 ONLINE이다.
1-3 엑세스 제한(SINGEL_USER | RESTRICTED_USER | MULTI_USER)
SINGLE_USER로 설정하면 한 번에 한 명의 사용자만 데이터베이스에 연결할 수 있다. RESTRICTED_USER로 설정하면 db_owner 고정 데이터베이스 역할, dbcreator 와 sysdamin 고정 서버 역할의 멤버만 연결 할 수 있으며, 연결할 수 있는 멤버의 수에는 제한이 없다. MULTI_USER는 적합한 권한을 가진 모든 사용자가 연결 할 수 있다.
1-4 복구모델(RECOVERY)
전체(FULL)로 설정하면 데이터베이스가 손상된 경우 트랜잭션 로그 백업을 사용하여 완벽하게 복원할 수 있다. 특정 시점의 복원도 가능하여 중요 데이터베이스에 적합한 복구 모델이다. 대량 로그(BULK_LOGGED)로 설정하면 특정 대량 작업에 대해 로그 공간을 최소로 사용하여, 대량 작업에 대한 성능을 높일 수 있다. 하지만 대량 작업이 발생한 경우 특점 시점 복원을 할 수 없다. 단순(SIMPLE)으로 설정하면 최소한의 로그 공간을 사용하며, 로그 백업을 수행할 수 없다. 개발용이나 읽기 전용 데이터베이스에 적합한 복구 모델이다.
2. 데이터베이스 파일 크기 정보
EXEC sp_helpfile
-- 사용 중인 디스크 크기
SELECT(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
-- 전체 디스트 크기
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM sys.database_files
-- DB 사이즈 줄이기(MDF, NDF)
DBCC SHRINKDATABASE ([DB명], TRUNCATEONLY)
-- 로그 사이즈 줄이기(LDF)
DBCC SHRINKFILE(tempdev, TRUNCATEONLY);
3. 파일 그룹
3-1 주 파일 그룹(Primary File Group)
주 파일 그룹, 즉 PRIMARY 파일 그룹은 주 데이터 파일(mdf 확장자를 가진 파일)을 포함한다. 물론 보조 데이터 파일(ndf 확장자를 가진 파일)도 Primary 파일 그룹에 추가할 수 있다.
PRIMARY 파일 그룹만 사용하는 경우에는 어떤 테이블은 주 데이터 파일에 저장하고, 어떤 테이블은 보조 데이터 파일에 저장하는 형태를 갖출 수는 없다.
3-2 사용자 정의 파일 그룹(User-defined File Groups)
PRIMARY 파일 그룹 외에 사용자가 마든 파일 그룹이 사용자 파일 그룹이며, 여러 개 만들 수 있다. 사용자 저의 파일 그룹을 사용하면 테이블을 역할별로 구분하여 서로 다른 위치에 저장할 수 있다. PRIMARY 파일 그룹은 하나만 존재할 수 있지만, 사용자 정의 파일 그룹은 여러개 존재할 수 있다.
--파일 그룹 추가
ALTER DATABASE HRDB2
ADD FILEGROUP UF01
--파일 그룹에 파일 추가
ALTER DATABASE HRDB2
ADD FILE (
NAME = 'UF01',
FILENAME = 'E:\SQLData\UF01.ndf',
SIZE = 512MB,
FILEGROWTH = 128MB
) TO FILEGROUP UF01
-- 기본 그룹 변경
ALTER DATABASE HRDB2
MODIFY FILEGROUP UF01 DEFAULT
-- 특정 파일 그룹에 테이블 생성
CREATE TABLE [dbo].[GUNDAM_2](
[ID] [int] NULL,
[ID2] [int] NULL
) ON [UF01]
4. tempdb 데이터베이스 이동
데이터 파일과 트랜잭션 로그 파일이 물리적으로 서로 다른 디스크에 위치하는 것이 성능을 위해 좋다고 했다. 성능을 위해 또 하나의 고려할 것이 tempdb 데이터베이스 위치이다.
tempdb 데이터 베이스는 사용자가 의도하지 않더라도 SQL Server 내부적인 필요 때문에 사용되는 경우가 많다. 수많은 임시 테이블들이 만들어지고 제거되는 tempdb 데이터베이스다 보다, tempdb 데이터베이스를 다른 디스크에 두는 것이 성능에 좋다. 사용자 데이터베이스는 분리한 후 새로운 위치로 파일을 옮긴 후 연결하면 된다. tempdb 데이터베이스는 SQL Server 운영 중에 절대로 분리하거나 연결할 수 없는 데이터베이스다.
tempdb 데이터베이스를 옮길려면, SQL Server 운영 중에 ALTER DATABASE 문으로 모든 파일의 새로운 위치를 저장한 후 SQL Server 서비스를 재시작하면 된다. tempdb 데이터베이스는 SQL Server 서비스가 시작되면서 다시 만들어지므로, 기존 파일을 새로운 위치로 옮길 필요가 없다. 파일들이 새롭게 만들어질 폴더만 정확히 존재하고 있으면 된다.
-- 데이터 파일 옮기기
ALTER DATABASE tempdb
MODIFY FILE (
NAME = tempdev,
FILENAME = 'E:\SQLData\tempdb.mdf'
)
ALTER DATABASE tempdb
MODIFY FILE (
NAME = temp2,
FILENAME = 'E:\SQLData\tempdb_mssql_2.ndf'
)
ALTER DATABASE tempdb
MODIFY FILE (
NAME = temp3,
FILENAME = 'E:\SQLData\tempdb_mssql_3.ndf'
)
ALTER DATABASE tempdb
MODIFY FILE (
NAME = temp4,
FILENAME = 'E:\SQLData\tempdb_mssql_4.ndf'
)
-- 로그 파일 옮기기
ALTER DATABASE tempdb
MODIFY FILE (
NAME = templog,
FILENAME = 'F:\SQLLog\templog.ldf'
)
4. 데이터베이스 이름 변경
EXEC sp_renamedb 'HRDB2' 'GUNDAM'
ALTER DATABASE HRDB2
MODIFY NAME = GUNDAM
(참고로 데이터베이스 옵션에서 액세스 허용을 SINGLE_USER로 변경해야 함)
