Verwalten von Dateispeicherplatz für Datenbanken in Azure SQL-Datenbank

Gilt für::Azure SQL-Datenbank

In diesem Artikel werden verschiedene Arten von Speicherplatz für Datenbanken in der Azure SQL-Datenbank beschrieben. Obwohl ungewöhnlich, enthält dieser Artikel Schritte, die ausgeführt werden können, wenn der zugewiesene Dateispeicher explizit verwaltet werden muss.

Übersicht

Bei Azure SQL-Datenbank gibt es Workloadmuster, bei denen die Zuordnung von zugrunde liegenden Datendateien für Datenbanken größer als die Anzahl der verwendeten Datenseiten werden kann. Dieser Fall kann eintreten, wenn der belegte Platz zunimmt und Daten später gelöscht werden. Der Grund dafür ist, dass der zugeordnete Dateispeicherplatz nicht automatisch wieder freigegeben wird, wenn Daten gelöscht werden.

Die Überwachung der Dateispeicherplatzverwendung und die Verkleinerung von Datendateien können in folgenden Szenarien erforderlich sein:

• Ermöglichen von Datenwachstum in einem Pool für elastische Datenbanken, wenn der den Datenbanken zugeordnete Dateispeicherplatz die maximale Poolgröße erreicht
• Ermöglichen der Verringerung der maximalen Größe einer einzelnen Datenbank oder eines Pools für elastische Datenbanken
• Ermöglichen der Änderung einer einzelnen Datenbank oder eines Pools für elastische Datenbanken, um eine andere Dienstebene oder Leistungsstufe mit einer geringeren maximalen Größe zu verwenden

Überwachen der Dateispeicherplatzverwendung

Bei den meisten Speicherplatzmetriken, die in den folgenden APIs angezeigt werden, wird lediglich die Größe der verwendeten Datenseiten ermittelt:

• Azure Resource Manager-basierte Metrik-APIs einschließlich PowerShell get-metrics

Bei den folgenden APIs wird jedoch auch die Größe des Speicherplatzes ermittelt, der Datenbanken und Pools für elastische Datenbanken zugeordnet ist:

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

Grundlegendes zu den Arten von Speicherplatz für eine Datenbank

Es wichtig, dass Sie mit den folgenden Speicherplatzmengen vertraut sind, damit Sie den Dateispeicherplatz einer Datenbank verwalten können.

Das folgende Diagramm veranschaulicht die Beziehung zwischen den verschiedenen Arten von Speicherplatz für eine Datenbank.

Abfragen einer einzigen Datenbank nach Dateispeicherplatzinformationen

Verwenden Sie die folgende Abfrage auf sys.database_files, um die Menge des zugewiesenen und des ungenutzten Speicherplatzes in der Datenbankdatei zu ermitteln. Als Einheit für das Abfrageergebnis wird MB verwendet.

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

Grundlegendes zu den Arten von Speicherplatz für einen elastischen Pool

Es wichtig, dass Sie mit den folgenden Speicherplatzmengen vertraut sind, damit Sie den Dateispeicherplatz eines Pools für elastische Datenbanken verwalten können.

Speicherplatzinformationen eines elastischen Pools abfragen

Die folgenden Abfragen können verwendet werden, um die Speicherplatzmengen für einen elastischen Pool zu ermitteln.

Elastischer Datenpool genutzter Speicherplatz

Ändern Sie die folgende Abfrage, um den belegten Speicherplatz eines Pools für elastische Datenbanken zurückzugeben. Als Einheit für das Abfrageergebnis wird MB verwendet.

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

Zugeordneter Datenspeicherplatz im elastischen Pool und ungenutzter zugeordneter Speicherplatz

Ändern Sie die folgenden Beispiele, um eine Tabelle zurückzugeben, in der der gesamt zugewiesene Speicherplatz und nicht verwendeter Speicherplatz für jede Datenbank in einem elastischen Pool aufgeführt sind. Die Tabelle sortiert Datenbanken aus diesen Datenbanken mit der größten Menge nicht genutztem Speicherplatz bis zur geringsten Menge nicht verwendetem Speicherplatz. Als Einheit für das Abfrageergebnis wird MB verwendet.

