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

Si applica a:Database SQL di Azure

• Database SQL di Azure
• Istanza gestita di database SQL di Azure

Questo articolo descrive diversi tipi di spazio di archiviazione per i database nel database SQL di Azure. Potresti dover gestire esplicitamente lo spazio file allocato occasionalmente. Questo articolo include i passaggi per farlo.

Panoramica

Alcuni schemi di carico di lavoro possono far sì che lo spazio allocato ai file dati diventi più grande dello spazio utilizzato. Questa condizione si verifica quando lo spazio utilizzato aumenta a causa della crescita dei dati, ma successivamente si cancella o comprime i dati. Lo spazio allocato ma inutilizzato non viene automaticamente recuperato perché la recuperazione richiede molte risorse e rallenterà la crescita futura dei file.

Potresti dover ridurre i file dati e recuperare lo spazio inutilizzato nei seguenti scenari:

• Consentire la crescita dei dati per i database in un pool elastico quando uno spazio allocato grande per alcuni database nel pool fa sì che il pool si avvicini alla sua dimensione massima.
• Per consentire una riduzione della dimensione massima di un singolo database o di un pool elastico.
• Cambiare il database o un elastic pool in un livello con un limite massimo di dimensione inferiore.
• Per ridurre i costi di storage utilizzando il livello di servizio Hyperscale.

Attenzione

Non considerare le operazioni di riduzione come una normale manutenzione. 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 dei file

Le API di Azure Resource Manager (ARM), inclusi i get-metric di PowerShell, restituiscono lo spazio utilizzato e allocato per database ed elastic pool.

Le seguenti viste di sistema restituiscono anche la dimensione dello spazio utilizzato e allocato per database e pool elastici:

• sys.resource_stats
• sys.elastic_pool_resource_stats
• sys.dm_elastic_pool_resource_stats

Informazioni sui tipi di spazio di archiviazione per un database

Comprendere le quantità di spazio di archiviazione seguenti è importante per gestire lo spazio file di un database.

Quantità di database	Definizione	Commenti
Spazio dati usato	La quantità di spazio utilizzata per memorizzare i dati.	In genere, lo spazio usato aumenta con gli inserimenti e diminuisce con le eliminazioni. 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 in qualsiasi frammentazione. Ad esempio, se si elimina una riga da ogni pagina di dati, non si riduce necessariamente lo spazio usato.
Spazio dati allocato	La quantità di spazio di archiviazione occupata dai file dati.	La quantità di spazio allocata cresce automaticamente, ma non diminuisce mai automaticamente dopo le cancellazioni. Questo comportamento garantisce che i futuri inserimenti siano più rapidi, poiché lo spazio non deve essere riallocato.
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	La quantità massima di spazio che può essere utilizzata per memorizzare dati.	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.

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.

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

Comprendere le seguenti quantità di spazio di archiviazione è importante per gestire lo spazio file di un pool elastico.

Quantità del pool elastico	Definizione	Commenti
Spazio dati usato	Somma dello spazio dati usato da tutti i database nel pool elastico.
Spazio dati allocato	La somma dello spazio di archiviazione occupato dai file dati in tutti i database del 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 utilizzato da un pool elastico per tutti i relativi database.	Lo spazio destinato alla piscina elastica non dovrebbe superare la dimensione massima della piscina elastica. Se si verifica questa condizione, allora i dati allocati ma non utilizzati possono essere recuperati riducendo i file dati.

Il messaggio di errore "Il pool elastico ha raggiunto il suo limite di memoria" indica che gli oggetti del database occupano abbastanza spazio per raggiungere il limite massimo di spazio di archiviazione del pool elastico. Considera di aumentare il limite di archiviazione o liberare spazio dati come descritto in Reclamare spazio allocato inutilizzato.

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

Usa le seguenti query per determinare le quantità di spazio di archiviazione per un pool elastico.

Spazio dati del pool elastico usato

