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 nel database SQL di Azure. Anche se non è raro, questo articolo include passaggi che possono essere eseguiti quando lo spazio file allocato deve essere gestito in modo esplicito.

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 il numero di pagine di dati usate. Questa condizione si può verificare quando lo spazio usato aumenta e i dati vengono poi eliminati. Ciò è dovuto al fatto che lo spazio file allocato non viene recuperato automaticamente quando i dati vengono eliminati.

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

Monitoraggio dell'utilizzo dello spazio dei file

La maggior parte delle metriche per lo spazio di archiviazione visualizzate nel portale di Azure e delle 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:

• T-SQL: sys.resource_stats
• T-SQL: sys.elastic_pool_resource_stats

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.

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

Eseguire una query su un database singolo per ottenere informazioni sullo spazio dei file

Usare la query seguente su sys.database_files per restituire la quantità di spazio per i dati del database allocato e la quantità di spazio inutilizzato allocato. L'unità di misura dei risultati di query è costituita da 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;

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.

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 utilizzato nel pool elastico. 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 totale e lo spazio inutilizzato per ogni database in un pool elastico. La tabella ordina i database da tali database con la maggior quantità di spazio inutilizzato al minimo di spazio inutilizzato. L'unità di misura dei risultati di query è costituita da MB.

Aggiungere i risultati della query per ogni database nel pool 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.

Lo script di PowerShell richiede il modulo PowerShell di SQL Server. Per altre informazioni, vedere modulo powerShell di SQL Server.

Lo script di PowerShell seguente completa questi passaggi:

1. Dichiarare variabili. Sostituisci questi valori con i tuoi valori.
2. Ottieni un elenco dei database nel pool elastico.
3. Per ogni database nel pool elastico, ottenere lo spazio totale allocato in MB e lo spazio allocato ma inutilizzato in MB.
4. Visualizzare i database in ordine decrescente dello spazio allocato inutilizzato.

$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 the total allocated space in MB and the allocated but unused space 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 unused allocated space
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort -Property DatabaseDataSpaceAllocatedUnusedInMB -Descending | Format-Table

Lo screenshot seguente mostra un esempio di output dello script:

Dimensione massima dei dati nel pool elastico

Modificare la query T-SQL seguente per restituire l'ultima dimensione massima registrata dei dati del pool elastico. 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

Ridurre i file di dati

A causa di un potenziale impatto sulle prestazioni del database, database SQL di Azure non compatta automaticamente i file di dati. I clienti possono tuttavia compattare i file di dati autonomamente quando preferiscono. La compattazione non deve essere un'operazione programmata regolarmente, ma piuttosto un evento monouso in risposta a una riduzione significativa del consumo di spazio usato dai file di dati.

In database SQL di Azure, per compattare i file è possibile usare uno dei comandi DBCC SHRINKDATABASE o DBCC SHRINKFILE:

• DBCC SHRINKDATABASE compatta tutti i file di dati e di resoconto in un database usando un singolo comando. Il comando compatta un file di dati alla volta e può richiedere molto tempo per i database di dimensioni maggiori. Compatta anche il file di resoconto, che in genere non è necessario perché database SQL di Azure compatta automaticamente i file di log in base alle esigenze.
• Il comando DBCC SHRINKFILE supporta scenari più avanzati:
  • Può scegliere come destinazione singoli file in base alle esigenze, invece di compattare tutti i file nel database.
  • Ogni comando DBCC SHRINKFILE può essere eseguito in parallelo con altri comandi DBCC SHRINKFILE per compattare più file contemporaneamente e ridurre il tempo totale di compattazione, a scapito di un utilizzo più elevato delle risorse e una maggiore probabilità di bloccare le query utente, se vengono eseguite durante la compattazione.
    • La compattazione di più file di dati contemporaneamente consente di completare l'operazione di compattazione più velocemente. Se si usa la compattazione del file di dati simultaneo, è possibile osservare il blocco temporaneo di una richiesta di compattazione da un'altra.
  • Se la parte finale del file non contiene dati, può ridurre le dimensioni del file allocate più velocemente specificando l'argomento TRUNCATEONLY. TRUNCATEONLY non richiede lo spostamento dei dati all'interno del file.
• Per ulteriori 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 database master.

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

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

