Parametrisierung der Hallengren Jobs

Der schwedische DBA und DB-Entwickler Ola Hallengren hat mit seiner Maintenance Solution ein Script geschaffen, das in ca. 9000 T-SQL Zeilen einige der am häufigsten verwendeten MSSQL-Wartungsjobs einrichtet. Der Aufruf dieses Scripts erstellt jedoch nur die Jobs, ohne hierzu das Scheduling einzurichten.

Auf dieser Seite werden einige Empfehlungen zum Setup der Jobs gegeben, nachdem sie durch Ausführung der T-SQL-Datei eingerichtet wurden. Die folgenden Kapitel geben zu jedem dieser Jobs einige Zusatzinformationen, -Konfigurationen und Empfehlungen an.

Die hier wiedergegebenen Empfehlungen berücksichtigen nicht alle möglichen Parameter. Es gibt viele Einstellungen für die es ohne verwendete Parametrisierung entsprechende Default-Werte gibt. Einer dieser Werte ist der Parameter @MinNumberOfPages des Jobs IndexOptimize - USER_DATABASES bzw. der Prozedur IndexOptimize. Dieser Wert ist per Default auf 1000 gesetzt, was bedeutet, dass Indizes mit weniger als 1000 Seiten von der Indexoptimierung ausgenommen werden.

Eine detaillierte Auflistung der Parametrisierung findet man unter SQL Server Index and Statistics Maintenance . Es lohnt sich auch, sich die Stored Procedures und deren mögliche Parameter nach Installation der Maintenance-Solution anzusehen.

CommandLog Cleanup

Durch das Setup-Script wird in der Master-Datenbank eine Tabelle mit dem Namen CommandLog angelegt. Werden die Wartungsjobs so parametrisiert, dass ihre Aktionen protokolliert werden (Parameter @LogToTable= 'Y'), so werden diese Aktionen in die CommandLog Tabelle geschrieben. Der Job CommandLog Cleanup kann verwendet werden, um diese Tabelle regelmäßig zu bereinigen.

Standardeinstellung

Es werden alle Einträge die älter als 30 Tage sind aus der CommandLog-Tabelle gelöscht.

DELETE FROM [dbo].[CommandLog]
  WHERE StartTime < DATEADD(dd,-30,GETDATE())

Änderungsempfehlung

keine

Scheduling

In der Regel bleibt die CommandLog Tabelle sehr klein, so dass es hier keine Performancebeeinträchtigung gibt.
Empfehlung: Täglich um 22:00

DatabaseBackup - SYSTEM_DATABASES - FULL

Vollsicherung der Systemdatenbanken. Die Sicherungen werden über die zentrale von Hallengren bereitgestellte Stored Procedure DatabaseBackup durchgeführt, die je nach gewünschtem Datenbank- und Backup-Typ parametrisiert wird.

Standardeinstellung

Die Backups werden in der CommandLog-Tabelle protokolliert (@LogToTable), aber nicht bereinigt (@CleanupTime = NULL).

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'SYSTEM_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'

Änderungsempfehlung

Die msdb enthält eine Tabelle in der alle Backup-spezifischen Informationen abgelegt werden. Diese kann unter Umständen sehr groß werden und in der Regel sind die älteren Einträge für ein eventuelles Recovery irrelevant. Deshalb wird empfohlen, Einträge nach einer gewissen Zeit (Parameter @CleanupTime) zu löschen. Die Zeitangabe erfolgt in Stunden, hier sind also 30 Tage (30 * 24 Std = 720) gewählt.

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'SYSTEM_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 720,
@CheckSum = 'Y',
@LogToTable = 'Y'

Scheduling

Wegen der zentralen Rolle der Systemdatenbanken und da die Backups der System-DBs nach sehr kurzer Zeit beendet sind, sollten diese täglich gesichert werden.

Empfehlung: Täglich um 22:05 bzw. nach dem Konsistenz-Check der Systemdatenbanken. Es sollte verifiziert werden, ob die Konsistenz-Prüfung innerhalb der vorgesehenen 5 Minuten (22:00 (s.u.) - 22:05) abgeschlossen ist. Ggf. das System-Backup-Timing anpassen.

DatabaseBackup - USER_DATABASES - DIFF

Differentielles Backup der Benutzerdatenbanken.