Addiere die Abfrageergebnisse für jede Datenbank im Pool, um den insgesamt zugewiesenen Speicherplatz für den elastischen Pool zu bestimmen. Der zugewiesene Speicherplatz des elastischen Pools sollte die maximale Größe des elastischen Pools nicht überschreiten.

Für das PowerShell-Skript ist sql Server PowerShell-Modul erforderlich. Weitere Informationen finden Sie im SQL Server PowerShell-Modul.

Das folgende PowerShell-Skript führt die folgenden Schritte aus:

1. Deklarieren Sie Variablen. Ersetzen Sie diese Werte durch Ihre eigenen Werte.
2. Erhalten Sie eine Liste der Datenbanken im elastischen Pool.
3. Rufen Sie für jede Datenbank im elastischen Pool den gesamten zugewiesenen Speicherplatz in MB und den zugewiesenen, aber nicht verwendeten Speicherplatz in MB ab.
4. Anzeigen von Datenbanken in absteigender Reihenfolge des nicht zugewiesenen Speicherplatzes.

$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

Der folgende Screenshot zeigt ein Beispiel für die Ausgabe des Skripts:

Maximale Datengröße des elastischen Pools

Ändern Sie die folgende T-SQL-Abfrage, um die zuletzt aufgezeichnete maximale Größe der Daten des Pools für elastische Datenbanken zurückzugeben. Als Einheit für das Abfrageergebnis wird MB verwendet.

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

Freigeben von ungenutztem zugewiesenem Speicherplatz

Verkleinern von Dateien

Aufgrund einer potenziellen Auswirkung auf die Datenbankleistung verkleinert die Azure SQL-Datenbank die Datendateien nicht automatisch. Kunden können jedoch jederzeit Ihre Dateien per Self-Service verkleinern. Verkleinern sollte kein regelmäßig geplanter Vorgang sein, sondern ein einmaliges Ereignis als Reaktion auf eine erhebliche Reduzierung des genutzten Speicherplatzes in der Datendatei.

In Azure SQL Database können Sie zum Verkleinern von Dateien entweder die Befehle DBCC SHRINKDATABASE oder DBCC SHRINKFILE verwenden:

• DBCC SHRINKDATABASE verkleinert alle Daten- und Protokolldateien in einer Datenbank mit einem einzigen Befehl. Der Befehl verkleinert eine Datendatei nach der anderen, was bei größeren Datenbanken sehr lange dauern kann. Außerdem wird die Protokolldatei geschrumpft, was in der Regel nicht notwendig ist, da Azure SQL Database Protokolldateien bei Bedarf automatisch schrumpft.
• Der DBCC SHRINKFILE-Befehl unterstützt fortgeschrittenere Szenarien:
  • Er kann je nach Bedarf einzelne Dateien verkleinern, anstatt alle Dateien in der Datenbank zu verkleinern.
  • Jeder DBCC SHRINKFILE-Befehl kann parallel zu anderen DBCC SHRINKFILE-Befehlen ausgeführt werden, um mehrere Dateien gleichzeitig zu verkleinern und die Gesamtzeit der Verkleinerung zu verkürzen, allerdings auf Kosten eines höheren Ressourcenverbrauchs und einer höheren Wahrscheinlichkeit, dass Benutzerabfragen blockiert werden, wenn sie während der Verkleinerung ausgeführt werden.
    • Durch gleichzeitiges Verkleinern mehrerer Datendateien können Sie den Verkleinerungsvorgang schneller abschließen. Wenn Sie die gleichzeitige Datendateiverkleinerung verwenden, können Sie ggf. das vorübergehende Blockieren einer Verkleinerungsanforderung durch eine andere beobachten.
  • Wenn der Tail der Datei keine Daten enthält, kann die zugeordnete Dateigröße schneller reduziert werden, indem das TRUNCATEONLY Argument angegeben wird. TRUNCATEONLY erfordert keine Datenverschiebung innerhalb der Datei.
• Weitere Informationen zu diesen Shrink-Befehlen finden Sie unter DBCC SHRINKDATABASE und DBCC SHRINKFILE.

