다음을 통해 공유

Azure SQL Database의 데이터베이스에 대한 파일 공간 관리

  • 아티클

적용 대상: Azure SQL Database

이 문서에서는 Azure SQL Database의 데이터베이스에 대한 다양한 종류의 스토리지 공간 및 할당된 파일 공간을 명시적으로 관리해야 하는 경우 취할 수 있는 단계를 설명합니다.

개요

Azure SQL Database에는 데이터베이스를 위한 기본 데이터 파일의 할당량이 사용하는 데이터 페이지 수를 초과할 수 있는 워크로드 패턴이 있습니다. 이 상태는 사용하는 공간이 증가하고 추후에 데이터를 삭제하는 경우 발생할 수 있습니다. 이렇게 되는 이유는 데이터가 삭제될 때 할당되어 있는 파일 공간이 자동으로 회수되지 않기 때문입니다.

파일 공간 사용량 모니터링 및 데이터 파일 축소는 다음과 같은 시나리오에서 필요할 수 있습니다.

  • 데이터베이스에 할당된 파일 공간이 풀 최대 크기에 도달하는 경우 탄력적 풀의 데이터 증가를 허용합니다.
  • 단일 데이터베이스 또는 탄력적 풀의 최대 크기 감소를 허용합니다.
  • 단일 데이터베이스 또는 탄력적 풀을 더 작은 최대 크기의 다른 서비스 계층 또는 성능 계층으로 변경하는 것을 허용합니다.

참고

축소 작업을 정기적인 유지 관리 작업으로 간주해서는 안 됩니다. 반복되는 일상 업무에 따라 증가하는 데이터와 로그 파일은 축소 작업이 필요하지 않습니다.

파일 공간 사용량 모니터링

다음 API에 표시되는 대부분의 스토리지 공간 메트릭은 사용한 데이터 페이지의 크기만 측정합니다.

  • PowerShell get-metrics를 포함한 Azure Resource Manager 기반 메트릭 API

그러나 다음 API는 데이터베이스 및 탄력적 풀에 할당된 공간의 크기도 측정합니다.

데이터베이스를 위한 저장소 공간 유형 이해

다음 스토리지 공간 수량을 이해하는 것은 데이터베이스의 파일 공간을 관리하는 데 중요합니다.

데이터베이스 수량 정의 주석
사용된 데이터 공간 데이터베이스 데이터를 저장하는 데 사용되는 공간 크기입니다. 일반적으로 사용된 공간은 삽입(삭제) 시 증가(감소)합니다. 작업 및 조각화와 관련된 데이터의 크기 및 패턴에 따라 삽입 또는 삭제 시 사용된 공간이 변경되지 않는 경우가 있습니다. 예를 들어 모든 데이터 페이지에서 하나의 행을 삭제한다고 해서 사용된 공간이 반드시 감소하지는 않습니다.
할당된 데이터 공간 데이터베이스 데이터 저장에 사용할 수 있는 형식화된 파일 공간의 크기입니다. 할당된 공간의 크기는 자동으로 증가하지만 삭제 후에는 감소하지 않습니다. 이 동작은 공간을 다시 형식화할 필요가 없기 때문에 향후 삽입이 더 빨라질 수 있습니다.
할당되었지만 사용되지 않은 데이터 공간 할당된 데이터 공간의 크기와 사용된 데이터 공간 간의 차이입니다. 이 수량은 데이터베이스 데이터 파일을 축소하면 회수할 수 있는 사용 가능한 공간의 최대 크기를 나타냅니다.
데이터 최대 크기 데이터베이스 데이터 저장에 사용할 수 있는 최대 공간의 크기입니다. 할당된 데이터 공간 크기는 데이터 최대 크기를 초과할 수 없습니다.

다음 다이어그램에서는 데이터베이스에 대한 여러 스토리지 공간 유형 간의 관계를 보여줍니다.

데이터베이스 수량 테이블에서 크기 차이 데이터베이스 공간 개념을 보여 주는 다이어그램.

파일 공간 정보를 위해 단일 데이터베이스 쿼리

sys.database_files에 대한 다음 쿼리를 사용하여 할당 데이터베이스 파일 공간 크기 및 사용하지 않은 할당 공간 크기를 반환합니다. 쿼리 결과의 단위는 MB입니다.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

탄력적 풀을 위한 저장소 공간 유형 이해

