DBCC SHRINKFILE(Transact-SQL)

적용 대상: SQL Server Azure SQL 데이터베이스Azure SQL Managed Instance

현재 데이터베이스의 지정된 데이터 또는 로그 파일 크기를 축소합니다. 파일을 비우고 데이터베이스 제거를 허용하는 동일한 파일 그룹의 파일 간에 데이터를 이동하는 데 사용할 수 있습니다. 최소 파일 크기를 새 값으로 다시 설정하여 파일을 만들 때 해당 크기보다 작게 축소할 수 있습니다.

Transact-SQL 구문 표기 규칙

Syntax

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { 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 }

참고

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

인수

file_name

축소할 파일의 논리적 이름입니다.

file_id

축소할 파일의 ID 번호입니다. 파일 ID를 가져오려면 FILE_IDEX 시스템 함수를 사용하거나 현재 데이터베이스의 sys.database_files 카탈로그 뷰를 쿼리합니다.

target_size

파일의 새 메가바이트 크기를 나타내는 정수입니다. 이 값을 지정하지 않거나 0을 지정하면 DBCC SHRINKFILE은 파일 생성 크기로 감소합니다.

DBCC SHRINKFILE <target_size>를 사용하여 빈 파일의 기본 크기를 줄일 수 있습니다. 예를 들어 5MB 파일을 만든 다음 파일이 아직 비어 있을 때 파일을 3MB로 줄이면 기본 파일 크기가 3MB로 설정됩니다. 이 내용은 데이터가 포함된 적이 없는 빈 파일에만 적용됩니다.

이 옵션은 FILESTREAM 파일 그룹 컨테이너에서 지원되지 않습니다.

지정된 경우 DBCC SHRINKFILE은 target_size로 파일을 축소합니다. 해제할 파일 영역에서 사용된 페이지는 파일 보유 영역의 사용 가능한 공간으로 이동됩니다. 예를 들어 10MB의 데이터 파일을 사용하면 target_size가 8인 DBCC SHRINKFILE 작업이 파일의 마지막 2MB에서 사용된 모든 페이지를 파일의 처음 8MB에서 할당되지 않은 페이지로 이동합니다. DBCC SHRINKFILE은 필요한 저장 데이터 크기 이하로 파일을 축소하지 않습니다. 예를 들어 10MB 데이터 파일에서 7MB가 사용되는 경우 target_size가 6인 DBCC SHRINKFILE 문은 파일을 6MB가 아니라 7MB로만 축소합니다.

EMPTYFILE

지정한 파일의 모든 데이터를 동일한 파일 그룹의 다른 파일로 마이그레이션합니다. 즉, EMPTYFILE은 지정된 파일의 데이터를 동일한 파일 그룹의 다른 파일로 마이그레이션합니다. EMPTYFILE은 이 파일이 읽기 전용이 아니더라도 파일에 새 데이터가 추가되지 않는다고 가정합니다. ALTER DATABASE 문을 사용하여 파일을 제거할 수 있습니다. ALTER DATABASE 문을 사용하여 파일 크기를 변경하면 읽기 전용 플래그가 재설정되고 데이터를 추가할 수 있습니다.

FILESTREAM 파일 그룹 컨테이너의 경우 FILESTREAM 가비지 수집기가 실행되어 EMPTYFILE이 다른 컨테이너에 복사한 불필요한 파일 그룹 컨테이너 파일을 모두 삭제할 때까지 파일을 제거하는 데 ALTER DATABASE를 사용할 수 없습니다. 자세한 내용은 sp_filestream_force_garbage_collection을 참조하세요. FILESTREAM 컨테이너를 제거하는 방법에 대한 내용은 ALTER DATABASE 파일 및 파일 그룹 옵션(Transact-SQL)에서 해당 섹션을 참조하세요.

NOTRUNCATE

target_percent의 지정 여부와 관계없이 데이터 파일의 끝에서 할당된 페이지를 파일 앞의 할당되지 않은 페이지로 이동합니다. 파일 끝의 사용 가능한 공간이 운영 체제에 반환되지 않고, 파일의 물리적 크기가 변경되지 않습니다. 그러므로 NOTRUNCATE를 지정하면 파일이 축소되지 않는 것처럼 보입니다.

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

이 옵션은 FILESTREAM 파일 그룹 컨테이너에서 지원되지 않습니다.

TRUNCATEONLY

파일 끝의 모든 사용 가능한 공간을 운영 체제로 해제하지만 파일 내에서 페이지 이동을 수행하지 않습니다. 데이터 파일은 마지막으로 할당된 익스텐트까지만 축소됩니다.

TRUNCATEONLY로 지정되면 target_size가 무시됩니다.