Die folgenden Beispiele müssen ausgeführt werden, während eine Verbindung mit der Zielbenutzerdatenbank und nicht mit der master-Datenbank besteht.

So verwenden Sie DBCC SHRINKDATABASE, um alle Daten- und Protokolldateien in einer bestimmten Datenbank zu verkleinern:

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

In Azure SQL Database kann eine Datenbank eine oder mehrere Datendateien haben, die automatisch erstellt werden, wenn die Daten wachsen. Um das Dateilayout Ihrer Datenbank zu ermitteln, einschließlich der verwendeten und zugewiesenen Größe jeder Datei, fragen Sie die Katalogansicht sys.database_files mit dem folgenden Beispielskript ab:

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

Mit dem Befehl DBCC SHRINKFILE können Sie z. B. einen Schrumpfvorgang für nur eine Datei durchführen:

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

Beachten Sie die potenziellen negativen Auswirkungen auf die Leistung der Verkleinerung von Datenbankdateien. Weitere Informationen finden Sie unter Indexwartung nach Verkleinerung.

Verkleinern der Transaktionsprotokolldatei

Im Gegensatz zu Datendateien verkleinert Azure SQL-Datenbank die Transaktionsprotokolldatei automatisch, um eine übermäßige Speicherplatznutzung zu vermeiden, die zu Fehlern führen kann, weil nicht genügend Speicherplatz verfügbar ist. In den meisten Fällen müssen Sie die Transaktionsprotokolldatei nicht verkleinern.

Wenn das Transaktionsprotokoll in den Premium- und Business-Critical-Dienstebenen groß wird, kann es erheblich zum Verbrauch des lokalen Speichers und damit zur Annäherung an den maximalen lokalen Speicher-Grenzwert beitragen. Wenn sich der lokale Speicherverbrauch dem Grenzwert nähert, können Kunden das Transaktionsprotokoll mithilfe des Befehls DBCC SHRINKFILE verkleinern, wie im folgenden Beispiel gezeigt. Dadurch wird lokaler Speicher freigegeben, nachdem der Befehl abgeschlossen wurde, ohne auf den regelmäßigen automatischen Verkleinerungsvorgang zu warten.

Die folgenden Beispiele müssen ausgeführt werden, während eine Verbindung mit der Zielbenutzerdatenbank und nicht mit der master-Datenbank besteht.

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

Automatisches Verkleinern

Als Alternative zum manuellen Schrumpfen von Datendateien kann das automatische Schrumpfen für eine Datenbank aktiviert werden. Verglichen mit DBCC SHRINKDATABASE und DBCC SHRINKFILE ist das automatische Verkleinern allerdings beim Freigeben von Dateispeicherplatz unter Umständen weniger effizient.

Das automatische Verkleinern ist standardmäßig deaktiviert. Dies ist für die meisten Datenbanken die empfohlene Einstellung. Wenn das automatische Verkleinern aktiviert werden muss, wird empfohlen, das automatische Verkleinern wieder zu deaktivieren, sobald die Ziele der Speicherplatzverwaltung erreicht wurden, anstatt das automatische Verkleinern dauerhaft aktiviert zu lassen. Weitere Informationen finden Sie im Abschnitt unter Überlegungen zu AUTO_SHRINK.

So kann beispielsweise eine automatische Verkleinerung hilfreich sein, wenn ein elastischer Pool viele Datenbanken enthält, die erhebliches Wachstum und eine Verringerung des verwendeten Platzes aufweisen, was dazu führt, dass der Pool seine maximale Größenbeschränkung erreicht. Dieses Szenario ist nicht üblich.

Um die automatische Verkleinerung zu aktivieren, führen Sie den folgenden Befehl aus, während Sie mit Ihrer Datenbank verbunden sind (nicht mit der master-Datenbank).

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

Weitere Informationen zu diesem Befehl finden Sie in den Optionen für DATABASE SET.

Indexpflege nach der Verkleinerung