다음 스토리지 공간 수량을 이해하는 것은 탄력적 풀의 파일 공간을 관리하는 데 중요합니다.

탄력적 풀 수량 정의 주석
사용된 데이터 공간 탄력적 풀에서 모든 데이터베이스에 사용되는 데이터 공간의 합계입니다.
할당된 데이터 공간 탄력적 풀에서 모든 데이터베이스에 할당된 데이터 공간의 합계입니다.
할당되었지만 사용되지 않은 데이터 공간 탄력적 풀에서 모든 데이터베이스에 할당된 데이터 공간의 크기와 사용된 데이터 공간 간의 차이입니다. 이 수량은 데이터베이스 데이터 파일을 축소하면 회수할 수 있는 탄력적 풀에 대해 할당된 공간의 최대 크기를 나타냅니다.
데이터 최대 크기 해당 데이터베이스 모두에 대해 탄력적 풀에서 사용할 수 있는 최대 데이터 공간의 크기입니다. 탄력적 풀에 할당된 공간은 탄력적 풀 최대 크기를 초과할 수 없습니다. 이 상태가 발생하면 데이터베이스 데이터 파일을 축소하여 사용되지 않은 할당된 공간을 회수할 수 있습니다.

참고 항목

"탄력적 풀이 스토리지 한도에 도달했습니다."라는 오류 메시지는 데이터베이스 개체가 탄력적 풀 스토리지 제한을 충족하기에 충분한 공간을 할당했지만 데이터 공간 할당에 사용되지 않는 공간이 있을 수 있음을 나타냅니다. 탄력적 풀의 저장 한도를 늘리거나 단기적인 해결책으로 사용하지 않은 할당한 공간 회수의 샘플을 사용하여 데이터 공간을 확보하는 것을 고려하세요. 또한 데이터베이스 파일 축소가 잠재적으로 성능에 부정적인 영향을 미칠 수 있음을 알고 있어야 합니다. 축소 후 인덱스 유지 관리를 참조하세요.

스토리지 공간 정보를 탄력적 풀에 쿼리

탄력적 풀에 대한 스토리지 공간 수량을 확인하려면 다음 쿼리를 사용할 수 있습니다.

사용되는 탄력적 풀 데이터 공간

다음 쿼리를 수정하여 사용된 탄력적 풀 데이터 공간의 크기를 반환합니다. 쿼리 결과의 단위는 MB입니다.

-- Connect to master
-- Elastic pool data space used in MB  
SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

할당된 탄력적 풀 데이터 공간 및 사용되지 않은 공간

다음 예제를 수정하여 탄력적 풀의 각 데이터베이스에 대해 할당된 공간 및 사용되지 않은 할당된 공간이 나열된 테이블을 반환합니다. 테이블에는 데이터베이스가 사용되지 않은 할당된 공간이 가장 큰 것에서 사용되지 않은 할당된 공간이 가장 작은 순서로 정렬됩니다. 쿼리 결과의 단위는 MB입니다.

풀의 각 데이터베이스에 할당된 공간을 확인하는 쿼리 결과를 함께 추가하여 탄력적 풀에 대한 할당된 총 공간을 확인할 수 있습니다. 할당된 탄력적 풀 공간은 탄력적 풀 최대 크기를 초과할 수 없습니다.

중요

PowerShell Azure Resource Manager 모듈은 여전히 Azure SQL Database에서 지원되지만 향후의 모든 개발은 Az.Sql 모듈을 위한 것입니다. AzureRM 모듈은 적어도 2020년 12월까지 버그 수정을 계속 수신할 예정입니다. Az 모듈 및 AzureRm 모듈의 명령에 대한 인수는 실질적으로 동일합니다. 호환성에 대한 자세한 내용은 새로운 Azure PowerShell Az 모듈 소개를 참조하세요.

PowerShell 스크립트를 사용하려면 SQL Server PowerShell 모듈이 필요합니다. 설치하려면 PowerShell 모듈 다운로드를 참조하세요.

$resourceGroupName = "<resourceGroupName>"
$serverName = "<serverName>"
$poolName = "<poolName>"
$userName = "<userName>"
$password = "<password>"

# get list of databases in elastic pool
$databasesInPool = Get-AzSqlElasticPoolDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -ElasticPoolName $poolName
$databaseStorageMetrics = @()