Bei allen Sicherungen der Benutzerdatenbanken müssen immer die DB-Größen, der Transaktionsumfang, der maximale Datenverlust und die Anforderung an die Wiederherstellungszeit berücksichtigt werden. Außerdem die Zeiten zu denen die Instanz normalerweise wenig verwendet wird. Die Empfehlungen hier beziehen sich auf eine sehr große Datenbank (im Bereich mehrerer TB) mit einer hohen Transaktionslast, einem maximalen Datenverlust von 5 Minuten. Die Dauer eines Recovery sei bei den Annahmen hier zweitrangig. Abends ab 18:00 wird die Instanz ab 18:00 nur gering genutzt.

Standardeinstellung

Die Backups werden in der CommandLog-Tabelle protokolliert (@LogToTable), aber nicht bereinigt (@CleanupTime = NULL).

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'DIFF',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'

Änderungsempfehlung

Änderungsempfehlung wie bei dem Backup der System-Datenbanken.

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'DIFF',
@Verify = 'Y',
@CleanupTime = 720,
@CheckSum = 'Y',
@LogToTable = 'Y'

Scheduling

Empfehlung: Täglich um 18:00, außer Freitags, weil an diesem Tag die Vollsicherung erstellt wird.

DatabaseBackup - USER_DATABASES - FULL

Vollsicherung der Benutzerdatenbanken.

Siehe auch Anmerkungen unter “DatabaseBackup - USER_DATABASES - DIFF”

Standardeinstellung

Die Backups werden in der CommandLog-Tabelle protokolliert (@LogToTable), aber nicht bereinigt (@CleanupTime = NULL).

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'

Änderungsempfehlung

Änderungsempfehlung wie bei dem Backup der System-Datenbanken.

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 720,
@CheckSum = 'Y',
@LogToTable = 'Y'

Scheduling

Empfehlung: Freitags um 18:00, an den übrigen Wochentagen läuft das differentielle Backup.

DatabaseBackup - USER_DATABASES - LOG

TLog-Sicherung der Benutzerdatenbanken.

Siehe auch Anmerkungen unter “DatabaseBackup - USER_DATABASES - DIFF”

Standardeinstellung

Die Backups werden in der CommandLog-Tabelle protokolliert (@LogToTable), aber nicht bereinigt (@CleanupTime = NULL).

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'

Änderungsempfehlung

Änderungsempfehlung wie bei dem Backup der System-Datenbanken.

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 720,
@CheckSum = 'Y',
@LogToTable = 'Y'

Scheduling

Empfehlung: Alle 5 Minuten. Je länger man den Zeitraum zwischen zwei TLog Backups wählt, desto höher ist im Disaster-Fall der maximale Datenverlust, desto größer ist die Gefahr des starken Anwachsens des TLog-Files und desto länger dauert natürlich auch die einzelne TLog-Sicherung. Bei Datenbanken mit sehr hoher Anforderung an den maximalen Datenverlust kann man auch eine minütliche TLog-Sicherung sehen.

DatabaseIntegrityCheck - SYSTEM_DATABASES

Integritäts-Check der System-Datenbanken.

Standardeinstellung

Die Integritäts-Checks werden in der CommandLog-Tabelle protokolliert (@LogToTable).

EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'SYSTEM_DATABASES',
@LogToTable = 'Y'

Änderungsempfehlung

keine

Scheduling

Empfehlung: Täglich vor dem Backup der System-Datenbanken, beispielsweise um 22:00. Es sollte verifiziert werden, ob die Prüfung innerhalb der vorgesehenen 5 Minuten bis zum Backup um 22:05 abgeschlossen ist. Ggf. das System-Backup-Timing anpassen.

DatabaseIntegrityCheck - USER_DATABASES

Integritäts-Check der Benutzerdatenbanken.

Standardeinstellung

Die Integritäts-Checks werden in der CommandLog-Tabelle protokolliert (@LogToTable).

EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y'

Änderungsempfehlung

keine

Scheduling

Empfehlung: Falls zeitlich die Möglichkeit besteht, vor jedem Voll- und differentiellen Backup die Integrität prüfen: Die Sicherung einer inkonsistenten Datenbank wäre im Recovery-Fall unbrauchbar. Es sollte regelmäßig verifiziert werden, dass die Prüfung bis zum Backup abgeschlossen ist. Ggf. das DB-Backup-Timing anpassen.

IndexOptimize - USER_DATABASES

Dieser Job führt Index-Optimierungen in den Benutzerdatenbanken durch