Nachdem ein Verkleinerungsvorgang für Datendateien abgeschlossen wurde, können Indizes fragmentiert werden. Die Fragmentierung reduziert den Lese-/E-Durchsatz für bestimmte Workloads, z. B. Abfragen mit großen Scans. Wenn nach dem Abschluss des Verkleinerungsvorgang eine Leistungsbeeinträchtigung auftritt, sollten Sie eine Indexwartung in Betracht ziehen, um die Indizes neu zu erstellen. Denken Sie daran, dass Indexneuerstellungen freien Speicherplatz in der Datenbank erfordern und daher dazu führen können, dass der zugewiesene Speicherplatz erhöht wird, was der Auswirkung einer Schrumpfung entgegenwirkt.

Weitere Informationen zur Indexpflege finden Sie unter Optimierung der Indexpflege zur Verbesserung der Abfrageleistung und Reduzierung des Ressourcenverbrauchs.

Große Datenbanken schrumpfen

Wenn der Datenbank zugewiesene Speicherplatz Hunderte von Gigabyte oder mehr beträgt, kann die Verkleinerung eine beträchtliche Zeit in Anspruch nehmen, die oft in Stunden oder bei Datenbanken mit mehreren Terabyte in Tagen gemessen wird. Es gibt Prozessoptimierungen und Best Practices, mit denen Sie diesen Prozess effizienter gestalten und die Anwendungs-Workloads weniger beeinträchtigen können.

Erfassen der Grundlinie der Raumnutzung

Bevor Sie mit der Verkleinerung beginnen, erfassen Sie den aktuell belegten und zugewiesenen Speicherplatz in jeder Datenbankdatei, indem Sie die folgende Abfrage zur Speicherplatznutzung ausführen:

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

Nach Abschluss der Schrumpfung können Sie diese Abfrage erneut ausführen und das Ergebnis mit der ursprünglichen Basislinie vergleichen.

Abschneiden von Datendateien

Es wird empfohlen, zunächst für jede Datendatei eine Schrumpfung mit dem Parameter TRUNCATEONLY durchzuführen. Auf diese Weise wird zugeteilter, aber ungenutzter Speicherplatz am Ende der Datei schnell und ohne Datenverschiebung entfernt. Der folgende Beispielbefehl schneidet die Datendatei mit der file_id 4 ab:

DBCC SHRINKFILE (4, TRUNCATEONLY);

Sobald dieser Befehl für jede Datendatei ausgeführt wurde, können Sie die Abfrage zur Speicherplatznutzung erneut ausführen, um die Verringerung des zugewiesenen Speicherplatzes zu sehen, falls es eine gibt. Sie können den zugewiesenen Speicherplatz für die Datenbank auch im Azure-Portal einsehen.

Bewertung der Indexseitendichte

Wenn das Abschneiden von Datendateien nicht zu einer ausreichenden Verringerung des zugewiesenen Speicherplatzes führt, müssen Sie Datendateien verkleinern. Als optionaler, aber empfohlener Schritt sollten Sie jedoch zunächst die durchschnittliche Seitendichte für die Indizes in der Datenbank ermitteln. Für die gleiche Datenmenge werden Schrumpfvorgänge schneller abgeschlossen, wenn die Seitendichte hoch ist, da weniger Seiten verschoben werden müssen. Wenn die Seitendichte für einige Indizes niedrig ist, sollten Sie eine Wartung dieser Indizes in Betracht ziehen, um die Seitendichte zu erhöhen, bevor Sie die Datendateien verkleinern. Eine höhere Seitendichte ermöglicht eine tiefere Reduzierung des zugewiesenen Speicherplatzes.

Um die Seitendichte für alle Indizes in der Datenbank zu ermitteln, verwenden Sie die folgende Abfrage. Die Seitendichte wird in der Spalte avg_page_space_used_in_percent angegeben.

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;

Wenn es Indizes mit einer hohen Seitenzahl und einer Seitendichte von weniger als 60-70 % gibt, sollten Sie in Erwägung ziehen, diese Indizes neu zu erstellen oder zu reorganisieren, bevor Sie die Datendateien verkleinern.

Bei größeren Datenbanken kann die Abfrage, um die Seitendichte zu ermitteln, eine lange Zeit in Anspruch nehmen. Das Neuerstellen oder Neuorganisieren großer Indizes erfordert auch eine erhebliche Zeit- und Ressourcennutzung. Die Indexwartung vor dem Verkleinern kann jedoch die Dauer des Verkleinerungsprozesses verkürzen und höhere Platzeinsparungen erzielen.

