Modern database environments require sophisticated maintenance strategies that can handle multiple databases efficiently while providing comprehensive reporting. This article presents an enterprise-grade T-SQL script that automates index maintenance across multiple databases, supports both tables and indexed views, and includes intelligent email reporting for proactive database administration.
The Evolution: From Single Database to Enterprise Scale
While basic index maintenance scripts focus on individual databases, enterprise environments demand solutions that can:
Process multiple databases with configurable scope
Provide comprehensive reporting via automated email notifications
Handle different object types (tables and indexed views) intelligently
Integrate seamlessly with SQL Server Agent for automation
Scale efficiently across large database environments
Enterprise-Grade Features
Multi-Database Processing Options
-- Database Scope Configuration
DECLARE @DatabaseScope NVARCHAR(20) = 'ALL_USER'; -- Options: 'CURRENT', 'ALL_USER', 'SPECIFIC'
DECLARE @SpecificDatabase NVARCHAR(128) = 'MyDatabase'; -- Used with 'SPECIFIC' scope
Three flexible modes:
- CURRENT: Process only the current database (perfect for database-specific jobs) 2 ALL_USER: Process all user databases automatically (enterprise-wide maintenance) SPECIFIC: Target a single named database (selective maintenance)
Intelligent Email Reporting
-- Email Configuration
DECLARE @SendEmail BIT = 1; -- Enable email reports
DECLARE @EmailProfile NVARCHAR(128) = 'Default'; -- Database Mail profile
DECLARE @EmailRecipients NVARCHAR(MAX) = '[email protected];[email protected]';
DECLARE @EmailSubjectPrefix NVARCHAR(100) = '[SQL Server]';
Smart email features:
Contextual subject lines: "ERRORS ENCOUNTERED", "MAINTENANCE COMPLETED", or "NO ACTION REQUIRED"
Detailed database-by-database breakdown
Comprehensive execution summary with success/failure tracking
Error alerting with specific failure details
Advanced Architecture
Cross-Database Statistics Collection
The script employs dynamic SQL to collect fragmentation statistics across multiple databases:
-- Dynamic cross-database analysis
SET @SQL = '
USE [' + @CurrentDatabase + '];
INSERT INTO #IndexStats (...)
SELECT ...
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''DETAILED'') ips
-- ... comprehensive analysis logic
';
EXEC sp_executesql @SQL;
This approach ensures:
Consistent analysis across all target databases
Centralized result aggregation for comprehensive reporting
Database-specific context preservation
Efficient resource utilization
Execution Status Tracking
```sql-- Execution tracking with detailed status
ALTER TABLE #IndexStats ADD ExecutionStatus NVARCHAR(50) DEFAULT 'PENDING';
-- Status updates during execution
UPDATE #IndexStats
SET ExecutionStatus = CASE
WHEN @success = 1 THEN 'SUCCESS'
ELSE 'ERROR: ' + ERROR_MESSAGE()
END;
Email Report Structure
The automated email reports provide comprehensive insights:
Executive Summary
Total databases processed
Aggregate index statistics
Overall success/failure metrics
Error count and types
Database-Specific Details
Per-database index counts
Fragmentation statistics
Maintenance actions performed
Execution results
Detailed Action Log
Individual index maintenance commands
Success/failure status for each operation
Fragmentation percentages before maintenance
Error details for failed operations
Production Deployment Strategies
SQL Server Agent Integration
Job 1: Weekly Analysis
```sql-- Configuration for analysis-only run
DECLARE @ExecuteCommands BIT = 0; -- Analysis mode
DECLARE @DatabaseScope NVARCHAR(20) = 'ALL_USER';
DECLARE @SendEmail BIT = 1; -- Send analysis report
Job 2: Weekend Maintenance
sql-- Configuration for full maintenance
DECLARE @ExecuteCommands BIT = 1; -- Execute maintenance
DECLARE @DatabaseScope NVARCHAR(20) = 'ALL_USER';
DECLARE @SendEmail BIT = 1; -- Send completion report
Environment-Specific Configurations
Development Environment:
```sql-- Aggressive maintenance for development
DECLARE @FragmentationThresholdReorganize FLOAT = 5.0;
DECLARE @FragmentationThresholdRebuild FLOAT = 20.0;
DECLARE @DatabaseScope NVARCHAR(20) = 'ALL_USER';
Production Environment:
```sql
-- Conservative settings for production
DECLARE @FragmentationThresholdReorganize FLOAT = 15.0;
DECLARE @FragmentationThresholdRebuild FLOAT = 40.0;
DECLARE @DatabaseScope NVARCHAR(20) = 'SPECIFIC'; -- Controlled targeting
Advanced Email Configuration
Database Mail Setup
Before using the email features, ensure Database Mail is configured:
```sql-- Enable Database Mail
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
-- Create mail profile (example)
```sql
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Notifications',
@description = 'Database maintenance notifications';
Recipient Management
```sql-- Multiple recipients with semicolon separation
DECLARE @EmailRecipients NVARCHAR(MAX) = '[email protected];[email protected];[email protected]';
Monitoring and Alerting Integration
SCOM/Nagios Integration
The email reports can be parsed by monitoring systems for automated alerting:
```sql-- Error-specific subject line for monitoring systems
SET @EmailSubject = @EmailSubjectPrefix + ' Index Maintenance Report - ' +
CASE
WHEN @TotalErrors > 0 THEN 'ERRORS ENCOUNTERED'
-- ... other conditions
END;
PowerBI Dashboard Integration
The script's output can feed into PowerBI dashboards for trend analysis:
Historical fragmentation trends
Maintenance success rates
Database growth patterns
Performance impact analysis
Performance Considerations
Resource Management
-- Staggered execution to manage resource usage
DECLARE maintenance_cursor CURSOR FOR
SELECT DatabaseName, MaintenanceCommand, RecommendedAction
FROM #IndexStats
WHERE MaintenanceCommand IS NOT NULL
ORDER BY DatabaseName, -- Database grouping
CASE ObjectType WHEN 'TABLE' THEN 1 ELSE 2 END, -- Tables first
CASE RecommendedAction WHEN 'REBUILD' THEN 1 ELSE 2 END; -- Rebuilds first
Memory Optimization
Cursor-based processing to handle large result sets efficiently
Dynamic SQL execution to minimize memory footprint per database
Temporary table cleanup to prevent memory leaks
Security and Compliance
Permission Requirements
The script requires specific permissions:
db_owner on target databases for index maintenance
DatabaseMailUserRole in msdb for email functionality
View any database for cross-database statistics collection
Audit Trail
All activities are logged and reported:
Command execution details in email reports
Error logging with full context
Success metrics for compliance reporting
Source code: https://github.com/gpiwonka/SQLScripts/blob/master/IndexMaintenanceScript.sql
more ... https://piwonka.cc
Top comments (0)