# for each database in the elastic pool, get space allocated in MB and space allocated unused in MB
foreach ($database in $databasesInPool) {
    $sqlCommand = "SELECT DB_NAME() as DatabaseName, `
    SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, `
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB `
    FROM sys.database_files `
    GROUP BY type_desc `
    HAVING type_desc = 'ROWS'"
    $serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
    $databaseStorageMetrics = $databaseStorageMetrics + 
        (Invoke-Sqlcmd -ServerInstance $serverFqdn -Database $database.DatabaseName `
            -Username $userName -Password $password -Query $sqlCommand)
}

# display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort -Property DatabaseDataSpaceAllocatedUnusedInMB -Descending | Format-Table

다음 스크린샷은 스크립트 출력의 예입니다.

탄력적 풀 할당 공간 및 사용되지 않는 할당 공간을 보여 주는 관련 PowerShell cmdlet의 출력 스크린샷입니다.

탄력적 풀 데이터 최대 크기

다음 T-SQL 쿼리를 수정하여 마지막으로 기록된 탄력적 풀 데이터 최대 크기를 반환합니다. 쿼리 결과의 단위는 MB입니다.

-- Connect to master
-- Elastic pools max size in MB
SELECT TOP 1 elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

사용되지 않는 할당된 공간 회수

중요

축소 명령은 실행하는 동안 데이터베이스 성능에 영향을 주므로, 가능하면 사용량이 낮은 기간 동안 실행해야 합니다.

데이터 파일 축소

데이터베이스 성능에 영향을 미칠 수 있기 때문에 Azure SQL Database는 데이터 파일을 자동으로 축소하지 않습니다. 그러나 고객은 선택한 시간에 셀프 서비스를 통해 데이터 파일을 축소할 수 있습니다. 이는 정기 예약 작업이 아니라, 데이터 파일 사용 공간 사용량이 크게 감소하는 상황에 대처하는 일회성 이벤트여야 합니다.

팁

일반 애플리케이션 워크로드로 인해 파일이 동일한 할당된 크기로 다시 증가하는 경우 데이터 파일을 축소하지 않는 것이 좋습니다.

Azure SQL Database에서 파일을 축소하려면 DBCC SHRINKDATABASE 또는 DBCC SHRINKFILE 명령을 사용할 수 있습니다.

  • DBCC SHRINKDATABASE는 단일 명령을 사용하여 데이터베이스의 모든 데이터 및 로그 파일을 축소합니다. 이 명령은 한 번에 하나의 데이터 파일을 축소하므로 더 큰 데이터베이스의 경우 시간이 오래 걸릴 수 있습니다. 또한 Azure SQL Database에서 필요에 따라 자동으로 로그 파일을 축소하므로 일반적으로 불필요한 로그 파일을 축소합니다.
  • DBCC SHRINKFILE 명령은 고급 시나리오를 지원합니다.
    • 데이터베이스의 모든 파일을 축소하기보다, 필요에 따라 개별 파일을 대상으로 지정할 수 있습니다.
    • 각 DBCC SHRINKFILE 명령은 다른 DBCC SHRINKFILE 명령과 동시에 실행하여 여러 파일을 동시에 축소하고 총 축소 시간을 줄일 수 있습니다. 그러나 축소하는 동안 실행되는 경우 리소스 사용량이 증가하고 사용자 쿼리를 차단할 가능성이 높아집니다.
      • 여러 데이터 파일을 동시에 축소하면 축소 작업을 더 빠르게 완료할 수 있습니다. 동시 데이터 파일 축소를 사용하는 경우 한 축소 요청에 의해 다른 축소 요청이 일시적인 차단되는 것을 관찰할 수 있습니다.
    • 파일의 끝에 데이터가 없으면 TRUNCATEONLY 인수를 지정하여 할당된 파일 크기를 훨씬 더 빠르게 줄일 수 있습니다. 여기에는 파일 내 데이터 이동이 필요하지 않습니다.
  • 이러한 축소 명령에 대한 자세한 내용은 DBCC SHRINKDATABASE 및 DBCC SHRINKFILE을 참조하세요.
  • 데이터베이스 및 파일 축소 작업은 Azure SQL Database 하이퍼스케일의 미리 보기에서 지원됩니다. 자세한 내용은 Azure SQL Database Hyperscale 축소를 참조하세요.

다음 예제는 master 데이터베이스가 아닌 대상 사용자 데이터베이스에 연결된 동안 실행해야 합니다.

DBCC SHRINKDATABASE를 사용하여 지정된 데이터베이스의 모든 데이터 및 로그 파일을 축소하려면 다음을 수행합니다.

-- Shrink database data space allocated.
DBCC SHRINKDATABASE (N'database_name');

Azure SQL Database에서 데이터베이스에는 데이터가 증가함에 따라 자동으로 만들어지는 하나 이상의 데이터 파일이 있을 수 있습니다. 각 파일의 사용된 크기 및 할당된 크기를 포함하여 데이터베이스의 파일 레이아웃을 확인하려면 다음 샘플 스크립트를 사용하여 sys.database_files 카탈로그 보기를 쿼리합니다.

-- Review file properties, including file_id and name values to reference in shrink commands
SELECT file_id,
       name,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024. AS space_used_mb,
       CAST(size AS bigint) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS bigint) * 8 / 1024. AS max_file_size_mb
FROM sys.database_files
WHERE type_desc IN ('ROWS','LOG');

하나의 파일에 대한 축소는 DBCC SHRINKFILE 명령을 통해서만 실행할 수 있습니다. 예를 들어 다음과 같습니다.

-- Shrink database data file named 'data_0` by removing all unused at the end of the file, if any.
DBCC SHRINKFILE ('data_0', TRUNCATEONLY);
GO

데이터베이스 파일 축소가 잠재적으로 성능에 부정적인 영향을 미칠 수 있음을 알고 있어야 합니다. 축소 후 인덱스 유지 관리를 참조하세요.

트랜잭션 로그 파일 축소

데이터 파일과 달리 Azure SQL Database는 공간 부족 오류를 일으킬 수 있는 과도한 공간 사용을 방지하기 위해 트랜잭션 로그 파일을 자동으로 축소합니다. 일반적으로 고객은 트랜잭션 로그 파일을 축소할 필요가 없습니다.

프리미엄 및 중요 비즈니스용 서비스 계층에서 트랜잭션 로그가 커지면 로컬 스토리지 사용량이 최대 로컬 스토리지 한도에 크게 영향을 미칠 수 있습니다. 로컬 스토리지 사용량이 한도에 가까운 경우 고객은 다음 예제와 같이 DBCC SHRINKFILE 명령을 사용하여 트랜잭션 로그를 축소하도록 선택할 수 있습니다. 이렇게 하면 정기적 자동 축소 작업을 기다리지 않아도 명령이 완료되는 즉시 로컬 스토리지의 공간이 확보됩니다.

다음 예제는 master 데이터베이스가 아닌 대상 사용자 데이터베이스에 연결된 상태에서 실행해야 합니다.

-- Shrink the database log file (always file_id 2), by removing all unused space at the end of the file, if any.
DBCC SHRINKFILE (2, TRUNCATEONLY);

자동 축소

데이터 파일을 수동으로 축소하는 대신 데이터베이스에 대해 자동 축소를 사용하도록 설정할 수 있습니다. 그러나 자동 축소의 파일 공간 회수 효과는 DBCC SHRINKDATABASE 및 DBCC SHRINKFILE보다 떨어질 수 있습니다.

기본적으로 대부분의 데이터베이스는 자동 축소를 사용하지 않도록 설정되어 있습니다. 자동 축소를 사용하도록 설정해야 하는 경우 공간 관리 목표를 달성한 후 영구적으로 사용하도록 설정하는 대신 사용하지 않도록 설정하는 것이 좋습니다. 자세한 내용은 AUTO_SHRINK 고려 사항을 참조하세요.

예를 들어 자동 축소는 사용된 데이터 파일 공간이 크게 증가하고 감소하여 풀이 최대 크기 제한에 도달하는 많은 데이터베이스가 탄력적 풀에 포함된 특정 시나리오에서 유용할 수 있습니다. 일반적인 시나리오는 아닙니다.

자동 축소를 사용하도록 설정하려면 master 데이터베이스가 아닌 데이터베이스에 연결된 상태에서 다음 명령을 실행합니다.

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT SET AUTO_SHRINK ON;

이 명령에 대한 자세한 내용은 DATABASE SET 옵션을 참조하세요.

축소 후 인덱스 유지 관리

데이터 파일에 대한 축소 작업이 완료되면 인덱스가 조각화될 수 있습니다. 이로 인해 대규모 검사를 사용하는 쿼리와 같은 특정 워크로드에 대한 성능 최적화 효과가 줄어듭니다. 축소 작업 완료 후 성능 저하가 발생하면 인덱스 유지 관리를 통해 인덱스를 다시 빌드하는 것을 고려해 봅니다. 인덱스를 다시 빌드하려면 데이터베이스에 사용 가능한 공간이 필요하므로 할당된 공간이 증가하여 축소 효과가 무효화될 수 있습니다.

인덱스 유지 관리에 대한 자세한 내용은 쿼리 성능 향상 및 리소스 소비 감소를 위한 인덱스 유지 관리 최적화를 참조하세요.

큰 데이터베이스 축소

데이터베이스 할당 공간이 수백 기가바이트 이상인 경우 축소를 완료하는 데 상당한 시간이 필요할 수 있으며, 다중 테라바이트 데이터베이스의 경우 몇 시간 또는 며칠로 측정될 수 있습니다. 이 프로세스를 더 효율적으로 만들고 애플리케이션 워크로드에 미치는 영향을 더 줄이는 데 사용할 수 있는 프로세스 최적화 및 모범 사례가 있습니다.

공간 사용량 기준 캡처

축소를 시작하려면 먼저 다음 공간 사용량 쿼리를 실행하여 각 데이터베이스 파일에서 현재 사용된 공간 및 할당된 공간을 캡처합니다.

SELECT file_id,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024. AS space_used_mb,
       CAST(size AS bigint) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS bigint) * 8 / 1024. AS max_size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

축소가 완료되면 이 쿼리를 다시 실행하여 결과를 초기 기준과 비교할 수 있습니다.

데이터 파일 자르기

먼저 TRUNCATEONLY 매개 변수를 사용하여 각 데이터 파일에 대해 축소를 실행하는 것이 좋습니다. 이렇게 하면 파일 끝에 할당되었지만 사용하지 않은 공간이 있는 경우 데이터 이동 없이 빠르게 이를 제거합니다. 다음 샘플 명령은 file_id가 4인 데이터 파일을 자릅니다.

DBCC SHRINKFILE (4, TRUNCATEONLY);

이 명령이 모든 데이터 파일에 대해 실행되면 공간 사용량 쿼리를 다시 실행하여 할당된 공간이 감소한 경우 이를 확인할 수 있습니다. Azure Portal에서도 데이터베이스에 할당된 공간을 확인할 수 있습니다.

인덱스 페이지 밀도 평가

데이터 파일을 잘라도 할당된 공간이 충분히 줄어들지 않으면 데이터 파일을 축소해야 합니다. 그러나 선택적이지만 권장되는 단계로, 먼저 데이터베이스의 인덱스에 대한 평균 페이지 밀도를 결정해야 합니다. 동일한 양의 데이터의 경우 페이지 밀도가 높으면 더 적은 수의 페이지를 이동해야 하므로 축소가 더 빠르게 완료됩니다. 일부 인덱스에 대한 페이지 밀도가 낮은 경우 먼저 이러한 인덱스에 대한 유지 관리를 수행하여 페이지 밀도를 높인 후에 데이터 파일을 축소하는 것이 좋습니다. 이렇게 하면 축소가 할당된 스토리지 공간을 더 많이 줄일 수도 있습니다.

데이터베이스의 모든 인덱스에 대한 페이지 밀도를 확인하려면 다음 쿼리를 사용합니다. 페이지 밀도는 avg_page_space_used_in_percent 열에 보고됩니다.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

페이지 밀도가 60~70% 미만인 페이지의 수가 많은 인덱스가 있는 경우 데이터 파일을 축소하기 전에 이러한 인덱스를 다시 빌드하거나 다시 구성하는 것이 좋습니다.

참고 항목

더 큰 데이터베이스의 경우 페이지 밀도를 결정하는 쿼리를 완료하는 데 시간이 오래 걸릴 수 있습니다. 또한 큰 인덱스를 다시 빌드하거나 다시 구성하려면 상당한 시간과 리소스 사용량이 필요합니다. 한편으로 페이지 밀도를 높이는 데 추가 시간을 소비하고 다른 한편으로 축소 기간을 줄이고 더 높은 공간 절약을 달성하는 것 사이에는 절충점이 있습니다.

페이지 밀도가 낮은 인덱스가 여러 개 있는 경우 여러 데이터베이스 세션에서 인덱스를 병렬로 다시 빌드하여 프로세스 속도를 높일 수 있습니다. 그러나 이렇게 하여 데이터베이스 리소스 제한에 접근하지 않는지 확인하고 실행 중인 애플리케이션 워크로드에 충분한 리소스 헤드룸을 남겨 둡니다. Azure Portal 또는 sys.dm_db_resource_stats 보기를 사용하여 리소스 사용량(CPU, 데이터 I/O, 로그 I/O)을 모니터링하고, 이러한 각 차원의 리소스 사용률이 100%보다 훨씬 낮게 유지되는 경우에만 추가 병렬 다시 빌드를 시작합니다. CPU, 데이터 I/O 또는 로그 I/O 사용률이 100%인 경우 더 많은 CPU 코어를 확보하고 I/O 처리량을 늘리도록 데이터베이스를 스케일 업할 수 있습니다. 이렇게 하면 추가 병렬 다시 작성을 통해 프로세스를 더 빠르게 완료할 수 있습니다.

샘플 인덱스 다시 빌드 명령

ALTER INDEX 문을 사용하여 인덱스를 다시 빌드하고 해당 페이지의 밀도를 높이는 샘플 명령은 다음과 같습니다.

ALTER INDEX [index_name] ON [schema_name].[table_name] 
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8, 
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), 
RESUMABLE = ON);

