Gestire lo spazio dei file per i database in database SQL di Azure

Si applica a: Database SQL di Azure

Questo articolo descrive diversi tipi di spazio di archiviazione per i database in Azure SQL Database e i passaggi che possono essere eseguiti quando lo spazio file allocato deve essere gestito in modo esplicito.

Nota

Questo articolo non si applica a Istanza gestita di SQL di Azure.

Panoramica

Nel database SQL di Azure sono disponibili modelli di carico di lavoro in cui l'allocazione dei file di dati sottostanti per i database può superare la quantità di pagine di dati usate. Questa condizione si può verificare quando lo spazio usato aumenta e i dati vengono successivamente eliminati. Ciò è dovuto al fatto che lo spazio file allocato non viene recuperato automaticamente quando i dati vengono eliminati.

Può essere necessario monitorare l'utilizzo dello spazio file e compattare i file di dati per:

  • Consentire l'aumento delle dimensioni dei dati in un pool elastico quando lo spazio file allocato per i relativi database raggiunge le dimensioni massime del pool.
  • Consentire la riduzione delle dimensioni massime di un database singolo o di un pool elastico.
  • Consentire il passaggio di un database singolo o di un pool elastico a un livello di servizio o a un livello di prestazioni diverso con dimensioni massime inferiori.

Nota

Le operazioni di compattazione non devono essere considerate un'operazione di manutenzione regolare. I file di dati e di log che aumentano a causa di operazioni aziendali regolari e ricorrenti non richiedono operazioni di compattazione.

Monitoraggio dell'utilizzo dello spazio file

La maggior parte delle metriche dello spazio di archiviazione visualizzate nelle API seguenti misura solo le dimensioni delle pagine di dati usate:

  • API per le metriche basate su Azure Resource Manager tra cui get-metrics di PowerShell

Le API seguenti misurano invece anche le dimensioni dello spazio allocato per i database e i pool elastici:

Informazioni sui tipi di spazio di archiviazione per un database

La comprensione delle quantità di spazio di archiviazione seguenti è importante per la gestione dello spazio file di un database.

Quantità di database Definizione Commenti
Spazio dati usato Quantità di spazio utilizzata per archiviare i dati del database. In genere, lo spazio usato aumenta quando vengono inseriti i dati e diminuisce quando vengono eliminati. In alcuni casi, lo spazio usato non cambia in caso di inserimenti o eliminazioni, a seconda della quantità e del modello di dati coinvolti nell'operazione e dell'eventuale frammentazione. Ad esempio, se si elimina una riga da ogni pagina di dati, non si riduce necessariamente lo spazio usato.
Spazio dati allocato Quantità di spazio file formattato messo a disposizione per l'archiviazione dei dati del database. La quantità di spazio allocato aumenta automaticamente, ma non diminuisce mai dopo le eliminazioni. Questo comportamento assicura che gli inserimenti futuri avvengano più velocemente, perché non è necessario riformattare lo spazio.
Spazio dati allocato ma non usato Differenza tra la quantità di spazio dati allocato e lo spazio dati usato. Questa quantità rappresenta la quantità massima di spazio libero che può essere recuperata compattando i file di dati del database.
Dimensioni massime dei dati Quantità massima di spazio che può essere usata per l'archiviazione dei dati del database. La quantità di spazio dati allocato non può superare le dimensioni massime dei dati.

Il diagramma seguente illustra la relazione tra i diversi tipi di spazio di archiviazione per un database.

Tipi di spazio di archiviazione e relazioni

Eseguire una query su un database singolo per ottenere informazioni sullo spazio di archiviazione

Per determinare le quantità di spazio di archiviazione per un database singolo, è possibile usare le query seguenti.

Spazio dati del database usato

Modificare la query seguente per restituire la quantità di spazio dati del database usato. L'unità di misura dei risultati di query è costituita da MB.

-- Connect to master
-- Database data space used in MB
SELECT TOP 1 storage_in_megabytes AS DatabaseDataSpaceUsedInMB
FROM sys.resource_stats
WHERE database_name = 'db1'
ORDER BY end_time DESC;

Spazio di dati del database allocato e spazio allocato non usato

Usare la query seguente per restituire la quantità di spazio dati di database allocato e la quantità di spazio non usato allocato. L'unità di misura dei risultati di query è costituita da MB.

-- Connect to database
-- Database data space allocated in MB and database data space allocated unused in MB
SELECT 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';

