In diesem Artikel wird die SQL Server Auto Shrink-Datenbank-Eigenschaft besprochen, die es SQL Server ermöglicht, die Datenbankdateien automatisch zu verkleinern, wenn der Wert in der Datenbankoption als True konfiguriert ist. Die automatische Verkleinerung wird von der Serverfunktion „Auto Shrink Database“ durchgeführt, die das Hauptthema dieses Artikels ist.
Um die Aktivität der Verkleinerung von Daten-/Protokolldateien zu vermeiden, wenn die Dateigröße den freien Speicherplatz in der Datendatei übersteigt, muss der DBA (Datenbankadministrator) die Protokolldateien in regelmäßigen Abständen sichern. Die Sicherung der gesamten Datenbank ist keine gute Idee; man muss auch das Transaktionsprotokoll erstellen oder einrichten. Die Anzahl der Transaktionsprotokolle nimmt zu, bis der gesamte verfügbare Speicherplatz belegt ist, wenn Sie keine Sicherungskopie davon erstellen. Wenn Sie die Datenbank sichern, wird der freie Speicherplatz wieder freigegeben und kann erneut verwendet werden. Der Datenbankadministrator muss eine Sicherung des Transaktionsprotokolls einplanen, um die Größe der Protokolldateien auf eine angemessene Größe zu reduzieren.
Die Funktion „Automatisches Schrumpfen“ von SQL Server ist standardmäßig auf SQL Server-Instanzdatenbanken deaktiviert. In dem Szenario, in dem Sie eine Reihe von relativ kleinen Datenbanken haben, die entweder durch das Einfügen neuer Tupel oder durch eine große Anzahl von leeren Bereichen, die durch das Löschen mehrerer Tupel entstanden sind, zu einer größeren Größe anwachsen, wird SQL Server Auto Shrink in dieser Situation ziemlich praktisch. Außerdem müssen Sie sich keine Sorgen über die Fragmentierung der Datenbankdateien machen.
Beim Verkleinern der Datenbank müssen Sie sowohl kritische als auch relativ große Datenbanken im Rahmen der Wartung verkleinern. Außerdem sollte man es vermeiden, die manuelle Verkleinerungsfunktion auszuführen; dies hat zur Folge, dass man nie mit den Problemen in Bezug auf die neue oder bestehende Anforderung vertraut ist. Das Schrumpfen von Transaktionsdateien ist jedoch besser als das Schrumpfen von Datendateien.
Wie aktiviert und deaktiviert man das automatische Verkleinern von SQL Server für die Datenbank?
Benutzer können die Option zum automatischen Verkleinern der Datenbank sowohl mit SSMS als auch mit T-SQL aktivieren und deaktivieren.
Aktivieren des automatischen Verkleinerns der Datenbank mit SSMS:
Benutzer können diese Option in den Eigenschaften der Datenbank mit dem Tag „Auto Shrink“ aktivieren oder deaktivieren. Hier wird True in der Dropdown-Liste diese Option für Ihre Datenbank aktivieren.
Autologisches Schrumpfen der Datenbank mit T-SQL aktivieren:
Benutzer können die folgenden T-SQL-Anweisungen ausführen, um das Schrumpfen der Datenbank zu aktivieren oder zu deaktivieren. Wir haben die AdventureWorks-Datenbank für die obige T-SQL-Anweisung verwendet.
|
1
2
3
4
5
6
|
–Aktivieren der automatischen Verkleinerung für die Datenbank AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
–Deaktivieren Sie das automatische Schrumpfen für die Datenbank AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Auswirkungen des Schrumpfens der Datenbank auf die Abfrageleistung
Schlechte Auswirkungen können auf der Seite der Abfrageleistung entstehen, wenn Sie die Option zum automatischen Schrumpfen der Datenbank und die Einstellungen für das automatische Wachstum zusammen für die Datenbank aktivieren. Wir können etwas Raum für die Datendateien zu wachsen und auch einige häufige Auto-Growth-Ereignisse zu vermeiden, indem Sie die Datenbankgröße auf einen optimalen Wert oder meist jede Datenbank haben einige Parameter mit Auto-Growth-Funktionen aktiviert.
Für solche Datenbanken, Auto-Shrink-Funktionen müssen von uns aktiviert werden, wenn die Datenbank kleiner ist, eine und keine CRUD-Operation mehr, so kann es Schrumpfung von Datendateien möglich zu machen und rufen Sie den freien Speicherplatz, die wir gezielt zur Verfügung gestellt, um unsere Datenbanken von Auto-Growth-Ereignisse fernzuhalten. Freier Speicherplatz wird automatisch in den Daten- und Protokolldateien in regelmäßigen Abständen in sequenzieller Round-Robin-Priorität durch die Auto-Shrink-Funktion freigegeben, wenn mehrere Datenbanken diese Funktion aktiviert haben.
Bei großen Datenbanken wird das Auto-Growth und danach das Auto-Shrink ausgeführt, was zu Leistungsproblemen durch Fragmentierung auf Systemebene führt. Zusammenfassend lässt sich sagen, dass die automatische Verkleinerung aus den folgenden Gründen nicht für jede Datenbank aktiviert werden sollte:
- Die Ausführung der SQL Server Auto-Shrink-Algorithmen ohne jeglichen Zweck wird definitiv die Ressourcen in einer massiven Menge verschwenden
- Egal, ob Sie SQL Server Auto-Shrink oder manuelles Shrink ausführen, es wird offensichtlich die Indexfragmentierung verursachen und dies führt schließlich auch zum Schrumpfen der Datendateien
- Wenn der Server auch an die Grenzen des IO-Subsystems stößt, kann das Ausführen von Shrink dieses überfordern, Dies führt zu langen Warteschlangen auf der Festplatte und möglicherweise zu IO-Timeouts, was die IO- und CPU-Ressourcen des Servers massiv beansprucht
- Die Systemleistung wird durch die Fragmentierung des Dateisystems auf Festplattenebene gebremst, was wiederum die direkte Auswirkung des häufigen Verkleinerns und Vergrößerns von Datendateien ist
Um mehr über Wachstums- und Schrumpfungsereignisse in SQL Server zu erfahren, lesen Sie diesen Artikel: Details zu Wachstums- und Schrumpfungsereignissen in SQL Server-Datenbanken.
Für kritische Datenbanken kann der Experte eine manuelle Schrumpfungsoperation auf der Ebene der Datenbankdatei durchführen. Die manuelle Verkleinerung der Datei kann durchgeführt werden, wenn der Löschvorgang ausgeführt wurde und danach Speicherplatz zurückgewonnen wurde. Die fragmentierten Indizes müssen nach der Ausführung des Schrumpfungsvorgangs neu aufgebaut werden, da der Schrumpfungsvorgang zu einer Fragmentierung des Index führen kann. Der Prozentsatz der Indexfragmentierung kann vom Benutzer mit Hilfe von T-SQL-Anweisungen der DMVs überprüft werden. Das Verkleinern der Protokolldatei sollte jedoch nach Bedarf manuell durchgeführt werden und sollte nicht Teil der regelmäßigen Wartungsaktivitäten sein.
Um die Festplattennutzung regelmäßig zu überwachen, kann der Benutzer den Festplattennutzungsbericht analysieren, bevor er die manuelle Verkleinerungsoperation mit SSMS durchführt. Wenn der Benutzer ein Dashboard zur Berechnung des reservierten und des freien Speicherplatzes für Datenbankdateien haben möchte, ist der Festplattenbericht sehr hilfreich. Der Festplattenbericht enthält jedoch die Informationen über SQL Server DMVs. Der Festplattenbericht ist im folgenden Verzeichnis mit SSMS verfügbar.
Datenbank >> Berichte >> Standardberichte >> Festplattennutzung
Hier haben wir einen Festplattenbericht für die Datenbank. Echtzeitstatistiken für die Datendateien und Protokolldateien können vom Benutzer eingesehen werden. Dieser Bericht enthält primäre Informationen über den reservierten Gesamtspeicherplatz, den reservierten Speicherplatz für Datendateien, den reservierten Speicherplatz für Transaktionsprotokolle und den reservierten Speicherplatz für In-Memory-OLTP-Datenbanken.
Datenbank automatisch schrumpfen mit SQL Server-Jobs
Solche Skripte können durch die Planung eines Jobs durch den Benutzer ausgeführt werden, um den Datenbankschrumpfungsvorgang mit Scheduler-Aktivität auszuführen. Der erste Schritt des Skripts besteht darin, den freien Speicherplatz in der Datenbankdatei zu ermitteln und dann die Datei zu verkleinern, wenn die festgelegten Kriterien für den freien Speicherplatz erfüllt sind. Der freie Speicherplatz sollte mit Hilfe der SQL Server DMV’s berechnet werden.
Grundsätzlich wird die Überwachung der Datenbankdateien vom Datenbankadministrator durchgeführt, der seinerseits die Größe der Datenbankdateien überwacht. Wenn die vordefinierte Grenze von einer Datei überschritten wird, müssen sie solche Schritte durchführen. Diese Tätigkeit kann mit Hilfe eines SQL Server-Auftrags automatisiert werden, der täglich, wöchentlich oder monatlich zu einem vordefinierten Zeitpunkt ausgeführt wird.
Am besten ist es, diesen Auftrag nur für die Datenbankprotokolldatei zu planen und den freien Speicherplatz der Datendatei manuell zu überwachen. Denn dies kann auch die Abfrageleistung beeinträchtigen. SQL Server DMV gibt die Datendatei und die Protokolldatei in derselben Ergebnismenge zurück, so dass der Dateityp in der T-SQL-Anweisung von SQL Server DMV verzweigt werden muss. Bevor die Datei verkleinert wird, kann der Benutzer eine Abfragelogik auf den Gesamtspeicherplatz und den freien Speicherplatz der Protokolldatei anwenden. Wenn die Berechnungskriterien mit der Dateieigenschaft übereinstimmen, wird die Datei mit der Zieldateigröße verkleinert.
Prüfen Sie den freien Speicherplatz für die Datenbankdateien:
Wenn solche Kriterien mit den Datenbankdateien übereinstimmen, z. B. wenn der freie Speicherplatz größer als (n) MB/GB ist, wenn n(%) freier Speicherplatz mit dem Gesamtspeicherplatz verglichen wird, und viele mehr. Wenn kein freier Speicherplatz für die Protokolldatei verfügbar ist und die Dateigröße in der Nähe des Parameterwerts für die maximale Dateigröße liegt, muss der Datenbankadministrator das Transaktionsprotokoll durchsuchen.
Datenbankdatei schrumpfen:
|
1
|
DBCC SHRINKFILE(file_name, 5120);
|
Hier ist 5120 eine Zieldateigröße in MB. Die Datei wird also eine Größe von 5120 MB haben. Richten Sie die T-SQL-Abfrage mit den erforderlichen Kriterien im SQL Server-Jobschritt ein und planen Sie sie stundenweise ein.
Die Option SQL Server auto shrink kann nicht für alle Datenbanken aktiviert werden. Insbesondere hilft sie bei kleineren Datenbanken, die vergleichsweise weniger CRUD-Operationen durchführen.
Abschluss
In diesem Artikel haben wir die Auto Shrink-Datenbankeigenschaft in SQL Server besprochen, um Daten und Protokolldateien zu verkleinern und ungenutzten Speicherplatz zu entfernen. Das Verkleinern einer Datenbank ist ein teurer Vorgang und sollte mit Bedacht eingesetzt werden.
- Autor
- Recent Posts
Alle Beiträge von Jignesh Raiyani anzeigen
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Automatisieren der Tabellenpartitionierung in SQL Server – 7. Juli 2020
- Konfigurieren von SQL Server Always On Availability Groups auf AWS EC2 – 6. Juli 2020