이 명령은 온라인 및 다시 시작 가능한 인덱스 다시 빌드를 시작합니다. 이렇게 하면 다시 빌드가 진행되는 동안 동시 워크로드에서 테이블을 계속 사용할 수 있으며, 어떤 이유로든 중단된 경우 다시 빌드를 다시 시작할 수 있습니다. 그러나 이 유형의 다시 빌드는 테이블에 대한 액세스를 차단하는 오프라인 다시 빌드보다 느립니다. 다시 빌드하는 동안 다른 워크로드에서 테이블에 액세스할 필요가 없는 경우 ONLINE 및 RESUMABLE 옵션을 OFF로 설정하고 WAIT_AT_LOW_PRIORITY 절을 제거합니다.

인덱스 유지 관리에 대한 자세한 내용은 쿼리 성능 향상 및 리소스 소비 감소를 위한 인덱스 유지 관리 최적화를 참조하세요.

여러 데이터 파일 축소

앞에서 설명한 대로 데이터 이동을 통한 축소는 장기 프로세스입니다. 데이터베이스에 여러 데이터 파일이 있는 경우 여러 데이터 파일을 병렬로 축소하여 프로세스 속도를 높일 수 있습니다. 이 작업은 여러 데이터베이스 세션을 열고 각 세션에서 file_id 값이 다른 DBCC SHRINKFILE을 사용하여 수행합니다. 이전에 인덱스를 다시 빌드하는 경우와 마찬가지로 먼저 충분한 리소스 가용 공간(CPU, 데이터 I/O, 로그 I/O)이 있는지 확인한 후에 새 병렬 축소 명령을 시작해야 합니다.