TRUNCATEONLY 옵션은 로그에 있는 정보를 이동시키지 않습니다. 하지만 로그 파일의 끝에서 비활성 상태의 VLF를 제거합니다. 이 옵션은 FILESTREAM 파일 그룹 컨테이너에서 지원되지 않습니다.

WITH NO_INFOMSGS

모든 정보 메시지를 표시하지 않습니다.

축소 작업으로 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

적용 대상: SQL Server(SQL Server 2022(16.x) 이상) 및 Azure SQL Database.

축소 명령이 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 ]

적용 대상: SQL Server(SQL Server 2022(16.x) 이상) 및 Azure SQL Database.

• SELF

  아무 작업도 수행하지 않고 현재 실행 중인 파일 축소 작업을 종료합니다.

• BLOCKERS

  작업을 계속할 수 있도록 파일 축소 작업을 차단하는 모든 사용자 트랜잭션을 종료합니다. BLOCKERS 옵션을 사용하려면 ALTER ANY CONNECTION 권한을 가진 로그인이 필요합니다.

결과 집합

다음 표에서 결과 집합 열에 대해 설명합니다.

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

설명

DBCC SHRINKFILE은 현재 데이터베이스의 파일에 적용됩니다. 현재 데이터베이스를 변경하는 방법에 대한 자세한 내용은 USE(Transact-SQL)를 참조하세요.

DBCC SHRINKFILE 작업은 언제든지 중지될 수 있으며 완료된 작업은 모두 그대로 유지됩니다. EMPTYFILE 매개 변수를 사용하고 작업을 취소하면 다른 데이터가 추가되지 않도록 파일이 표시되지 않습니다.

DBCC SHRINKFILE 작업이 실패하면 오류가 발생합니다.

파일을 축소하는 동안 다른 사용자가 데이터베이스에서 작업할 수 있습니다. 데이터베이스가 단일 사용자 모드일 필요가 없습니다. 시스템 데이터베이스를 축소하기 위해 SQL Server 인스턴스를 단일 사용자 모드에서 실행하지 않아도 됩니다.

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

DBCC SHRINKFILE의 동시성 문제 이해

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

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

로그 파일 축소

로그 파일의 경우 데이터베이스 엔진에서는 target_size를 사용하여 전체 로그의 대상 크기를 계산합니다. 따라서 target_size는 축소 작업 후 로그에서 사용 가능한 공간의 크기입니다. 그런 다음, 전체 로그의 대상 크기가 각 로그 파일의 대상 크기로 변환됩니다. DBCC SHRINKFILE은 즉시 각 물리적 로그 파일을 대상 크기로 축소하려고 시도합니다. 그러나 가상 로그에 대상 크기보다 큰 논리 로그 부분이 있는 경우 데이터베이스 엔진은 가능한 한 많은 공간을 해제하고 정보용 메시지를 표시합니다. 이 메시지는 파일 끝의 가상 로그에서 논리 로그를 이동하기 위해 수행해야 하는 동작을 설명합니다. 작업을 수행한 후 DBCC SHRINKFILE을 사용하여 나머지 공간을 해제할 수 있습니다.

로그 파일은 가상 로그 파일 크기만큼만 축소할 수 있으므로 사용 중이 아닌 로그 파일이라도 가상 로그 파일의 크기보다 작게 축소할 수는 없습니다. 로그 파일이 생성되거나 확장될 때 데이터베이스 엔진에서는 동적으로 가상 파일 로그 크기를 선택합니다.

모범 사례

파일을 축소할 때는 다음 정보를 고려하십시오.

• 축소 작업은 테이블 잘라내기 또는 테이블 삭제 작업과 같이 사용되지 않는 공간이 많이 생기는 작업을 수행한 후에 가장 효과적입니다.

• 대부분의 데이터베이스에는 정기적인 일상 작업에 사용 가능한 일정 여유 공간이 필요합니다. 데이터베이스 파일을 반복해서 축소하지만 데이터베이스 크기가 다시 늘어나는 경우 이는 일반 작업을 위한 여유 공간이 필요함을 나타냅니다. 이러한 경우 데이터베이스 파일을 반복해서 축소하는 것은 불필요한 작업입니다. 데이터베이스 파일을 증가시키는 데 필요한 자동 증가 이벤트는 성능을 저하합니다.

• 축소 작업은 데이터베이스 인덱스의 조각화 상태를 보존하지 않으며 일반적으로 조각화 정도를 어느 정도까지 늘리기도 합니다. 이 조각화는 데이터베이스를 반복해서 축소하지 않아야 하는 또 다른 이유입니다.

• 동일한 데이터베이스의 여러 파일을 동시에 축소하지 않고 순차적으로 축소합니다. 시스템 테이블에 대한 경합으로 인해 차단이 발생하고, 그 결과 지연이 발생할 수 있습니다.