In database SQL di Azure un database può avere uno o più file di dati, creati automaticamente man mano che aumentano i dati. Per determinare il layout del file del database, incluse le dimensioni usate e allocate di ogni file, eseguire una query sulla vista del catalogo di sys.database_files 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 su un solo file tramite il comando DBCC SHRINKFILE, 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 della compattazione dei file di database. Per maggiori informazioni, vedere manutenzione dell'indice dopo la compattazione.

Compattare il file di registro delle transazioni

A differenza dei file di dati, Azure SQL Database compatta automaticamente il file di registro delle transazioni per evitare un utilizzo eccessivo di spazio che può causare errori di spazio insufficiente. Nella maggior parte dei casi, non è necessario compattare il file di log delle transazioni.

Nei livelli di servizio Premium e Business Critical, se il log delle transazioni diventa di grandi dimensioni, può contribuire in modo significativo al consumo di 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. Questa operazione rilascia l'archiviazione locale 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);

Riduzione automatica

In alternativa alla compattazione manuale dei file di dati, è possibile abilitare la compattazione automatica per un database. Tuttavia, può essere meno efficace nel recuperare spazio su disco rispetto a DBCC SHRINKDATABASE e DBCC SHRINKFILE.

Per impostazione predefinita, e come consigliato per la maggior parte dei database, auto_shrink è disabilitato. Se si rende necessario abilitare la compattazione automatica, è consigliabile disabilitarla dopo aver raggiunto gli obiettivi di gestione dello spazio, invece di mantenerla abilitata in modo permanente. Per altre informazioni, vedere Considerazioni per AUTO_SHRINK.

Ad esempio, la compattazione automatica può essere utile se un pool elastico contiene molti database che riscontrano una crescita significativa e una riduzione dello spazio usato, causando l'avvicinamento del limite massimo di dimensioni del pool. Questo scenario non è comune.

Per abilitare auto_shrink, eseguire il comando seguente una volta connessi al database (non nel database master).

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

Per ulteriori informazioni su questo comando, vedere opzioni DATABASE SET.

Manutenzione dell'indice dopo la compattazione

Al termine di un'operazione di compattazione rispetto ai file di dati, gli indici potrebbero essere frammentati. La frammentazione riduce la velocità effettiva di I/O di lettura per determinati carichi di lavoro, ad esempio le query che usano analisi di grandi dimensioni. Se si verifica una riduzione del livello delle prestazioni dopo il completamento dell'operazione di compattazione, prendere in considerazione la possibilità di eseguire la manutenzione degli indici ricostruendoli. Tenere presente che le ricostruzioni dell'indice richiedono spazio libero nel database e quindi possono causare l'aumento dello spazio allocato, contrastando l'effetto di una riduzione.

Per altre informazioni sulla manutenzione degli indici, vedere Ottimizzare la manutenzione degli indici per migliorare le prestazioni delle query e ridurre il consumo di risorse.

Compattare database di grandi dimensioni

Quando lo spazio allocato del database è in centinaia di gigabyte o superiore, la compattazione potrebbe richiedere un tempo significativo per il completamento, spesso misurato in ore o giorni per i database da più terabyte. Esistono tuttavia ottimizzazioni dei processi e procedure consigliate per rendere questa operazione più efficiente e con un impatto minore sui carichi di lavoro delle applicazioni.

Acquisire il riferimento di utilizzo dello spazio

Prima di iniziare la compattazione, acquisire lo spazio corrente utilizzato 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';

Al termine della compattazione, è possibile eseguire di nuovo questa query e confrontare il risultato con la previsione iniziale.

Truncare i file di dati

È consigliabile eseguire prima la compattazione per ogni file di dati con il parametro TRUNCATEONLY. In questo modo, se uno spazio allocato ma non usato si trova alla fine del file, viene rimosso rapidamente senza spostare 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 sull'utilizzo dello spazio per visualizzare la riduzione dello spazio allocato, se presente. È anche possibile visualizzare lo spazio allocato per il database nel portale di Azure.

Valutare la densità della pagina dell'indice