Usa la seguente query di esempio per restituire la quantità di spazio dati elastico del pool utilizzato. Modifica il parametro del nome elastico della piscina per farlo corrispondere al nome della tua piscina.

-- Connect to master
SELECT TOP (1) avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS elastic_pool_space_used_mb,
               avg_allocated_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS elastic_pool_space_allocated_mb,
               elastic_pool_storage_limit_mb AS elastic_pool_maximum_size_mb
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

Recuperare lo spazio allocato non usato

Importante

Le operazioni di riduzione consumano risorse e possono influire sulle prestazioni del database durante l'esecuzione. Quando possibile, usa shrink durante i periodi di basso utilizzo.

Ridurre i file di dati

Poiché la riduzione dei file di dati può influire sulle prestazioni del database, database SQL di Azure non riduce automaticamente i file di dati. Se necessario, puoi ridurre i file dati nel momento che preferisci. Non eseguire lo shrink come operazione pianificata regolarmente. Invece, considera di usarlo solo dopo una significativa riduzione dello spazio utilizzato.

Suggerimento

Non sprecare risorse di calcolo e tempo riducendo i file di dati se il carico di lavoro normale delle applicazioni fa crescere i file fino alla dimensione allocata di nuovo.

Per ridurre i file, usa uno dei seguenti comandi T-SQL: DBCC SHRINKDATABASE o DBCC SHRINKFILE

• DBCC SHRINKDATABASE riduce tutti i dati e i file di log in un database con un unico 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 ridurre il tempo totale necessario per la riduzione, a scapito di un maggiore utilizzo delle risorse e di una maggiore probabilità che le query dell'utente e i comandi DBCC SHRINKFILE concorrenti vengano temporaneamente bloccati.
  • Se la coda del file non contiene dati, puoi ridurre più rapidamente la dimensione del file assegnata specificando l'argomento TRUNCATEONLY . TRUNCATEONLY Non richiede il trasferimento dei dati all'interno del file, ma non riduce nemmeno la dimensione allocata.
• Per ulteriori informazioni su questi comandi di compattazione, vedere DBCC SHRINKDATABASE e DBCC SHRINKFILE.

Esegui i seguenti esempi mentre sei connesso al database utente di destinazione, non al master database.

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

DBCC SHRINKDATABASE (N'database_name');

Un database potrebbe avere uno o più file dati, creati automaticamente man mano che i dati crescono. Per determinare la disposizione del file del tuo database, inclusa la dimensione utilizzata e allocata di ciascun file, consulta la sys.database_files vista catalogo utilizzando il seguente script di esempio:

-- Review file properties, including the file_id and name values to use 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');

Per ridurre un singolo file, usa 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);

Compattare il file di registro delle transazioni

A differenza dei file di dati, database SQL di Azure 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 registro delle transazioni diventa grande, potrebbe contribuire significativamente al consumo di memoria locale verso il limite massimo di spazio locale . Se il consumo di memoria locale è vicino al limite, potresti scegliere di ridurre il registro delle transazioni usando il DBCC SHRINKFILE comando mostrato nell'esempio seguente. Questa operazione rilascia l'archiviazione locale non appena il comando viene completato, senza attendere l'operazione di compattazione automatica periodica.

Esegui il seguente esempio mentre sei connesso al database utente di destinazione, non al master database.

-- 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 diventa necessario abilitare l'auto-riduczione, si consiglia di disabilitarlo una volta raggiunti gli obiettivi di gestione dello spazio, invece di mantenerlo attivato permanentemente. Per altre informazioni, vedere Considerazioni per AUTO_SHRINK.

Ad esempio, l'auto-shrink può essere utile se un pool elastico contiene molti database che sperimentano continuamente una crescita e una riduzione significativa dello spazio utilizzato, portando il pool ad avvicinarsi al limite massimo di dimensioni. Questo scenario non è comune.