Wenn es mehrere Indizes mit geringer Seitendichte gibt, können Sie diese möglicherweise parallel in mehreren Datenbanksitzungen neu aufbauen, um den Prozess zu beschleunigen. Stellen Sie jedoch sicher, dass Sie sich dabei nicht den Grenzwerten für Datenbankressourcen nähern. Lassen Sie genügend Ressourcenpuffer für Anwendungs-Workloads, die möglicherweise ausgeführt werden. Überwachen Sie den Ressourcenverbrauch (CPU, Daten-E/A, Protokoll-E/A) im Azure-Portal oder verwenden Sie die sys.dm_db_resource_stats Ansicht. Starten Sie zusätzliche parallele Neuerstellungen nur, wenn die Ressourcenauslastung für jede dieser Dimensionen wesentlich niedriger als 100%bleibt. Wenn die CPU-, Daten-IO- oder Protokoll-IO-Auslastung bei 100 % liegt, können Sie die Datenbank skalieren, um mehr CPU-Kerne zu haben und den IO-Durchsatz zu erhöhen.

Beispielbefehl zum Neuerstellen des Index

Es folgt ein Beispielbefehl zum Neuerstellen eines Index und zum Erhöhen seiner Seitendichte mithilfe der ALTER INDEX-Anweisung:

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

Dieser Befehl initiiert einen Online-Index-Neuaufbau mit Wiederaufnahmefunktion. Mit diesem Vorgang können gleichzeitige Workloads die Tabelle weiterhin verwenden, während die Neuerstellung ausgeführt wird, und Sie können die Neuerstellung fortsetzen, wenn sie aus irgendeinem Grund unterbrochen wird. Diese Art des Rebuilds ist jedoch langsamer als ein Offline-Rebuild, bei dem der Zugriff auf die Tabelle blockiert wird. Wenn keine anderen Workloads während des Wiederaufbaus auf die Tabelle zugreifen müssen, setzen Sie die Optionen ONLINE und RESUMABLE auf OFF und entfernen Sie die Klausel WAIT_AT_LOW_PRIORITY.

Weitere Informationen zur Indexpflege finden Sie unter Optimierung der Indexpflege zur Verbesserung der Abfrageleistung und Reduzierung des Ressourcenverbrauchs.

LOB-Daten vor dem Verkleinern komprimieren.

Verkleinern kann länger dauern, wenn die Datenbank Folgendes enthält:

• LOB-Datentypen wie varchar(max), nvarchar(max), varbinary(max), xml oder ähnliche Datentypen, die in der LOB_DATA Zuordnungseinheit gespeichert sind.
• Große Zeilen , die in einer ROW_OVERFLOW_DATA Zuordnungseinheit gespeichert sind.
• Columnstore-Indizes.

Um den Shrinkvorgang zu beschleunigen und mehr Platz freizugeben, führen Sie zuerst eine Indexreorganisation mit LOB-Kompaktierung durch. LOB-Komprimierung vor der Verkleinerung wird für alle Indizes empfohlen, die LOB-Spalten oder große Zeilen enthalten. Beispiel:

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

Das Reorganisieren oder Neuerstellen von Columnstore-Indizes vor dem Verkleinern kann die Geschwindigkeit und Effektivität des Verkleinerungsvorgangs ebenfalls erhöhen.

Mehrere Datendateien schrumpfen

Wie bereits erwähnt, ist die Verkleinerung einschließlich der Datenverschiebung ein zeitaufwändiger Prozess. Wenn die Datenbank mehrere Datendateien enthält, können Sie den Vorgang beschleunigen, indem Sie mehrere Datendateien parallel verkleinern. Öffnen Sie mehrere Datenbanksitzungen, und verwenden Sie für jede Sitzung einen anderen file_id-Wert in DBCC SHRINKFILE. Ähnlich wie bei der Wiederherstellung von Indizes sollten Sie vor jedem neuen parallelen Verkleinerungsbefehl sicherstellen, dass Sie über genügend Ressourcen verfügen (CPU, Data IO, Log IO).