Dimensioni massime dei dati del database

Modificare la query seguente per restituire le dimensioni massime dei dati del database. L'unità di misura dei risultati di query è costituita da byte.

-- Connect to database
-- Database data max size in bytes
SELECT DATABASEPROPERTYEX('db1', 'MaxSizeInBytes') AS DatabaseDataMaxSizeInBytes;

Informazioni sui tipi di spazio di archiviazione per un pool elastico

La comprensione delle quantità di spazio di archiviazione seguenti è importante per la gestione dello spazio file di un pool elastico.

Quantità di pool elastico Definizione Commenti
Spazio dati usato Somma dello spazio dati usato da tutti i database nel pool elastico.
Spazio dati allocato Somma dello spazio dati allocato da tutti i database nel pool elastico.
Spazio dati allocato ma non usato Differenza tra la quantità di spazio dati allocato e lo spazio dati usato da tutti i database nel pool elastico. Questa quantità rappresenta la quantità massima di spazio allocato per il pool elastico che può essere recuperata compattando i file di dati del database.
Dimensioni massime dei dati Quantità massima di spazio dati che può essere usata dal pool elastico per tutti i rispettivi database. Lo spazio allocato per il pool elastico non deve superare le dimensioni massime del pool elastico. Se si verifica questa condizione, lo spazio allocato e non usato può essere recuperato compattando i file di dati del database.

Nota

Il messaggio di errore "Il pool elastico ha raggiunto il limite di archiviazione" indica che gli oggetti di database sono stati allocati spazio sufficiente per soddisfare il limite di archiviazione del pool elastico, ma potrebbe esserci spazio inutilizzato nell'allocazione dello spazio dati. Prendere in considerazione l'aumento del limite di archiviazione del pool elastico o come soluzione a breve termine, liberando spazio dati usando la sezione Recuperare spazio allocato inutilizzato di seguito. È anche necessario tenere presente il potenziale impatto negativo sulle prestazioni della compattazione dei file di database. Vedere la sezione Manutenzione degli indici dopo la compattazione riportata di seguito.

Eseguire una query su un pool elastico per ottenere informazioni sullo spazio di archiviazione

Le query seguenti possono essere usate per determinare le quantità di spazio di archiviazione per un pool elastico.

Spazio dati del pool elastico usato

Modificare la query seguente per restituire la quantità di spazio dati del pool elastico usato. L'unità di misura dei risultati di query è costituita da 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;

Spazio di dati del pool elastico allocato e spazio allocato non usato

Modificare gli esempi seguenti per restituire una tabella che elenca lo spazio allocato e lo spazio allocato inutilizzato per ogni database in un pool elastico. La tabella dispone i database in ordine decrescente in base alla quantità di spazio allocato non usato. L'unità di misura dei risultati di query è costituita da MB.

I risultati delle query per determinare lo spazio allocato per ogni database nel pool possono essere sommati per determinare lo spazio totale allocato per il pool elastico. Lo spazio allocato del pool elastico non deve superare le dimensioni massime del pool elastico.

Importante

Il modulo Azure Resource Manager di PowerShell è ancora supportato da Database SQL di Azure, ma tutte le attività di sviluppo future sono incentrate sul modulo Az.Sql. Il modulo AzureRM continuerà a ricevere correzioni di bug almeno fino a dicembre 2020. Gli argomenti per i comandi nei moduli Az e AzureRm sono sostanzialmente identici. Per altre informazioni sulla compatibilità, vedere Introduzione del nuovo modulo Az di Azure PowerShell.

Lo script di PowerShell richiede il modulo SQL Server PowerShell. Vedere Scaricare il modulo PowerShell per l'installazione.

$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

Lo screenshot seguente mostra un esempio di output dello script:

Esempio di spazio allocato del pool elastico e spazio allocato non usato

Dimensioni massime dei dati del pool elastico

Modificare la query T-SQL seguente per restituire l'ultima dimensione massima dei dati del pool elastico registrata. L'unità di misura dei risultati di query è costituita da 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;

Recuperare lo spazio allocato non usato

Importante

I comandi di compattazione influiscono sulle prestazioni del database mentre è in esecuzione e, se possibile, dovrebbero essere eseguiti in periodi di utilizzo ridotto.

Compattare i file di dati

