DOCSEARCHbyAlgolia
Submit Docs

SQL Server Maintenance Solution

repository·main·Indexed 25 days ago

https://github.com/olahallengren/sql-server-maintenance-solution

A comprehensive SQL Server maintenance solution providing automated scripts for database backups, integrity checks, and index/statistics maintenance. Key components include DatabaseBackup for flexible backup operations (supporting local, network, Azure Blob Storage, and S3), IndexOptimize for index and statistics management, and DatabaseIntegrityCheck. The solution includes support for encryption, compression, and parallel processing via Queue scripts.

Tokens
10.1K
Snippets
19
Records
42
Agent score
82%

What's inside sql-server-maintenance-solution

  4. Download the SQL Server Maintenance Solution scripts

    main

    Choose the appropriate script based on your SQL Server platform:

    • For SQL Server 2017, 2019, 2022, 2025, and Azure SQL Managed Instance: Use MaintenanceSolution.sql. This script creates all necessary objects and jobs.
    • For Azure SQL Database: Use MaintenanceSolutionAzureSQLDatabase.sql. This script creates objects for integrity checks and index/statistics maintenance.

  5. Install individual maintenance components

    main

    If you do not want the full solution, you can install individual components as separate scripts.

    Important Dependencies:

    • CommandExecute is a required dependency for DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize.
    • When updating DatabaseBackup, DatabaseIntegrityCheck, or IndexOptimize, you must also update CommandExecute.
    • CommandLog is required if you intend to use the option to log commands to a table.
    • Queue.sql and QueueDatabase.sql are used for processing databases in parallel.

    Available Scripts:

    • DatabaseBackup.sql: Stored procedure to back up databases.
    • DatabaseIntegrityCheck.sql: Stored procedure to check the integrity of databases.
    • IndexOptimize.sql: Stored procedure to rebuild/reorganize indexes and update statistics.
    • CommandExecute.sql: Stored procedure to execute and log commands.
    • CommandLog.sql: Table to log commands.
    • Queue.sql: Table for parallel processing.
    • QueueDatabase.sql: Table for parallel processing.

  8. Optimize index maintenance performance and constraints

    main

    Enhance dbo.IndexOptimize execution with the following options:

    • Performance: Use @SortInTempdb = 'Y' to perform sort operations in tempdb and @MaxDOP = 0 to use all available CPUs.
    • Partitioning: Use @PartitionLevel = 'Y' to maintain partitioned indexes at the partition level.
    • Time Limits: Use @TimeLimit = <seconds> to ensure no new commands are executed after a specific duration.
    • Logging: Use @LogToTable = 'Y' to record maintenance results in a table.
    -- Example: Rebuild indexes with tempdb sorting, max DOP, and a 1-hour time limit
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@MaxDOP = 0,
@TimeLimit = 3600

  9. Install the SQL Server Maintenance Solution

    main

    To use the DatabaseIntegrityCheck stored procedure, you must first install the maintenance solution objects. Choose the script corresponding to your SQL Server platform:

    • SQL Server 2017, 2019, 2022, 2025, and Azure SQL Managed Instance: Use MaintenanceSolution.sql. This script creates all necessary objects and jobs.
    • Azure SQL Database: Use MaintenanceSolutionAzureSQLDatabase.sql. This script creates objects for integrity check and index/statistics maintenance.

  10. Configure custom directory structures and file names

    main

    Control how backup files are organized using @DirectoryStructure and @FileName. You can use tokens like {ServerName}, {InstanceName}, {DatabaseName}, {BackupType}, and {Year}{Month}{Day} to create dynamic paths. Setting these to NULL will prevent the creation of sub-directories.

    -- Custom directory structure without server/instance names
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@DirectoryStructure = '{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}',
@AvailabilityGroupDirectoryStructure = '{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}'

  11. Target specific indexes or exclude them

    main

    Use the @Indexes parameter to control which indexes are processed:

    • Specific Index: Provide the fully qualified name (e.g., 'DatabaseName.SchemaName.TableName').
    • Exclusion: Use the syntax 'ALL_INDEXES, -DatabaseName.SchemaName.TableName' to process all indexes except the one specified with a minus sign.
    -- Rebuild all indexes except for a specific table
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'ALL_INDEXES, -AdventureWorks.Production.Product'

  12. Back up databases to multiple network shares

    main

    You can distribute backups across multiple network shares by providing a comma-separated list to @Directory and specifying the number of files via @NumberOfFiles.

    EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '\\Server1\Backup, \\Server2\Backup, \\Server3\Backup, \\Server4\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@NumberOfFiles = 4