L'opzione di riduzione automatica del database non ha effetto nei database Hyperscale.

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

Dopo il completamento di un'operazione di riduzione, gli indici potrebbero frammentarsi. Per la maggior parte dei carichi di lavoro sulle piattaforme moderne, la frammentazione degli indici difficilmente influirà sulle prestazioni. Per carichi di lavoro che utilizzano grandi scansioni di indici, la frammentazione potrebbe ridurre la velocità di I/O di lettura. Se si verifica un degrado delle prestazioni dopo il completamento dell'operazione di ridurre, si considera la manutenzione degli indici per ricostruire o riorganizzare gli indici. Le ricostruzioni di indici richiedono spazio libero nel database, quindi potrebbero far aumentare lo spazio allogato, contrastando l'effetto di un restringimento.

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 in un database è di centinaia di gigabyte o più, il riducimento può richiedere molto tempo. Le operazioni di riduzione possono durare ore, giorni o settimane per database multi-terabyte. Questa sezione descrive ottimizzazioni dei processi e best practice che rendono questo processo più efficiente e meno impattante sui carichi di lavoro applicativi.

Suggerimento

ShrinkDriver è uno script PowerShell che automatizza e semplifica il processo di riduzione per grandi database, trasformandolo in un'unica operazione osservabile e riprendibile. Lo script riduce più file in parallelo, ritenta quando interrotto e genera report di stato dettagliati durante l'esecuzione.

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.

Tronca i file di dati per un vantaggio rapido ma limitato

Se vuoi ridurre rapidamente lo spazio allocato, valuta l'esecuzione di DBCC SHRINKFILE con il parametro TRUNCATEONLY. Se c'è spazio allocato ma inutilizzato alla fine del file, l'operazione rimuove rapidamente quello spazio senza alcun movimento di dati.

Tuttavia, non usarla TRUNCATEONLY se il tuo obiettivo è massimizzare la riduzione dello spazio assegnato. Per raggiungere questo obiettivo, è necessario eseguire il processo di riduzione completa descritto più avanti in questa sezione. Poiché quel processo tronca i file alla fine, una riduzione separata con TRUNCATEONLY non comporta alcun vantaggio.

Il seguente esempio di comando tronca l'ID del file 4:

DBCC SHRINKFILE (4, TRUNCATEONLY);

Dopo aver eseguito questo comando per ogni file dati, riesegui la query di utilizzo dello spazio per vedere la riduzione dello spazio allocato, se presente. Puoi anche visualizzare lo spazio assegnato per il database nel portale Azure.

Valutare la densità della pagina dell'indice

Come passaggio opzionale ma consigliato, determina la densità media di pagine per gli indici nel database. Per la stessa quantità di dati, le operazioni di riduzione si completano più velocemente se la densità di pagina è alta, perché l'operazione sposta meno pagine all'interno di ogni file. 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 pagine più alta permette al shrink di ottenere una riduzione più profonda dello spazio di archiviazione allolocato.

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 ci sono indici con un alto numero di pagine (come riportato nella page_count colonna) con densità inferiore a 60-70%, considera di ricostruire o riorganizzare questi indici prima di ridurre i file 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, assicurati di non avvicinarti ai limiti delle risorse del database facendo così. Lasciare un sufficiente margine di risorse per i carichi di lavoro dell'applicazione che potrebbero essere in esecuzione. Monitorare il consumo di risorse (CPU, Data IO, Log IO) nel portale Azure o utilizzando la vista sys.dm_db_resource_stats. Avviare ulteriori operazioni di indicizzazione solo se l'utilizzo delle risorse su ciascuna di queste dimensioni rimane sostanzialmente inferiore a 100%.

Esempio di comando di ricostruzione dell'indice

Il seguente comando di esempio utilizza l'istruzione ALTER INDEX per ricostruire un indice e aumentare la densità delle pagine:

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.

Riorganizza gli indici prima di ridurlo