Standardeinstellung

Die Indexoptimierungen werden in der CommandLog-Tabelle protokolliert (@LogToTable).

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y'

Änderungsempfehlung

Die Standardeinstellung für den Aufruf der StoredProcedure ist

Fragmentierung > 30%: Rebuild
Fragmentierung > 5%: Reorganisation

Abweichend davon empfiehlt Brent Ozar unter How to Configure Ola Hallengren's IndexOptimize Maintenance Script - Brent Ozar Unlimited® die Werte 50% bzw. 80%. Auf dieser Seite wird auch empfohlen, die Standard-Aktivität für @FragmentationMedium und @FragmentationHigh zu ändern. Wie zu Beginn dieses Dokuments erwähnt, spielt bei der Verwendung der Prozedur [dbo].[IndexOptimize] der Parameter @MinNumberOfPages eine Rolle: Wird er nicht eingesetzt, so wird der Default-Wert 1000 verwendet, d.h. es werden nur Indizes optimiert, die mindestens 1000 Seiten groß sind.

Dem entsprechend die folgende Änderung (ACHTUNG: INDEX_REBUILD_ONLINE erfordert eine Enterprise Edition).

Im Zusammenhang mit dem Index-Rebuild können auch die Statistiken aktualisiert werden. Hierzu existieren die beiden Parameter @UpdateStatistics und @StatisticsSample. Im Normalfall ermittelt MSSQL den Umfang in Prozent der Statistikermittlung automatisch. Möchte man von diesem Defaultwert abweichen, kann man den Wert von @StatisticsSample bspw. auf 100 (= 100%) setzen, damit ein vollständiger Scan für alle Tabellen durchgeführt wird.

Beispiele für eine konkrete Instanz:

Ohne @UpdateStatistics, ohne @StatisticsSample: 4 Sek.
Mit @UpdateStatistics = ‘ALL’, ohne @StatisticsSample: 1:02
Mit @UpdateStatistics = ‘ALL’, mit @StatisticsSample = 100: 2:12

-- Standard der Stored Procedure IndexOptimize:
-- @FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
-- @FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y',
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80
-- ,@MinNumberOfPages =???
-- ,@UpdateStatistics  = 'ALL'
-- ,@StatisticsSample = 100

Scheduling

