다음을 통해 공유

DBCC SHRINKDATABASE(Transact-SQL)

  • 아티클

적용 대상: SQL Server Azure SQL 데이터베이스Azure SQL Managed InstanceAzure Synapse 분석

지정한 데이터베이스에 있는 데이터 및 로그 파일의 크기를 축소합니다.

Transact-SQL 구문 표기 규칙

구문

SQL Server 구문:

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH 

    {     
         [ WAIT_AT_LOW_PRIORITY 
            [ ( 
                  <wait_at_low_priority_option_list>
             )] 
         ] 
         
         [ , NO_INFOMSGS]
    }
]

< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>

< wait_at_low_priority_option > ::=
  ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Azure Synapse Analytics 구문:

DBCC SHRINKDATABASE
( database_name
     [ , target_percent ]
)
[ WITH NO_INFOMSGS ]

참고

SQL Server 2014 이전 버전의 Transact-SQL 구문을 보려면 이전 버전 설명서를 참조하세요.

인수

database_name | database_id | 0

축소할 데이터베이스의 이름 또는 ID입니다. 0은 현재 데이터베이스가 사용됨을 나타냅니다.

target_percent

데이터베이스를 축소한 후 데이터베이스에 남겨둘 여유 공간의 비율입니다.

NOTRUNCATE

할당된 페이지를 파일 끝에서 파일 앞의 할당되지 않은 페이지로 이동합니다. 이 작업은 파일 내의 데이터를 압축합니다. target_percent는 선택 사항입니다. Azure Synapse Analytics에서는 이 옵션을 지원하지 않습니다.

파일 끝의 여유 공간이 운영 체제로 반환되지 않고, 파일의 실제 크기가 변경되지 않습니다. 따라서 NOTRUNCATE를 지정할 때 데이터베이스는 축소되지 않는 것으로 나타납니다.

NOTRUNCATE는 데이터 파일에만 적용됩니다. NOTRUNCATE는 로그 파일에 영향을 주지 않습니다.

TRUNCATEONLY

파일 끝의 모든 여유 공간을 운영 체제로 릴리스합니다. 파일 내의 어떤 페이지도 이동하지 않습니다. 데이터 파일은 마지막으로 할당된 익스텐트까지 축소됩니다. TRUNCATEONLY로 지정된 경우 target_percent는 무시됩니다. Azure Synapse Analytics에서는 이 옵션을 지원하지 않습니다.

TRUNCATEONLY 옵션을 사용하는 DBCC SHRINKDATABASE는 데이터베이스 트랜잭션 로그 파일에만 영향을 줍니다. 데이터 파일을 잘라내려면 대신 DBCC SHRINKFILE을 사용하세요. 자세한 내용은 DBCC SHRINKFILE을 참조하세요.

WITH NO_INFOMSGS

심각도가 0에서 10 사이인 모든 정보 메시지를 표시하지 않습니다.

축소 작업으로 WAIT_AT_LOW_PRIORITY

적용 대상: SQL Server 2022(16.x) 이상 버전, Azure SQL Database, Azure SQL Managed Instance

우선 순위가 낮은 대기 기능은 잠금 경합을 줄입니다. 자세한 내용은 DBCC SHRINKDATABASE의 동시성 문제 이해를 참조하세요.

이 기능은 온라인 인덱스 작업의 WAIT_AT_LOW_PRIORITY와 유사하지만 몇 가지 차이점이 있습니다.

  • ABORT_AFTER_WAIT 옵션 NONE을 지정할 수 없습니다.

WAIT_AT_LOW_PRIORITY

축소 명령이 WAIT_AT_LOW_PRIORITY 모드에서 실행될 때 스키마 안정성(Sch-S) 잠금이 필요한 새 쿼리는 축소 작업이 대기를 중지하고 실행을 시작할 때까지 대기 중인 축소 작업에 의해 차단되지 않습니다. 스키마 수정 잠금(Sch-M 잠금)을 가져올 수 있을 때 축소 작업이 실행됩니다. WAIT_AT_LOW_PRIORITY 모드의 새 축소 작업이 장기 실행 쿼리로 인해 잠금을 얻을 수 없는 경우 축소 작업은 결국 기본으로 1분 후에 시간 초과되고 오류 없이 종료됩니다.