Riorganizzare gli indici prima di ridurre può rendere l'operazione di riduzione significativamente più veloce in due scenari.

1. Se il database soddisfa tutti i seguenti criteri:

   • Dispone di un gran numero di file dati (più di 10).
   • Ha un gran numero di tabelle nel database (diverse centinaia o più), che utilizzano complessivamente una grande quantità di spazio (centinaia di gigabyte o più).
   • Una grande quantità di dati viene cancellata da alcune tabelle.

   Per questi database, la riorganizzazione degli indici sulle tabelle da cui sono stati eliminati dati riduce la durata di una fase lunga del processo di riduzione.

2. Se il database contiene:

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

   Per eseguire l'operazione di riduzione più velocemente e liberare più spazio in questo caso, assicurati di includere la clausola LOB_COMPACTION quando riorganizzi gli indici. La compattazione LOB prima della riduzione è raccomandata per tutti gli indici che contengono colonne LOB o righe di grandi dimensioni.

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

Il seguente esempio mostra un comando per riorganizzare un indice ed eseguire la compattazione LOB:

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

Ridurre in parallelo più file di dati

Un'operazione di riduzione che richiede il trasferimento dei dati è un processo di lunga durata. 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 seguente comando di esempio riduce l'ID file 4, cercando di ridurne la dimensione allocata a 52.000 MB:

DBCC SHRINKFILE (4, 52000);

Per ridurre lo spazio allocato al file al minimo possibile, esegui l'istruzione senza specificare la dimensione target:

DBCC SHRINKFILE (4);

Se inizi troppe operazioni di riduzione parallela, potresti osservare un alto utilizzo delle risorse e contesa di blocchi tra le operazioni di riducimento. Per la maggior parte degli scenari, il numero ottimale di operazioni di riduzione parallela è tra quattro e otto.

Riduzione in passi incrementali

Se un'operazione di riduzione si interrompe inaspettatamente (ad esempio, a causa di manutenzione pianificata o non programmata), un carico di lavoro potrebbe iniziare a utilizzare lo spazio liberato dal riducimento prima che il riducimento tronchi il file, perdendo parte del progressivo riducimento fatto finora. Poiché la riduzione richiede spesso molto tempo, le probabilità di un'interruzione sono maggiori.

Per evitare questo problema, riduci ogni file in passaggi più piccoli e incrementali. Nel DBCC SHRINKFILE comando, imposta il target che sia più piccolo dello spazio attualmente allocato per il file, ma più grande dello spazio utilizzato restituito dalla query di utilizzo dello spazio di base .

Ad esempio, se lo spazio assegnato per il file ID 4 è di 200.000 MB e vuoi ridurlo a 100.000 MB, puoi prima impostare l'obiettivo a 180.000 MB:

DBCC SHRINKFILE (4, 180000);

Dopo che questo comando riduce la dimensione allocata a 180.000 MB, puoi eseguire nuovamente il shrink, impostando prima il target a 160.000 MB, poi a 140.000 MB, e continuare a ridurre il target finché il file non raggiunge la dimensione desiderata.

Ridurre i file a incrementi potrebbe richiedere più tempo, ma riduce il rischio di riducere ripetutamente l'intero file a causa di un'interruzione imprevista.

Come punto di partenza, usa un incremento nell'intervallo di 10-20 gigabyte. Puoi regolare l'incremento secondo necessità per il tuo scenario. Incrementi più grandi potrebbero permetterti di completare il file shrink più velocemente, incrementi più piccoli riducono il rischio di perdere progressi se il shrink viene interrotto.

Monitoraggio delle operazioni di riduzione

Per monitorare l'avanzamento dell'operazione di shrink per tutte le sessioni di shrink eseguite contemporaneamente, utilizza la query seguente:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       writes,
       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 OUTER JOIN sys.databases AS d
         ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim', 'DbccFilesCompact', 'DbccLOBCompact', 'DBCC');