Se il troncamento dei file di dati non ha comportato una riduzione sufficiente dello spazio allocato, è necessario compattare i file di dati. Tuttavia, come passaggio facoltativo ma consigliato, è necessario prima determinare la densità media delle pagine per gli indici all'interno del database. Per la stessa quantità di dati, le operazioni di compattazione si completano più velocemente se la densità di pagina è elevata, perché deve spostare meno pagine. Se la densità di pagina è bassa per alcuni indici, si consiglia di eseguire operazioni di manutenzione su questi indici per aumentare la densità di pagina prima di compattare i file di dati. Una densità di pagina più elevata consente 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 pagina viene segnalata nella colonna 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;

Se sono presenti indici con un numero elevato di pagine con densità di pagina inferiore al 60-70%, valutare la possibilità di ricompilare o riorganizzare questi indici prima di compattare i file di dati.

Per i database di dimensioni maggiori, la query per determinare la densità delle pagine potrebbe richiedere molto tempo. Anche la ricompilazione o la riorganizzazione di indici di grandi dimensioni richiede tempi e utilizzi sostanziali delle risorse. Tuttavia, la manutenzione dell'indice prima della compattazione può ridurre la durata della compattazione e consentire maggiori risparmi di spazio.

Se sono presenti più indici con densità di pagina bassa, è possibile ricompilarli in parallelo in più sessioni di database per velocizzare il processo. Tuttavia, assicurarsi di non avvicinarsi ai limiti delle risorse del database eseguendo questa operazione. Lasciare un sufficiente margine di risorse per i carichi di lavoro dell'applicazione che potrebbero essere in esecuzione. Monitorare l'utilizzo delle risorse (CPU, I/O dati, I/O log) nel portale di Azure o usando la visualizzazione sys.dm_db_resource_stats. Avviare ricompilazioni parallele aggiuntive solo se l'utilizzo delle risorse in ognuna di queste dimensioni rimane sostanzialmente inferiore a 100%. Se l'utilizzo di CPU, l'I/O dei dati o l'I/O dei log è pari al 100%, è possibile aumentare le prestazioni del database per avere più core CPU e aumentare la velocità effettiva di I/O.

Comando di ricompilazione dell'indice di esempio

Di seguito è riportato un comando di esempio per ricompilare un indice e aumentarne la densità di pagina usando l'istruzione 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);

Questo comando avvia una ricompilazione dell'indice online e ripristinabile. Questa operazione 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 rispetto a una ricompilazione offline, che blocca l'accesso alla tabella. Se nessun altro carico di lavoro deve accedere alla tabella durante la ricompilazione, impostare le opzioni ONLINE e RESUMABLE su OFF e rimuovere la clausola WAIT_AT_LOW_PRIORITY.

Per maggiori informazioni sulla manutenzione degli indici, vedere Ottimizzare la manutenzione degli indici per migliorare le prestazioni delle query e ridurre il consumo di risorse.

Compattare i dati LOB prima della riduzione

La compattazione può richiedere più tempo se il database contiene:

• Tipi di dati LOB, ad esempio varchar(max), nvarchar(max), varbinary(max), xml o tipi di dati simili archiviati nell'unità LOB_DATA di allocazione.
• Righe di grandi dimensioni archiviate in un'unità ROW_OVERFLOW_DATA di allocazione.
• Indici di tipo columnstore.

Per velocizzare l'esecuzione della riduzione e rilasciare più spazio, prima completare una riorganizzazione dell'indice con compattazione LOB. La compattazione LOB prima della riduzione è raccomandata per tutti gli indici che contengono colonne LOB o righe di grandi dimensioni. Per esempio:

ALTER INDEX [index_name] ON [schema_name].[table_name] 
REORGANIZE WITH (LOB_COMPACTION = ON);

La riorganizzazione o la ricostruzione degli indici columnstore prima della riduzione possono allo stesso modo aumentare la velocità e l'efficacia della riduzione.

Compattare più file di dati