WAIT_AT_LOW_PRIORITY 모드의 새 축소 작업이 장기 실행 쿼리로 인해 잠금을 얻을 수 없는 경우 축소 작업은 결국 기본으로 1분 후에 시간 초과되고 오류 없이 종료됩니다. 이는 동시 쿼리 또는 Sch-S 잠금을 보유하는 쿼리로 인해 축소 작업이 Sch-M 잠금을 가져올 수 없는 경우에 발생합니다. 시간 제한이 발생하면 오류 49516 메시지가 SQL Server 오류 로그로 전송되는데 예를 들면 다음과 같습니다. Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. 이 시점에서는 애플리케이션에 영향을 주지 않는다는 것을 알고 WAIT_AT_LOW_PRIORITY 모드에서 축소 작업을 다시 시도하면 됩니다.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

  • SELF

    SELF 옵션은 기본 옵션입니다. 아무 작업도 수행하지 않고 현재 실행 중인 데이터베이스 축소 작업을 종료합니다.

  • BLOCKERS

    작업을 계속할 수 있도록 데이터베이스 축소 작업을 차단하는 모든 사용자 트랜잭션을 종료합니다. BLOCKERS 옵션을 사용하려면 로그인에 ALTER ANY CONNECTION 권한이 있어야 합니다.

결과 집합

다음 표에서는 결과 집합의 열을 설명합니다.

열 이름 Description
DbId 데이터베이스 엔진에서 축소하려고 시도한 파일의 데이터베이스 ID입니다.
FileId 데이터베이스 엔진에서 축소하려고 시도한 파일의 파일 ID 번호
CurrentSize 현재 파일이 차지하고 있는 8KB 페이지의 수입니다.
MinimumSize 파일이 최소한으로 차지할 수 있는 8KB 페이지의 수입니다. 이 값은 파일의 최소 크기나 원래 만들어졌을 때의 크기와 일치합니다.
UsedPages 현재 파일에서 사용되는 8KB 페이지의 수입니다.
EstimatedPages 데이터베이스 엔진에서 예상하는 파일 축소 가능 크기에 해당하는 8KB 페이지의 수입니다.

참고

데이터베이스 엔진은 축소되지 않은 파일의 행은 표시하지 않습니다.

설명

참고

Azure Synapse에서 축소 명령을 실행하는 것은 I/O 집약적인 작업이며 전용 SQL 풀(이전의 SQL DW)을 오프라인으로 전환할 수 있으므로 권장되지 않습니다. 또한 이 명령을 실행한 후에 데이터 웨어하우스 스냅샷에 대한 비용 관련 사항이 발생합니다.

특정 데이터베이스의 모든 데이터와 로그 파일을 축소하려면 DBCC SHRINKDATABASE 명령을 실행합니다. 특정 데이터베이스의 한 데이터나 로그 파일을 동시에 축소하려면 DBCC SHRINKFILE 명령을 실행합니다.

현재 데이터베이스에 있는 여유(할당되지 않은) 공간의 양을 보려면 sp_spaceused를 실행합니다.

DBCC SHRINKDATABASE 작업은 진행 도중에 언제든지 중지될 수 있으며 완료된 작업은 모두 보관됩니다.

데이터베이스는 구성된 최소 크기의 데이터베이스보다 작을 수 없습니다. 데이터베이스를 처음 만들 때 최소 크기를 지정합니다. 또는 최소 크기는 파일 크기 변경 작업을 사용하여 명시적으로 설정한 마지막 크기일 수 있습니다. DBCC SHRINKFILE 또는 ALTER DATABASE와 같은 작업은 파일 크기 변경 작업의 예입니다.