A causa di un potenziale impatto sulle prestazioni del database, Azure SQL Database non riduce automaticamente i file di dati. Tuttavia, i clienti possono ridurre i file di dati tramite self-service al momento della scelta. Questo non deve essere un'operazione pianificata regolarmente, ma piuttosto un evento una sola volta in risposta a una riduzione importante del consumo di spazio usato nel file di dati.

Suggerimento

Non è consigliabile compattare i file di dati se il normale carico di lavoro dell'applicazione causa l'aumento delle dimensioni dei file fino a raggiungere le stesse dimensioni allocate.

In Azure SQL Database per compattare i file è possibile usare DBCC SHRINKDATABASE o DBCC SHRINKFILE comandi:

  • DBCC SHRINKDATABASE compatta tutti i dati e i file di log in un database usando un singolo comando. Il comando compatta un file di dati alla volta, che può richiedere molto tempo per i database più grandi. Compatta anche il file di log, che in genere non è necessario perché Azure SQL database riduce automaticamente i file di log in base alle esigenze.
  • DBCC SHRINKFILE il comando supporta scenari più avanzati:
    • Può indirizzare singoli file in base alle esigenze, anziché compattare tutti i file nel database.
    • Ogni DBCC SHRINKFILE comando può essere eseguito in parallelo con altri DBCC SHRINKFILE comandi per compattare più file contemporaneamente e ridurre il tempo totale di compattazione, a spese dell'utilizzo delle risorse più elevato e una maggiore probabilità di bloccare le query utente, se vengono eseguite durante la riduzione.
    • Se la parte finale del file non contiene dati, può ridurre le dimensioni del file allocato molto più velocemente specificando l'argomento TRUNCATEONLY . Ciò non richiede lo spostamento dei dati all'interno del file.
  • Per altre informazioni su questi comandi di compattazione, vedere DBCC SHRINKDATABASE e DBCC SHRINKFILE.

Gli esempi seguenti devono essere eseguiti durante la connessione al database utente di destinazione, non al master database.

Per usare DBCC SHRINKDATABASE per compattare tutti i dati e i file di log in un determinato database:

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

In Azure SQL Database, un database può avere uno o più file di dati, creato automaticamente quando i dati aumentano. Per determinare il layout dei file del database, incluse le dimensioni usate e allocate di ogni file, eseguire query sulla sys.database_files visualizzazione del catalogo usando lo script di esempio seguente:

-- 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');

È possibile eseguire una compattazione solo su un file tramite il DBCC SHRINKFILE comando, ad esempio:

-- 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

Tenere presente il potenziale impatto negativo sulle prestazioni dei file di database di compattazione, vedere la sezione Manutenzione dell'indice dopo la compattazione seguente.

Compattazione del file di log delle transazioni

A differenza dei file di dati, database SQL di Azure compatta automaticamente i file registro transazioni per evitare un utilizzo eccessivo dello spazio che può causare errori di spazio insufficiente. In genere non è necessario che i clienti compattino il file registro transazioni.

Nei livelli di servizio Premium e business critical, se il log delle transazioni diventa di grandi dimensioni, può contribuire in modo significativo all'utilizzo dell'archiviazione locale verso il limite massimo di archiviazione locale. Se il consumo di archiviazione locale è vicino al limite, i clienti possono scegliere di compattare il log delle transazioni usando il comando DBCC SHRINKFILE , come illustrato nell'esempio seguente. In questo modo l'archiviazione locale viene rilasciata non appena il comando viene completato, senza attendere l'operazione di compattazione automatica periodica.

L'esempio seguente deve essere eseguito durante la connessione al database utente di destinazione, non al database 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);

Compattazione automatica

In alternativa, è possibile ridurre manualmente i file di dati, la compattazione automatica può essere abilitata per un database. Può essere però meno efficace nel recupero dello spazio file rispetto a DBCC SHRINKDATABASE e DBCC SHRINKFILE.

Per impostazione predefinita, la compattazione automatica è disabilitata, consigliata per la maggior parte dei database. Se diventa necessario abilitare la compattazione automatica, è consigliabile disabilitarla una volta che gli obiettivi di gestione dello spazio sono stati raggiunti, anziché mantenerlo abilitato in modo permanente. Per altre informazioni, vedere Considerazioni per AUTO_SHRINK.

Ad esempio, la compattazione automatica può essere utile nello scenario specifico in cui un pool elastico contiene molti database che riscontrano una crescita significativa e una riduzione significativa dello spazio di file di dati usato, causando al pool di raggiungere il limite massimo di dimensioni. Questo non è uno scenario comune.