다음 샘플 명령은 file_id 4로 데이터 파일을 축소하여 파일 내에서 페이지를 이동하여 할당된 크기를 52,000MB로 줄이려고 합니다.

DBCC SHRINKFILE (4, 52000);

파일에 할당된 공간을 가능한 최소로 줄이려면 대상 크기를 지정하지 않고 다음 명령문을 실행합니다.

DBCC SHRINKFILE (4);

워크로드가 축소와 함께 동시에 실행되는 경우 축소가 완료되고 파일이 잘리기 전에 축소를 통해 확보된 스토리지 공간의 사용을 시작할 수 있습니다. 이 경우 축소는 지정된 대상에 할당된 공간을 줄일 수 없습니다.

이는 각 파일을 더 작은 단계로 축소하여 완화할 수 있습니다. 즉, 기준 공간 사용량 쿼리 결과에서 볼 수 있듯이 DBCC SHRINKFILE 명령에서 파일에 대해 현재 할당된 공간보다 약간 작은 대상을 설정합니다. 예를 들어 file_id가 4인 파일에 할당된 공간이 200,000MB이고 이를 100,000MB로 축소하려는 경우 먼저 대상을 170,000MB로 설정할 수 있습니다.

DBCC SHRINKFILE (4, 170000);

이 명령이 완료되면 파일이 잘리고 할당된 크기가 170,000MB로 줄어듭니다. 그러면 파일이 원하는 크기로 축소될 때까지 먼저 대상을 140,000MB로 설정하고, 다음으로 110,000MB 등으로 설정하면서 이 명령을 반복할 수 있습니다. 명령이 완료되었지만 파일이 잘리지 않은 경우 더 작은 단계(예: 30,000MB가 아닌 15,000MB)를 사용합니다.