Come indicato in precedenza, il restringimento con lo spostamento dei dati è un processo a lungo termine. Se nel database sono presenti più file di dati, è possibile velocizzare il processo compattando più file di dati in parallelo. Aprire più sessioni di database e usare DBCC SHRINKFILE in ogni sessione con un valore diverso file_id . Analogamente alla ricostruzione degli indici in precedenza, assicurarsi di avere a disposizione sufficienti risorse disponibili (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, spostando le pagine all'interno del file nel tentativo di ridurre le dimensioni allocate a 52.000 MB:

DBCC SHRINKFILE (4, 52000);

Per 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 alla compattazione, può iniziare a usare lo spazio di archiviazione liberato dalla compattazione prima del completamento della stessa e troncare il file. In questo caso, l'operazione di riduzione non è in grado di ridurre lo spazio allocato al target specificato.

Per evitare questo problema, compattare ogni file in passaggi più piccoli. Nel comando , impostare un obiettivo leggermente inferiore rispetto allo spazio allocato attualmente per il file, come illustrato nei 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, è possibile impostare prima la destinazione su 170.000 MB:

DBCC SHRINKFILE (4, 170000);

Questo comando tronca il file e riduce le dimensioni allocate a 170.000 MB. È quindi possibile ripetere questo comando, impostando la destinazione prima 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 compattazione per tutte le sessioni di compattazione in esecuzione simultanea, è 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');

Al termine della riduzione per tutti i file di dati, eseguire nuovamente la query di utilizzo dello spazio (o controllare nel portale di Azure) per determinare la riduzione risultante delle dimensioni di archiviazione allocate. Se esiste ancora una grande differenza tra lo spazio usato e lo spazio allocato, ricostruisci gli indici. Una ricompilazione dell'indice potrebbe ulteriormente aumentare temporaneamente lo spazio allocato. 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

In alcuni casi, un comando di compattazione può avere esito negativo con diversi errori, ad esempio timeout e deadlock. In generale, questi errori sono temporanei e non si verificano di nuovo se si ripete lo stesso comando. Se la riduzione ha esito negativo con un errore, i progressi compiuti finora vengono mantenuti. Eseguire di nuovo lo stesso comando di compattazione per continuare a compattare il file.

Lo script di esempio seguente illustra come eseguire il "shrink" in un ciclo di ripetizione dei tentativi. Il ciclo ritenta automaticamente l'operazione fino a un numero configurabile di volte in cui si verifica un errore di timeout o un errore di deadlock. Questo approccio di ripetizione è applicabile a molti altri errori che potrebbero verificarsi durante la riduzione.

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 riduzione può riscontrare errori a causa di determinati problemi noti.

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

• Numero errore: 49503, messaggio di errore: %.*ls: Impossibile spostare la pagina %d:%d perché si tratta di una pagina dell'archivio versioni persistente fuori riga. Motivo di blocco della pagina: %ls. Timestamp di blocco della 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). Non è possibile spostare le pagine contenenti versioni di riga.

Per attenuare il problema, è necessario attendere il completamento delle transazioni a esecuzione prolungata. In alternativa, è possibile identificare e terminare 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 compattare:

-- 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 KILL e specificando il valore session_id associato dal risultato della query:

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

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

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 colonna persistent_version_store_size_gb sono notevolmente ridotte rispetto alle dimensioni originali, la ripetizione della compattazione dovrebbe avere esito positivo.

• Numero errore: 5223, messaggio di errore: %.*ls: Impossibile deallocare la pagina vuota %d:%d.

Questo errore può verificarsi se sono in corso operazioni di manutenzione dell'indice, ad esempio ALTER INDEX. Riprova il comando 'shrink' dopo il completamento di queste operazioni.

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

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 segnaposto <file_id> e <page_id> con i valori effettivi del messaggio di errore ricevuto. Ad esempio, se il messaggio è Non è stato possibile deallocare la pagina vuota 1:62669, quindi <file_id> è 1 e <page_id> è 62669.

Ricostruire l'indice identificato dalla query e riprovare il comando ridurre.

• Numero errore: 5201, messaggio di errore: DBCC SHRINKDATABASE: il file con ID %d del database con ID %d è stato ignorato perché lo spazio disponibile non è sufficiente per il recupero.

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

Contenuto correlato

Per informazioni sulle dimensioni massime dei database, vedere:

• Limiti del modello di acquisto basato su vCore per il database SQL di Azure per un database singolo
• Limiti di risorse per i database singoli usando il modello di acquisto basato su DTU
• Limiti del modello di acquisto basato su vCore per il database SQL di Azure per i pool elastici
• Limiti di risorse per i pool elastici usando il modello di acquisto basato su DTU