Per abilitare la compattazione automatica, eseguire il comando seguente durante la connessione al database (non il database master).

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

Per altre informazioni su questo comando, vedere Opzioni ALTER DATABASE SET.

Manutenzione dell'indice dopo la compattazione

Dopo aver completato un'operazione di compattazione rispetto ai file di dati, gli indici possono diventare frammentati. Ciò riduce l'efficacia dell'ottimizzazione delle prestazioni per determinati carichi di lavoro, ad esempio le query che usano analisi di grandi dimensioni. Se la riduzione delle prestazioni si verifica dopo il completamento dell'operazione di compattazione, prendere in considerazione la manutenzione dell'indice per ricompilare gli indici. Tenere presente che la ricompilazione dell'indice richiede spazio libero nel database e quindi può causare l'aumento dello spazio allocato, contrastando l'effetto della compattazione.

Per altre informazioni sulla manutenzione dell'indice, vedere Ottimizzare la manutenzione dell'indice per migliorare le prestazioni delle query e ridurre l'utilizzo delle risorse.

Compattare database di grandi dimensioni

Quando lo spazio allocato del database è in centinaia di gigabyte o superiore, la riduzione può richiedere un tempo significativo per completare, spesso misurati in ore o giorni per i database multi-terabyte. Esistono ottimizzazioni dei processi e procedure consigliate che è possibile usare per rendere questo processo più efficiente e meno efficace per i carichi di lavoro dell'applicazione.

Baseline di utilizzo dello spazio di acquisizione

Prima di iniziare la compattazione, acquisire lo spazio corrente usato e allocato in ogni file di database eseguendo la query di utilizzo dello spazio seguente:

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';

Una volta completata la compattazione, è possibile eseguire nuovamente questa query e confrontare il risultato con la baseline iniziale.

Troncare i file di dati

È consigliabile prima eseguire la compattazione per ogni file di dati con il TRUNCATEONLY parametro . In questo modo, se è presente uno spazio allocato ma inutilizzato alla fine del file, verrà rimosso rapidamente e senza alcun spostamento dei dati. Il comando di esempio seguente tronca il file di dati con file_id 4:

DBCC SHRINKFILE (4, TRUNCATEONLY);

Dopo aver eseguito questo comando per ogni file di dati, è possibile eseguire nuovamente la query di utilizzo dello spazio per visualizzare la riduzione dello spazio allocato, se presente. È anche possibile visualizzare lo spazio allocato per il database in portale di Azure.

Valutare la densità della pagina dell'indice

Se la troncazione dei file di dati non ha comportato una riduzione sufficiente dello spazio allocato, sarà necessario compattare i file di dati. Tuttavia, come passaggio facoltativo ma consigliato, è necessario prima determinare la densità media di pagina per gli indici nel database. Per la stessa quantità di dati, la compattazione verrà completata più velocemente se la densità di pagina è elevata, perché dovrà spostare meno pagine. Se la densità di pagina è bassa per alcuni indici, è consigliabile eseguire la manutenzione in questi indici per aumentare la densità di pagina prima di compattare i file di dati. Ciò consentirà inoltre di ridurre in modo più profondo lo spazio di archiviazione allocato.

Per determinare la densità di pagina per tutti gli indici nel database, usare la query seguente. La densità di avg_page_space_used_in_percent pagina viene segnalata nella colonna.

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;

Se sono presenti indici con numero elevato di pagine con densità di pagina inferiore al 60-70%, prendere in considerazione la ricompilazione o la riorganizzazione di questi indici prima di compattare i file di dati.

Nota

Per i database più grandi, la query per determinare la densità di pagina può richiedere molto tempo (ore) per completare. Inoltre, la ricompilazione o la riorganizzazione di indici di grandi dimensioni richiede anche tempo e utilizzo di risorse sostanziali. C'è un compromesso tra la spesa di tempo aggiuntivo per aumentare la densità di pagina da una parte e ridurre la durata della riduzione e ottenere un risparmio di spazio più elevato su un altro.

Di seguito è riportato un comando di esempio per ricompilare un indice e aumentare la densità di pagina:

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);