데이터베이스가 원래 10MB 크기로 생성되었다고 가정합니다. 그런 다음, 100MB까지 증가합니다. 데이터베이스의 모든 데이터가 삭제된 경우에도 데이터베이스를 줄일 수 있는 최소 크기는 10MB입니다.

DBCC SHRINKDATABASE 실행 시 NOTRUNCATE 옵션 또는 TRUNCATEONLY 옵션을 지정합니다. 그렇지 않으면 NOTRUNCATE로 DBCC SHRINKDATABASE 작업을 실행한 뒤 TRUNCATEONLY로 DBCC SHRINKDATABASE 작업을 실행한 것과 같은 결과가 됩니다.

축소된 데이터베이스는 단일 사용자 모드에 있을 필요가 없습니다. 시스템 데이터베이스를 포함하여 데이터베이스가 축소될 때 다른 사용자가 데이터베이스에서 작업할 수 있습니다.

데이터베이스가 백업되는 동안에는 데이터베이스를 축소할 수 없습니다. 반대로 데이터베이스에 대한 축소 작업이 처리되는 동안에는 데이터베이스를 백업할 수 없습니다.

WAIT_AT_LOW_PRIORITY 사용하여 지정한 경우 축소 작업의 Sch-M 잠금 요청은 명령을 1분 동안 실행할 때 낮은 우선 순위로 대기합니다. 작업이 해당 기간 동안 차단되면 지정된 ABORT_AFTER_WAIT 작업이 실행됩니다.

DBCC SHRINKDATABASE 작동 방법

DBCC SHRINKDATABASE는 파일 단위로 데이터 파일을 축소하지만 로그 파일이 모두 한 연속 로그 풀에 있는 것처럼 로그 파일을 축소합니다. 항상 파일은 끝부터 축소됩니다.

두 개의 로그 파일, 데이터 파일 및 mydb라는 데이터베이스가 있다고 가정합니다. 데이터 파일과 로그 파일은 각각 10MB이고 데이터 파일에는 6MB의 데이터가 포함됩니다. 데이터베이스 엔진는 각 파일의 대상 크기를 계산합니다. 이 값은 파일을 축소할 크기입니다. DBCC SHRINKDATABASE에 target_percent를 지정하면 데이터베이스 엔진에서는 축소 후 파일에 target_percent 만큼의 여유 공간이 남도록 대상 크기를 계산합니다.

예를 들어 mydb를 축소하기 위해 target_percent를 25로 지정하면 데이터베이스 엔진에서는 데이터 파일의 대상 크기를 8MB(6MB의 데이터 + 2MB의 여유 공간)로 계산합니다. 따라서 데이터베이스 엔진는 데이터 파일의 마지막 2MB에서 데이터 파일의 처음 8MB에 포함된 여유 공간으로 데이터를 이동한 다음, 파일을 축소합니다.

mydb의 데이터 파일에 7MB의 데이터가 있다고 가정합니다. target_percent를 30으로 지정하면 여유 공간이 30%만 남도록 이 데이터 파일이 축소됩니다. 그러나 target_percent를 40으로 지정해도 데이터 파일의 현재 총 크기에 충분한 여유 공간을 만들 수 없기 때문에 데이터 파일이 축소되지 않습니다.

이 문제를 다른 방법으로 생각해 볼 수도 있습니다. 원하는 여유 공간 40% + 전체 데이터 파일 70%(10MB 중 7MB)는 100%보다 큽니다. 30보다 큰 모든 target_size는 데이터 파일을 축소하지 않습니다. 원하는 여유 백분율에 데이터 파일이 차지하는 현재 백분율을 더한 값이 100%를 초과하기 때문에 축소되지 않습니다.

로그 파일의 경우 데이터베이스 엔진에서는 target_percent를 사용하여 전체 로그의 대상 크기를 계산합니다. 따라서 target_percent가 축소 작업 후 로그의 여유 공간 크기입니다. 그런 다음 전체 로그의 대상 크기가 각 로그 파일의 대상 크기로 변환됩니다.