Empfehlung: Wie die Integritätsprüfung sollte auch die Indexwartung vor dem Backup abgeschlossen sein. Andernfalls müsste man im Falle eines erforderlichen Recovery die Indexoptimierung erneut durchführen (oder mit nicht-reorganisierten Indizes bis zum nächsten Lauf dieses Jobs warten.

Sofern ausreichend Zeit ist und die Verteilung der Daten sehr unregelmäßig ist, sollten alle Statistiken komplett aktualisiert werden. Ggf. zwei / mehrere Jobs einrichten, z.B. täglich nur IndexOptimize, am Wochenende den zweiten Job mit @UpdateStatistics = ‘ALL’ und @StatisticsSample = 100.

Output File Cleanup

Dieser Job führt eine Dateibereinigung in dem MSSQL-Log-Verzeichnis durch. Das Verzeichnis liegt unter

C:\Program Files\Microsoft SQL Server\MSSQL16.Instanzname\MSSQL\Log und es werden alle Dateien gelöscht, die im Namen mindestens drei Underscores enthalten und deren Name mit .txt endet und die älter als 30 Tage sind. Die Dateien stammen aus der Protokollierung der Hallengren-Scripte.

cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "$(ESCAPE_SQUOTE(SQLLOGDIR))" /m *_*_*_*.txt /d -30 2^>^&1') do if EXIST "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v echo del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v& del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v"

Änderungsempfehlung

keine

Scheduling

Das Script führt zu keiner nennenswerten Last auf dem Server, so dass es täglich zu einer beliebigen Zeit ausgeführt werden kann.

sp_delete_backup_history

Alle Informationen bezüglich durchgeführter Backups werden in verschiedenen Systemtabellen der msdb-Systemdatenbank abgelegt, um im Problemfall Recovery-relevante Informationen vorzuhalten, wie z.B.

wo liegt das Full-Backup
wo liegen eventuelle differentielle Backups
wo liegen TLog-Backups
welche TLog-Backups werden für ein Recovery bis zum Zeitpunkt X benötigt

Bei einem gewissen Alter wird davon ausgegangen, dass entsprechend alte Backups für ein Recovery nicht mehr benötigt werden. Die Bereinigung erfolgt für Einträge die sich auf Backups beziehen, die älter als 30 Tage sind. Sofern die Cleanup-Zeit hier geändert wird, sollte sie auch bei den Backups im Parameter @CleanupTime entsprechend angepasst werden.

Standardeinstellung

Löschen aller Einträge die älter als 30 Tage sind.

DECLARE @CleanupDate datetime
SET @CleanupDate = DATEADD(dd,-30,GETDATE())
EXECUTE dbo.sp_delete_backuphistory @oldest_date = @CleanupDate

Änderungsempfehlung

keine

Scheduling

Das Script führt zu keiner nennenswerten Last auf dem Server, so dass es täglich zu einer beliebigen Zeit ausgeführt werden kann, eventuell aber auch nur im Rahmen von Backups.

sp_purge_jobhistory

Jede Ausführung von Agent-Jobs in der Instanz führt zu mehreren Einträgen in der Tabelle msdb.dbo.sysjobhistory. In dieser Tabelle können sich im Laufe der Zeit sehr viele Einträge ansammeln, insbesondere, wie bei einem unserer Kunden gesehen, wenn es Jobs gibt, die minütlich ausgeführt werden.

Zur Bereinigung existiert in der Systemdatenbank msdb die Stored Procedure [dbo].[sp_purge_jobhistory] der als Parameter ein Jobname, eine Job-ID und/oder ein bestimmtes Datum übergeben werden kann, um Job-Logs zu löschen die die angegebenen Kriterien erfüllen. Im Fall des Datums alle Logs die älter sind als dieses Datum.

SSMS bietet in dem Fenster zur Anzeige der Job-Historie ebenfalls die Möglichkeit, über den Delete-Button die Stored Procedure [dbo].[sp_purge_jobhistory] mit Parametern zu versorgen und aufzurufen. “Problem” hierbei: Eine solcher Aufruf muss explizit ausgelöst werden und wer denkt schon bei den vielen wichtigeren täglichen Aufgaben an die Job-Historie. Dies erledigt dieser Hallengren Job, sofern Sie diesen Job verwenden.

Standardeinstellung

Löschen aller Einträge die älter als 30 Tage sind.

DECLARE @CleanupDate datetime
SET @CleanupDate = DATEADD(dd,-30,GETDATE())
EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate

Änderungsempfehlung

keine -

Scheduling

Das Script führt zu keiner nennenswerten Last auf dem Server, so dass es täglich zu einer beliebigen Zeit ausgeführt werden kann.

Fazit

Das Hallengren Wartungsscript ist ein praktisches Mittel um die wichtigsten DB-Wartungssjobs anzulegen. Für das Scheduling ist der DBA verantwortlich. In diesem Beitrag wurden die verschiedenen Jobs beschrieben und Empfehlungen zum Scheduling gegeben. Sollten Sie Fragen zu diesem Thema haben, können Sie sich gerne an unsere Experten wenden und einen unverbindlichen Termin über unser Kontaktformular vereinbaren.

Parametrisierung der Hallengren Jobs

CommandLog Cleanup

Standardeinstellung

Änderungsempfehlung

Scheduling

DatabaseBackup - SYSTEM_DATABASES - FULL

Standardeinstellung

Änderungsempfehlung

Scheduling

DatabaseBackup - USER_DATABASES - DIFF

Standardeinstellung

Änderungsempfehlung

Scheduling

DatabaseBackup - USER_DATABASES - FULL

Standardeinstellung

Änderungsempfehlung

Scheduling

DatabaseBackup - USER_DATABASES - LOG

Standardeinstellung

Änderungsempfehlung

Scheduling

DatabaseIntegrityCheck - SYSTEM_DATABASES

Standardeinstellung

Änderungsempfehlung

Scheduling

DatabaseIntegrityCheck - USER_DATABASES

Standardeinstellung

Änderungsempfehlung

Scheduling

IndexOptimize - USER_DATABASES

Standardeinstellung

Änderungsempfehlung

Scheduling

Output File Cleanup

Änderungsempfehlung

Scheduling

sp_delete_backup_history

Standardeinstellung

Änderungsempfehlung

Scheduling

sp_purge_jobhistory

Standardeinstellung

Änderungsempfehlung

Scheduling

Fazit

Auf dieser Seite

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.