Questo comando avvia una ricompilazione dell'indice online e ripristinabile. Ciò consente ai carichi di lavoro simultanei di continuare a usare la tabella mentre la ricompilazione è in corso e consente di riprendere la ricompilazione se viene interrotta per qualsiasi motivo. Tuttavia, questo tipo di ricompilazione è più lento di una ricompilazione offline, che blocca l'accesso alla tabella. Se non sono necessari altri carichi di lavoro per accedere alla tabella durante la ricompilazione, impostare le ONLINE opzioni e RESUMABLE su OFF e rimuovere la WAIT_AT_LOW_PRIORITY clausola.

Se sono presenti più indici con bassa densità di pagina, è possibile ricompilarli in parallelo in più sessioni di database per velocizzare il processo. Assicurarsi tuttavia di non avvicinare i limiti delle risorse del database e lasciare sufficienti le risorse headroom per i carichi di lavoro dell'applicazione che potrebbero essere in esecuzione. Monitorare il consumo di risorse (CPU, I/O dati, I/O log) in portale di Azure o usando la visualizzazione sys.dm_db_resource_stats e avviare ricompilare paralleli aggiuntivi solo se l'utilizzo delle risorse in ognuna di queste dimensioni rimane sostanzialmente inferiore al 100%. Se CPU, I/O dati o utilizzo di I/O log è pari al 100%, è possibile aumentare il database per avere più core CPU e aumentare la velocità effettiva di I/O. Ciò può consentire la ricompilazione parallela aggiuntiva per completare più rapidamente il processo.

Per altre informazioni sulla manutenzione degli indici, vedere Ottimizzare la manutenzione dell'indice per migliorare le prestazioni delle query e ridurre l'utilizzo delle risorse.

Compattare più file di dati

Come indicato in precedenza, la compattazione con lo spostamento dei dati è un processo a esecuzione prolungata. Se il database ha più file di dati, è possibile velocizzare il processo compattando più file di dati in parallelo. A tale scopo, aprire più sessioni di database e usare DBCC SHRINKFILE in ogni sessione con un valore diverso file_id . Analogamente alla ricompilazione degli indici in precedenza, assicurarsi di disporre di risorse sufficienti (CPU, I/O dati, I/O log) prima di avviare ogni nuovo comando di compattazione parallela.

Il comando di esempio seguente riduce il file di dati con file_id 4, tentando di ridurre la dimensione allocata a 52000 MB spostando pagine all'interno del file:

DBCC SHRINKFILE (4, 52000);

Se si vuole ridurre lo spazio allocato per il file al minimo possibile, eseguire l'istruzione senza specificare le dimensioni di destinazione:

DBCC SHRINKFILE (4);

Se un carico di lavoro è in esecuzione simultaneamente con la compattazione, può iniziare a usare lo spazio di archiviazione liberato prima di completare la compattazione e troncare il file. In questo caso, la compattazione non sarà in grado di ridurre lo spazio allocato alla destinazione specificata.

È possibile attenuare questa operazione riducendo ogni file in passaggi più piccoli. Ciò significa che nel comando si imposta la destinazione leggermente inferiore rispetto allo spazio allocato corrente per il file, come illustrato nei DBCC SHRINKFILE risultati della query di utilizzo dello spazio di base. Ad esempio, se lo spazio allocato per il file con file_id 4 è 200.000 MB e si vuole ridurlo a 100.000 MB, è prima possibile impostare la destinazione su 170.000 MB:

DBCC SHRINKFILE (4, 170000);

Al termine di questo comando, il file verrà troncato e ridotto la dimensione allocata a 170.000 MB. È quindi possibile ripetere questo comando, impostando prima la destinazione su 140.000 MB, quindi su 110.000 MB e così via, fino a quando il file non viene ridotto alle dimensioni desiderate. Se il comando viene completato, ma il file non viene troncato, usare passaggi più piccoli, ad esempio 15.000 MB anziché 30.000 MB.

Per monitorare lo stato di avanzamento della compattazione per tutte le sessioni di compattazione simultanee, è possibile usare la query seguente:

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');

Nota

Lo stato di avanzamento della compattazione può essere non lineare e il valore nella percent_complete colonna potrebbe rimanere virtualmente invariato per lunghi periodi di tempo, anche se la riduzione è ancora in corso.