Nota

Il progresso della riduzione potrebbe essere non lineare e il valore nella percent_complete colonna potrebbe rimanere invariato per lunghi periodi, anche se la riduzione è ancora in corso. Un aumento dei valori cpu_time, reads o writes per lo stesso session_id tra due esecuzioni della query significa che shrink continua a progredire.

Quando la riduzione termina con successo per tutti i file dati, rieseguire la query di utilizzo dello spazio (o controllare nel portale Azure) per vedere la conseguente riduzione della dimensione di memoria allocata. Se c'è ancora una grande differenza tra spazio usato e spazio allogato, ricostruisci o riorganizza gli indici. Una ricostruzione dell'indice potrebbe aumentare temporaneamente lo spazio allocato. Tuttavia, ridurre nuovamente i file dati dopo la ricostruzione degli indici spesso comporta una riduzione più profonda dello spazio allocato.

Errori temporanei durante la compattazione

Occasionalmente, un comando di riduzione può fallire con errori come timeout e blocchi. Questi errori sono spesso transitori e non si ripetono se ripeti lo stesso comando. Se la riduzione fallisce con un errore, mantiene i progressi fatti finora. Eseguire di nuovo lo stesso comando di compattazione per continuare a compattare il file.

Lo script PowerShell ShrinkDriver ritenta automaticamente l'operazione di riduzione quando si verifica un errore temporaneo. Usa questo script per ridurre i database di grandi dimensioni.

Il seguente esempio di script T-SQL mostra come eseguire la riduzione di un singolo file in un ciclo di tentativi. Il ciclo ritenta automaticamente l'operazione fino a un numero configurabile di volte quando si verifica un errore di timeout o un deadlock. Questo approccio di ripetizione si applica a molti altri errori che potrebbero verificarsi durante la riduzione.

DECLARE @RetryCount AS INT = 3; -- adjust to configure desired number of retries
DECLARE @Delay AS 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 a timeout e deadlock, il riducimento può incontrare errori dovuti a determinati problemi noti.

Rivedere gli errori e i passaggi di mitigazione nelle sezioni seguenti.

Numero di errore 49503

%.*ls: Page %d:%d could not be moved because it is an off-row persistent version store page. Page holdup reason: %ls. Page holdup timestamp: %I64d.

Questo errore si verifica quando transazioni attive di lunga durata generano versioni di righe nel persistente record di versioni (PVS). Shrink non può spostare le pagine contenenti le versioni di riga.

Per mitigare questo errore, attendere che le transazioni di lunga durata siano completate. In alternativa, identifica e termina le transazioni di lunga durata, ma questa azione può influire sulla tua applicazione se non gestisce correttamente i fallimenti delle transazioni.

Per maggiori informazioni sulla risoluzione dei problemi relativi ai ritardi nella pulizia di PVS che potrebbero influire sull'operazione di riduzione, vedere Monitorare e risolvere i problemi relativi al ripristino accelerato del database.

Errore numero 5223

%.*ls: Empty page %d:%d could not be deallocated.

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

Se questo errore persiste, potresti dover ricostruire 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, sostituisci i <file_id> segnaposto e <page_id> con i valori effettivi del messaggio di errore. Ad esempio, se il messaggio è: Empty page 1:62669 could not be deallocated, allora <file_id> è 1 e <page_id> è 62669.

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

Errore numero 5201

DBCC SHRINKDATABASE: File ID %d of database ID %d was skipped because the file does not have enough free space to reclaim.

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

Contenuto correlato

• Limiti delle risorse per i singoli database che utilizzano il modello di acquisto vCore
• Limiti di risorse per i database singoli usando il modello di acquisto DTU - Database SQL di Azure
• Limiti delle risorse per i pool elastici secondo il modello di acquisto vCore
• Limiti delle risorse per i pool elastici secondo il modello di acquisto DTU