동시에 실행되는 모든 축소 세션에 대한 축소 진행률을 모니터링하려면 다음 쿼리를 사용할 수 있습니다.

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

참고 항목

축소 진행률은 비선형일 수 있으며, 축소가 아직 진행 중인 경우에도 percent_complete 열의 값이 오랜 기간 동안 거의 변경되지 않은 상태로 유지될 수 있습니다.

모든 데이터 파일에 대한 축소가 완료되면 공간 사용량 쿼리를 다시 실행(또는 Azure Portal에서 확인)하여 할당된 스토리지 크기가 감소되었는지 확인합니다. 사용한 공간과 할당한 공간 사이의 격차가 여전히 큰 경우 앞서 설명한 대로 인덱스를 다시 빌드할 수 있습니다. 이렇게 하면 할당된 공간이 일시적으로 더 늘어날 수 있지만, 인덱스를 다시 빌드한 후 데이터 파일을 다시 축소하면 할당된 공간이 더 많이 줄어듭니다.

축소 중 일시적인 오류

경우에 따라 시간 제한 및 교착 상태와 같은 다양한 오류로 축소 명령이 실패할 수 있습니다. 일반적으로 이러한 오류는 일시적이며, 동일한 명령을 반복하면 다시 발생하지 않습니다. 오류로 인해 축소가 실패하면 데이터 페이지 이동에서 지금까지 수행한 진행률이 유지되고, 동일한 축소 명령을 다시 실행하여 파일을 계속 축소할 수 있습니다.