Dopo aver completato la compattazione per tutti i file di dati, eseguire nuovamente la query sull'utilizzo dello spazio (o archiviare portale di Azure) per determinare la riduzione risultante delle dimensioni di archiviazione allocata. Se è insufficiente e esiste ancora una grande differenza tra lo spazio usato e lo spazio allocato, è possibile ricompilare gli indici come descritto in precedenza. Ciò può aumentare temporaneamente lo spazio allocato ulteriormente, tuttavia la compattazione dei file di dati dopo la ricompilazione degli indici dovrebbe comportare una riduzione più profonda dello spazio allocato.

Errori temporanei durante la compattazione

Occasionalmente, un comando di compattazione potrebbe non riuscire con vari errori, ad esempio timeout e deadlock. In generale, questi errori sono temporanei e non si verificano di nuovo se lo stesso comando viene ripetuto. Se la compattazione ha esito negativo con un errore, lo stato di avanzamento apportato finora nelle pagine dei dati in movimento viene mantenuto e lo stesso comando di compattazione può essere eseguito di nuovo per continuare a compattare il file.

Lo script di esempio seguente illustra come eseguire la compattazione in un ciclo di ripetizione dei tentativi per riprovare automaticamente fino a un numero configurabile di volte in cui si verifica un errore di timeout o un errore di deadlock. Questo approccio di ripetizione dei tentativi è applicabile a molti altri errori che possono verificarsi durante la compattazione.

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;

Oltre ai timeout e ai deadlock, la compattazione può riscontrare errori a causa di determinati problemi noti.

Gli errori restituiti e i passaggi di mitigazione sono i seguenti:

  • Numero di errore: 49503, messaggio di errore: %.*ls: Impossibile spostare la pagina %d:%d perché è una pagina dell'archivio delle versioni persistenti fuori riga. Motivo di blocco pagina: %ls. Timestamp di blocco pagina: %I64d.

Questo errore si verifica quando sono presenti transazioni attive a esecuzione prolungata che hanno generato versioni di riga nell'archivio versioni persistenti (PVS). Le pagine contenenti queste versioni di riga non possono essere spostate tramite compattazione, quindi non possono apportare progressi e non riescono con questo errore.

Per attenuare, è necessario attendere il completamento di queste transazioni a esecuzione prolungata. In alternativa, è possibile identificare e terminare queste transazioni a esecuzione prolungata, ma ciò può influire sull'applicazione se non gestisce correttamente gli errori delle transazioni. Un modo per trovare transazioni a esecuzione prolungata consiste nell'eseguire la query seguente nel database in cui è stato eseguito il comando di compattazione:

-- 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;

È possibile terminare una transazione usando il comando e specificando il KILL valore associato session_id dal risultato della query:

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

Attenzione

La terminazione di una transazione può influire negativamente sui carichi di lavoro.

Una volta terminate o completate le transazioni a esecuzione prolungata, un'attività in background interna non eseguirà più la pulizia delle versioni di riga non necessarie dopo qualche tempo. È possibile monitorare le dimensioni di PVS per misurare lo stato di avanzamento della pulizia usando la query seguente. Eseguire la query nel database in cui è stato eseguito il comando di compattazione:

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();

Una volta che le dimensioni pvS segnalate nella persistent_version_store_size_gb colonna sono notevolmente ridotte rispetto alle dimensioni originali, la rirunning shrink dovrebbe avere esito positivo.

  • Numero di errore: 5223, messaggio di errore: %.*ls: Pagina vuota %d:%d non è stato possibile deallocare.

Questo errore può verificarsi se sono presenti operazioni di manutenzione dell'indice in corso, ALTER INDEXad esempio . Riprovare il comando di compattazione dopo il completamento di queste operazioni.

Se questo errore persiste, l'indice associato potrebbe dover essere ricompilato. Per trovare l'indice da ricompilare, eseguire la query seguente nello stesso database in cui è stato eseguito il comando di compattazione:

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;

Prima di eseguire questa query, sostituire i <file_id> segnaposto e <page_id> con i valori effettivi del messaggio di errore ricevuto. Ad esempio, se il messaggio è Vuoto pagina 1:62669 non è stato possibile deallocare, è <file_id> e <page_id> è 162669.

Ricompilare l'indice identificato dalla query e riprovare il comando di compattazione.

  • Numero di errore: 5201, messaggio di errore: DBCC SHRINKDATABASE: ID file %d dell'ID del database %d ignorato perché il file non dispone di spazio sufficiente per recuperare.

Questo errore indica che il file di dati non può essere ridotto ulteriormente. È possibile passare al file di dati successivo.

Passaggi successivi