Der folgende Beispielbefehl verkleinert die Datendatei mit der file_id 4 und versucht, die ihr zugeteilte Größe durch Verschieben von Seiten innerhalb der Datei auf 52.000 MB zu reduzieren:

DBCC SHRINKFILE (4, 52000);

Wenn Sie den zugewiesenen Speicherplatz für die Datei so weit wie möglich reduzieren wollen, führen Sie die Anweisung ohne Angabe der Zielgröße aus:

DBCC SHRINKFILE (4);

Wenn ein Workload gleichzeitig mit Shrink läuft, kann er beginnen, den von Shrink freigegebenen Speicherplatz zu verwenden, bevor Shrink abgeschlossen ist und die Datei abgeschnitten wird. In diesem Fall kann der zugewiesene Speicherplatz nicht auf das gewünschte Ziel verkleinert werden.

Um dieses Problem zu vermeiden, verkleinern Sie jede Datei in kleineren Schritten. Legen Sie im DBCC SHRINKFILE Befehl das Ziel fest, das etwas kleiner als der aktuell zugewiesene Speicherplatz für die Datei ist, wie in den Ergebnissen der Basisspeichernutzungsabfrage zu sehen ist. Wenn der zugewiesene Speicherplatz für die Datei mit der Dateinummer 4 beispielsweise 200.000 MB beträgt und Sie auf 100.000 MB verkleinern möchten, können Sie das Ziel zunächst auf 170.000 MB setzen:

DBCC SHRINKFILE (4, 170000);

Mit diesem Befehl wird die Datei abgeschnitten und die zugeordnete Größe auf 170.000 MB reduziert. Sie können diesen Befehl dann wiederholen, indem Sie das Ziel zuerst auf 140.000 MB, dann auf 110.000 MB und so weiter festlegen, bis die Datei auf die gewünschte Größe geschrumpft ist. Wenn der Befehl abgeschlossen ist, die Datei aber nicht verkürzt wird, verwenden Sie kleinere Schritte, z. B. 15.000 MB statt 30.000 MB.

Um den Fortschritt der Verkleinerung für alle gleichzeitig laufenden Verkleinerungssitzungen zu überwachen, können Sie die folgende Abfrage verwenden:

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

Führen Sie nach der Verkleinerung für alle Datendateien die Abfrage zur Speicherplatznutzung (oder das Azure-Portal) erneut aus, um die resultierende Verringerung der zugewiesenen Speichergröße zu ermitteln. Wenn immer noch ein großer Unterschied zwischen dem verwendeten Raum und dem zugewiesenen Raum besteht, erstellen Sie Indizes neu. Eine Indexneuerstellung kann den zugewiesenen Speicherplatz vorübergehend weiter erhöhen. Das Verkleinern von Datendateien nach der Neuerstellung von Indizes sollte jedoch zu einer tieferen Verringerung des zugewiesenen Speicherplatzes führen.

Vorübergehende Fehler beim Schrumpfen

Gelegentlich kann ein Verkleinerungsbefehl mit verschiedenen Fehlern wie Timeouts und Deadlocks fehlschlagen. Im Allgemeinen sind diese Fehler vorübergehend und treten nicht wieder auf, wenn derselbe Befehl wiederholt wird. Wenn die Verkleinerung mit einem Fehler fehlschlägt, wird der bisher vorgenommene Fortschritt beibehalten. Führen Sie denselben Befehl zum Verkleinern erneut aus, um die Datei weiter zu verkleinern.

Das folgende Beispielskript zeigt, wie Sie in einer Wiederholungsschleife shrink ausführen können. Die Schleife wiederholt den Vorgang automatisch bis zu einer konfigurierbaren Anzahl von Wiederholungen, wenn ein Timeoutfehler oder ein Deadlock-Fehler auftritt. Dieser Ansatz der Wiederholung ist auf viele andere Fehler anwendbar, die beim Schrumpfen auftreten können.

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;

Zusätzlich zu Timeouts und Deadlocks können beim Verringern aufgrund bestimmter bekannter Probleme Fehler auftreten.

Die zurückgegebenen Fehler und Schritte zur Behebung sind wie folgt:

