Production-tested T-SQL scripts for SQL Server database maintenance, monitoring, security, and performance optimization.
These scripts are used in production environments to maintain SQL Server databases supporting enterprise applications with 150+ daily users. They cover index optimization, statistics management, backup verification, health monitoring, security auditing, Extended Events, capacity planning, configuration validation, and performance analysis.
- Rebuild-FragmentedIndexes.sql - Identifies and rebuilds/reorganizes indexes based on configurable fragmentation thresholds with full audit logging
- Find-DuplicateIndexes.sql - Detects overlapping and exact duplicate indexes wasting disk space and slowing writes
- Find-UnusedIndexes.sql - Identifies indexes with zero reads but ongoing write overhead, with drop scripts
- Find-HeapTables.sql - Finds tables without clustered indexes, reports forwarding pointer counts and size
- Find-NonIndexedForeignKeys.sql - Locates foreign keys missing supporting indexes, generates CREATE INDEX scripts
- Update-OutdatedStatistics.sql - Updates statistics exceeding modification thresholds with full scan or sampled options
- Verify-BackupIntegrity.sql - Validates backup chain integrity, checks gaps, reports RPO compliance
- Generate-RestoreScript.sql - Auto-generates complete RESTORE commands from backup history (Full + Diff + Log chain)
- Database-HealthCheck.sql - Comprehensive daily health assessment: database status, file space, blocking, error logs, job failures, DBCC history
- Storage-Monitoring.sql - Tracks database growth trends over time, forecasts capacity thresholds, captures historical snapshots
- Query-Performance-Analysis.sql - Top queries by CPU and I/O, missing index recommendations, wait statistics, unused index detection
- Find-ImplicitConversions.sql - Identifies queries suffering from implicit type conversions causing index scan instead of seek
- Calculate-MAXDOP.sql - Recommends optimal MAXDOP based on NUMA nodes, cores, and logical processors
- Analyze-BufferPoolUsage.sql - Shows which databases and tables consume the most buffer pool memory
- Audit-DatabasePermissions.sql - Complete permission inventory across all databases: users, roles, explicit grants, orphaned users
- Find-OrphanedUsers.sql - Identifies database users without matching server logins, generates remap/drop scripts
- Check-EncryptionStatus.sql - TDE status, certificate inventory and expiry, connection encryption audit, backup encryption
- Monitor-BlockedProcesses.sql - Captures blocking chains exceeding duration threshold with query text and resource details
- Monitor-Deadlocks.sql - Captures deadlock graphs with full XML reports for root cause analysis
- Monitor-LongRunningQueries.sql - Captures queries exceeding configurable duration with execution details
- Validate-BestPractices.sql - Checks instance against best practices (memory, MAXDOP, cost threshold, TempDB, auto-shrink, page verify, backup compression, DAC) with fix scripts
- Generate-ServerDocumentation.sql - Complete instance configuration snapshot: server info, settings, databases, file layout, logins, jobs, linked servers, trace flags
- Create-CustomAlerts.sql - SQL Agent alerts for severity 17-25 errors, error 825 (read-retry), 832 (page checksum), 855 (memory corruption)
- Monitor-TempDBContention.sql - Diagnoses TempDB page latch contention, file sizing imbalance, version store pressure, and top consumers
All scripts are designed to run on SQL Server 2016+ and are safe for production use. Execute directly in SSMS or schedule via SQL Agent.
Scripts that modify server state (Extended Events, alerts, configuration) include safety checks and can be customized via declared variables at the top of each file.
Maintenance scripts log their actions to a DBA_MaintenanceLog table for audit trails and trend analysis.
- SQL Server 2016+
- Tested on databases with 100+ tables and 250+ indexes
- Compatible with SQL Server Express, Standard, and Enterprise editions
MIT