다음 샘플 스크립트에서는 시간 제한 오류 또는 교착 상태 오류가 발생할 때 구성 가능한 횟수까지 자동으로 다시 시도하도록 다시 시도 루프에서 축소를 실행하는 방법을 보여줍니다. 이 재시도 방법은 축소 중에 발생할 수 있는 다른 많은 오류에 적용할 수 있습니다.

DECLARE @RetryCount int = 3; -- adjust to configure desired number of retries
DECLARE @Delay char(12);

-- Retry loop
WHILE @RetryCount >= 0
BEGIN

BEGIN TRY

DBCC SHRINKFILE (1); -- adjust file_id and other shrink parameters

-- Exit retry loop on successful execution
SELECT @RetryCount = -1;

END TRY
BEGIN CATCH
    -- Retry for the declared number of times without raising an error if deadlocked or timed out waiting for a lock
    IF ERROR_NUMBER() IN (1205, 49516) AND @RetryCount > 0
    BEGIN
        SELECT @RetryCount -= 1;

        PRINT CONCAT('Retry at ', SYSUTCDATETIME());

        -- Wait for a random period of time between 1 and 10 seconds before retrying
        SELECT @Delay = '00:00:0' + CAST(CAST(1 + RAND() * 8.999 AS decimal(5,3)) AS varchar(5));
        WAITFOR DELAY @Delay;
    END
    ELSE -- Raise error and exit loop
    BEGIN
        SELECT @RetryCount = -1;
        THROW;
    END
END CATCH
END;

시간 제한 및 교착 상태 외에도 알려진 특정 문제로 인해 축소에 오류가 발생할 수 있습니다.

반환되는 오류 및 완화 단계는 다음과 같습니다.

  • 오류 번호: 49503, 오류 메시지: %.*ls: 페이지 %d:%d은(는) 행 외부 영구 버전 저장소 페이지이므로 이동할 수 없습니다. 페이지 정지 이유: %ls. 페이지 정지 타임스탬프: %I64d.

행 버전을 PVS(영구 버전 저장소)에 생성한 장기 실행 활성 트랜잭션이 있는 경우 이 오류가 발생합니다. 이러한 행 버전이 포함된 페이지는 축소를 통해 이동할 수 없으므로 진행되지 않고 이 오류와 함께 실패합니다.

완화하려면 이러한 장기 실행 트랜잭션이 완료될 때까지 기다려야 합니다. 또는 이러한 장기 실행 트랜잭션을 식별하고 종료할 수 있지만 트랜잭션 오류를 정상적으로 처리하지 않는 경우 애플리케이션에 영향을 줄 수 있습니다. 장기 실행 트랜잭션을 찾는 한 가지 방법은 축소 명령을 실행한 데이터베이스에서 다음 쿼리를 실행하는 것입니다.