DBCC SHRINKDATABASE은 즉시 각 물리적 로그 파일을 대상 크기로 축소하려고 시도합니다. 논리 로그의 어떤 부분도 로그 파일의 대상 크기를 초과하여 가상 로그에 남아 있지 않다고 가정해 봅니다. 그런 다음, 파일이 성공적으로 잘리고 DBCC SHRINKDATABASE가 메시지 없이 완료됩니다. 그러나 가상 로그에 대상 크기보다 큰 논리 로그 부분이 있는 경우 데이터베이스 엔진는 가능한 한 많은 공간을 해제하고 정보용 메시지를 표시합니다. 이 메시지는 파일 끝의 가상 로그에서 논리 로그를 이동하기 위해 수행해야 하는 동작을 설명합니다. 작업이 실행된 후 DBCC SHRINKDATABASE를 사용하여 나머지 공간을 해제할 수 있습니다.

로그 파일은 가상 로그 파일 경계까지만 축소할 수 있습니다. 따라서 로그 파일을 가상 로그 파일 크기보다 작은 크기로 축소하는 것이 불가능할 수 있습니다. 사용되지 않더라도 불가능할 수 있습니다. 로그 파일이 생성되거나 확장될 때 데이터베이스 엔진에서 동적으로 가상 로그 파일의 크기가 선택됩니다.

DBCC SHRINKDATABASE의 동시성 문제 이해

데이터베이스 축소 및 파일 축소 명령은 특히 인덱스 다시 빌드와 같은 활성 유지 관리 또는 사용 중인 OLTP 환경에서 동시성 문제를 초래할 수 있습니다. 애플리케이션이 데이터베이스 테이블에 대해 쿼리를 실행할 때 이러한 쿼리는 쿼리가 작업을 완료할 때까지 스키마 안정성 잠금(Sch-S)을 획득하고 유지 관리합니다. 정기적인 사용 중에 공간을 회수하려고 할 때 데이터베이스를 축소하고 파일 작업을 축소하려면 현재 IAM(인덱스 할당 맵) 페이지를 이동하거나 삭제할 때 스키마 수정 잠금(Sch-M)이 필요하며 사용자 쿼리에 필요한 Sch-S 잠금을 차단해야 합니다. 따라서 장기 실행 쿼리는 쿼리가 완료될 때까지 축소 작업을 차단합니다. 즉, Sch-S 잠금이 필요한 새 쿼리도 대기 중인 축소 작업 뒤에 대기 중이며 또한 차단되므로 이러한 동시성 문제가 더욱 악화됩니다. 이로 인해 애플리케이션 쿼리 성능에 큰 영향을 줄 수 있으며 데이터베이스 파일을 축소하는 데 필요한 유지 관리를 완료하는 데 문제가 발생할 수도 있습니다. SQL Server 2022(16.x)에서 도입된 낮은 우선 순위의 축소 대기(WLP) 기능은 WAIT_AT_LOW_PRIORITY 모드에서 스키마 수정 잠금을 사용하여 이 문제를 해결합니다. 자세한 내용은 축소 작업의 WAIT_AT_LOW_PRIORITY를 참조하세요.

Sch-S 및 Sch-M 잠금에 관한 자세한 내용은 트랜잭션 잠금 및 행 버전 관리 가이드를 참조하세요.

모범 사례

데이터베이스를 축소할 때는 다음을 고려하세요.

  • 축소 작업은 테이블 잘라내기 또는 테이블 삭제 작업과 같이 사용되지 않는 공간이 생기는 작업을 수행한 후에 가장 효과적입니다.
  • 대부분의 데이터베이스에는 정기적인 일상 작업에 사용 가능한 일정 여유 공간이 필요합니다. 데이터베이스 파일을 반복해서 축소하지만 데이터베이스 크기가 다시 늘어나는 경우 이는 일반 작업을 위한 여유 공간이 필요함을 나타냅니다. 이러한 경우 데이터베이스 파일을 반복해서 축소하는 것은 불필요한 작업입니다. 데이터베이스 파일을 증가시키는 데 필요한 자동 증가 이벤트는 성능을 저하합니다.
  • 축소 작업은 데이터베이스 인덱스의 조각화 상태를 보존하지 않으며 일반적으로 조각화 정도를 어느 정도까지 늘리기도 합니다. 이 결과는 데이터베이스를 반복해서 축소하지 않아야 하는 또 다른 이유입니다.
  • 특정 요구 사항이 없으면 AUTO_SHRINK 데이터베이스 옵션을 ON으로 설정하지 마세요.