• Fehlernummer: 49503, Fehlermeldung: %.*ls: Die Seite %d:%d konnte nicht verschoben werden, da es sich um eine Seite handelt, die nicht in einer Zeile des persistenten Versionsspeichers liegt. Grund für das Zurückhalten der Seite: %ls. Zeitstempel der Seitenüberbrückung: %I64d.

Dieser Fehler tritt auf, wenn lange ausgeführte aktive Transaktionen vorhanden sind, die Zeilenversionen im permanenten Versionsspeicher (PVS) generiert haben. "Shrink kann die Seiten, die Zeilenversionen enthalten, nicht verschieben."

Um dies zu mildern, müssen Sie warten, bis lange ausgeführte Transaktionen abgeschlossen sind. Alternativ können Sie lang laufende Transaktionen identifizieren und beenden, dies kann sich jedoch auf Ihre Anwendung auswirken, wenn sie Transaktionsfehler nicht ordnungsgemäß behandelt. Eine Möglichkeit, langlaufende Transaktionen zu finden, besteht darin, die folgende Abfrage in der Datenbank auszuführen, auf der Sie den Befehl "shrink" angewendet haben.

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

Sie können eine Transaktion beenden, indem Sie den Befehl KILL verwenden und den zugehörigen Wert session_id aus dem Abfrageergebnis angeben:

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

Sobald die lang andauernden Transaktionen abgeschlossen sind, bereinigt eine interne Hintergrundaufgabe die nicht mehr benötigten Zeilenversionen. Sie können die PVS-Größe überwachen, um den Bereinigungsfortschritt zu messen, indem Sie die folgende Abfrage verwenden. Führen Sie die Abfrage in der Datenbank aus, in der Sie den Befehl shrink ausgeführt haben:

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

Sobald die in der Spalte persistent_version_store_size_gb gemeldete PVS-Größe im Vergleich zu ihrer ursprünglichen Größe erheblich reduziert ist, sollte das erneute Ausführen des Schrumpfvorgangs erfolgreich sein.

• Fehlernummer: 5223, Fehlermeldung: %.*ls: Die leere Seite %d:%d konnte nicht freigegeben werden.

Dieser Fehler kann auftreten, wenn es laufende Indexpflegeoperationen wie ALTER INDEX gibt. Wiederholen Sie den Befehl zum Verkleinern, nachdem diese Vorgänge abgeschlossen sind.

Wenn dieser Fehler weiterhin besteht, muss der zugehörige Index möglicherweise neu erstellt werden. Um den neu zu erstellenden Index zu finden, führen Sie die folgende Abfrage in derselben Datenbank aus, in der Sie den Befehl shrink ausgeführt haben:

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;

Ersetzen Sie vor dem Ausführen dieser Abfrage die Platzhalter <file_id> und <page_id> durch die tatsächlichen Werte aus der Fehlermeldung, die Sie erhalten haben. Wenn die Meldung lautet Leere Seite 1:62669 konnte nicht freigegeben werden, dann ist <file_id>1 und <page_id>62669.

Bauen Sie den von der Abfrage identifizierten Index neu auf und wiederholen Sie den Shrink-Befehl.

• Fehlernummer: 5201, Fehlermeldung: DBCC SHRINKDATABASE: Die Datei-ID %d der Datenbank-ID %d wurde übersprungen, weil die Datei nicht genügend freien Speicherplatz hat, um ihn wiederzugewinnen.

Dieser Fehler bedeutet, dass die Datendatei nicht weiter verkleinert werden kann. Sie können mit der nächsten Datei fortfahren.

Zugehöriger Inhalt

Informationen zur maximalen Datenbankgröße finden Sie unter:

• Beschränkungen des vCore-basierten Kaufmodells für eine einzelne Datenbank in Azure SQL-Datenbank
• Ressourcenlimits für Einzeldatenbanken, die DTU-basierte Kaufmodell verwenden
• Einschränkungen des vCore-basierten Kaufmodells für elastische Pools in der Azure SQL-Datenbank
• Ressourcengrenzwerte für elastische Pools, die das DTU-basierte Kaufmodell verwenden