-- Transactions sorted by duration
SELECT st.session_id,
       dt.database_transaction_begin_time,
       DATEDIFF(second, dt.database_transaction_begin_time, CURRENT_TIMESTAMP) AS transaction_duration_seconds,
       dt.database_transaction_log_bytes_used,
       dt.database_transaction_log_bytes_reserved,
       st.is_user_transaction,
       st.open_transaction_count,
       ib.event_type,
       ib.parameters,
       ib.event_info
FROM sys.dm_tran_database_transactions AS dt
INNER JOIN sys.dm_tran_session_transactions AS st
ON dt.transaction_id = st.transaction_id
OUTER APPLY sys.dm_exec_input_buffer(st.session_id, default) AS ib
WHERE dt.database_id = DB_ID()
ORDER BY transaction_duration_seconds DESC;

트랜잭션은 KILL 명령을 사용하고 쿼리 결과에서 연결된 session_id 값을 지정하여 종료할 수 있습니다.

KILL 4242; -- replace 4242 with the session_id value from query results

주의

트랜잭션을 종료하면 워크로드에 부정적인 영향을 줄 수 있습니다.

장기 실행 트랜잭션이 종료되거나 완료되면 일정 시간이 지난 후 내부 백그라운드 작업에서 더 이상 필요하지 않은 행 버전을 정리합니다. 다음 쿼리를 통해 PVS 크기를 모니터링하여 정리 진행률을 측정할 수 있습니다. 축소 명령을 실행한 데이터베이스에서 이 쿼리를 실행합니다.

SELECT pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
       pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
       pvss.current_aborted_transaction_count,
       pvss.aborted_version_cleaner_start_time,
       pvss.aborted_version_cleaner_end_time,
       dt.database_transaction_begin_time AS oldest_transaction_begin_time,
       asdt.session_id AS active_transaction_session_id,
       asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds
FROM sys.dm_tran_persistent_version_store_stats AS pvss
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

persistent_version_store_size_gb 열에 보고된 PVS 크기가 원래 크기에 비해 크게 줄어들었으면 축소 다시 실행이 성공한 것입니다.

  • 오류 번호: 5223, 오류 메시지: %.*ls: 빈 페이지 %d:%d을(를) 할당 취소할 수 없습니다.

이 오류는 다음과 같은 ALTER INDEX지속적인 인덱스 유지 관리 작업이 있는 경우에 발생할 수 있습니다. 이러한 작업이 완료되면 축소 명령을 다시 시도합니다.

이 오류가 지속되면 연결된 인덱스를 다시 빌드해야 할 수 있습니다. 다시 빌드할 인덱스를 찾으려면 축소 명령을 실행한 동일한 데이터베이스에서 다음 쿼리를 실행합니다.

SELECT OBJECT_SCHEMA_NAME(pg.object_id) AS schema_name,
       OBJECT_NAME(pg.object_id) AS object_name,
       i.name AS index_name,
       p.partition_number
FROM sys.dm_db_page_info(DB_ID(), <file_id>, <page_id>, default) AS pg
INNER JOIN sys.indexes AS i
ON pg.object_id = i.object_id
   AND
   pg.index_id = i.index_id
INNER JOIN sys.partitions AS p
ON pg.partition_id = p.partition_id;

이 쿼리를 실행하기 전에 <file_id> 및 <page_id> 자리 표시자를 받은 오류 메시지의 실제 값으로 바꿉니다. 예를 들어 빈 페이지 1:62669을(를) 할당 취소할 수 없습니다.라는 메시지인 경우 <file_id>는 1이고 <page_id>는 62669입니다.

쿼리에서 식별된 인덱스를 다시 빌드하고 축소 명령을 다시 시도합니다.

  • 오류 번호: 5201, 오류 메시지: DBCC SHRINKDATABASE: 데이터베이스 ID %d의 파일 ID %d에 빈 공간이 충분하지 않아 이 파일을 건너뛰었습니다.

이 오류는 데이터 파일을 더 이상 축소할 수 없음을 의미합니다. 다음 데이터 파일로 이동할 수 있습니다.

관련 콘텐츠

최대 데이터베이스 크기에 대한 정보는 다음을 참조하세요.