문제 해결

행 버전 관리 기반 격리 수준에서 실행 중인 트랜잭션에 의해 축소 작업이 차단될 수 있습니다. 예를 들어 DBCC SHRINKDATABASE 작업을 실행할 때 행 버전 관리 기반 격리 수준에서 실행되는 대규모 삭제 작업이 진행 중입니다. 이 상황이 발생하면 축소 작업은 삭제 작업이 완료될 때까지 기다렸다가 파일을 축소합니다. 축소 작업이 대기 중일 때 DBCC SHRINKFILE 및 DBCC SHRINKDATABASE 작업은 정보성 메시지(SHRINKDATABASE의 경우 5202, SHRINKFILE의 경우 5203)를 출력합니다. 이 메시지는 처음 시간에는 5분마다, 그리고 다가오는 시간마다 SQL Server 오류 로그에 인쇄됩니다. 예를 들어 오류 로그에 다음과 같은 오류 메시지가 있습니다.

DBCC SHRINKDATABASE for database ID 9 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.

이 오류는 109보다 오래된 타임스탬프가 있는 스냅샷 트랜잭션이 축소 작업을 차단한다는 것을 의미합니다. 해당 트랜잭션은 축소 작업이 완료된 마지막 트랜잭션입니다. 또한 sys.dm_tran_active_snapshot_database_transactions(Transact-SQL) 동적 관리 뷰의 transaction_sequence_num 또는 first_snapshot_sequence_num 열에 값 15가 있음을 나타냅니다. 뷰의 transaction_sequence_num 또는 first_snapshot_sequence_num 열에 축소 작업(109)으로 완료된 마지막 트랜잭션보다 작은 숫자가 포함될 수 있습니다. 그렇다면, 축소 작업은 해당 트랜잭션이 완료될 때까지 대기합니다.

문제를 해결하려면 다음 태스크 중 하나를 수행하십시오.

  • 축소 작업을 차단하는 트랜잭션을 종료합니다.
  • 축소 작업을 종료합니다. 완료된 작업은 모두 보관됩니다.
  • 아무 작업도 하지 않고 차단하는 트랜잭션이 완료될 때까지 축소 작업이 대기할 수 있게 합니다.

사용 권한

sysadmin 고정 서버 역할의 멤버 또는 db_owner 고정 데이터베이스 역할의 멤버여야 합니다.

예제

A. 데이터베이스 축소 및 여유 공간의 백분율 지정

다음 예제에서는 UserDB 사용자 데이터베이스의 데이터 및 로그 파일 크기를 줄여서 데이터베이스에 10%의 여유 공간을 허용합니다.

DBCC SHRINKDATABASE (UserDB, 10);
GO

B. 데이터베이스 자르기

다음 예제에서는 AdventureWorks2022 샘플 데이터베이스의 데이터 및 로그 파일을 마지막으로 할당된 익스텐트까지 축소합니다.

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

C. Azure Synapse Analytics 데이터베이스 축소

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10);

D. WAIT_AT_LOW_PRIORITY를 사용하여 데이터베이스 축소

다음 예제에서는 데이터베이스에서 20%의 여유 공간을 허용하도록 AdventureWorks2022 데이터베이스의 데이터 및 로그 파일 크기를 줄이려고 시도합니다. 1분 이내에 잠금을 가져올 수 없는 경우 축소 작업이 중단됩니다.

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

참고 항목

다음 단계