문제 해결

이 섹션에서는 DBCC SHRINKFILE 명령을 실행할 때 발생할 수 있는 문제를 진단하고 해결하는 방법에 대해 설명합니다.

파일이 축소되지 않음

오류 없는 축소 작업 후에 파일 크기가 변경되지 않는 경우에는 다음을 시도하여 파일에 적절한 사용 가능한 공간이 있는지 확인합니다.

• 다음 쿼리를 실행합니다.

SELECT name
    , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files;

• DBCC SQLPERF 명령을 실행하여 트랜잭션 로그에 사용된 공간을 반환합니다.

사용 가능한 공간이 부족하면 축소 작업에서 파일 크기를 더 이상 줄일 수 없습니다.

일반적으로 축소되지 않는 것처럼 보이는 파일은 로그 파일입니다. 일반적으로 축소되지 않는 것은 로그 파일이 잘리지 않았기 때문입니다. 로그를 자르려면 데이터베이스 복구 모델을 SIMPLE로 설정하거나 로그를 백업한 후 DBCC SHRINKFILE 작업을 다시 실행합니다.

축소 작업이 차단됨

행 버전 관리 기반 격리 수준에서 실행 중인 트랜잭션이 축소 작업을 차단할 수 있습니다. 예를 들어 DBCC SHRINKDATABASE 작업을 실행할 때 행 버전 관리 기반 격리 수준에서 실행 중인 대규모 삭제 작업이 진행되고 있으면 축소 작업은 계속하기 전에 삭제 작업이 완료될 때까지 기다립니다. 이 차단이 발생하면 DBCC SHRINKFILE 및 DBCC SHRINKDATABASE 작업은 정보 메시지(SHRINKDATABASE의 경우 5202, SHRINKFILE의 경우 5203)를 SQL Server 오류 로그에 인쇄합니다. 이 메시지는 처음 한 시간 동안에는 5분마다 그리고 그 다음부터는 한 시간마다 기록됩니다. 예를 들어 오류 로그에 다음 오류 메시지가 포함된 경우 다음과 같은 오류가 발생합니다.

DBCC SHRINKFILE for file ID 1 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 동적 관리 뷰의 transaction_sequence_num 또는 first_snapshot_sequence_num 열에 15인 값이 포함되어 있음을 나타냅니다. transaction_sequence_num 또는 first_snapshot_sequence_num 뷰 열에 축소 작업의 마지막으로 완료된 트랜잭션(109)보다 작은 숫자가 포함되면 축소 작업은 해당 트랜잭션이 완료될 때까지 기다립니다.

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

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

사용 권한

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

예제

A. 데이터 파일을 지정한 대상 크기로 축소

다음 예제에서는 UserDB 사용자 데이터베이스의 DataFile1이라는 데이터 파일의 크기를 7MB로 축소합니다.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. 로그 파일을 지정한 대상 크기로 축소

다음 예에서는 AdventureWorks2022 데이터베이스에 있는 로그 파일을 1MB로 축소합니다. DBCC SHRINKFILE 명령이 파일을 축소할 수 있도록 먼저 데이터베이스 복구 모델을 SIMPLE로 설정하여 파일을 자릅니다.

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

C. 데이터베이스 파일 자르기

다음 예에서는 AdventureWorks2022 데이터베이스의 주 데이터 파일을 자릅니다. sys.database_files 카탈로그 뷰를 쿼리하여 데이터 파일의 file_id를 가져옵니다.

USE AdventureWorks2022;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. 파일 비우기

다음 예에서는 데이터베이스에서 제거할 수 있도록 파일을 비우는 방법을 보여 줍니다. 이 예에서 사용하기 위해 데이터 파일이 먼저 생성되고 데이터를 포함합니다.

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2022
REMOVE FILE Test1data;
GO

E. WAIT_AT_LOW_PRIORITY를 사용하여 데이터베이스 파일 축소

다음 예제에서는 현재 사용자 데이터베이스에 있는 데이터 파일의 크기를 1MB로 축소하려고 시도합니다. sys.database_files 카탈로그 뷰를 쿼리하여 데이터 파일의 file_id를 가져옵니다. 이 예에서는 file_id 5입니다. 1분 이내에 잠금을 가져올 수 없는 경우 축소 작업이 중단됩니다.

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

참고 항목

• SQL Server의 자동 증가 및 자동 축소 설정에 대한 고려 사항
• 데이터베이스 파일 및 파일 그룹
• sys.database_files(Transact-SQL)
• sys.databases(Transact-SQL)
• FILE_ID(Transact-SQL)

다음 단계

• ALTER DATABASE(Transact-SQL)
• DBCC SHRINKDATABASE(Transact-SQL)
• 데이터베이스 축소
• 파일 축소