Automating SQL Server Database Administration with T-SQL Utility Scripts
Database performance doesn't degrade overnight — it erodes quietly through fragmented indexes, bloated data files, and unchecked log growth. By the time your queries slow to a crawl, the damage is already done.
To address this proactively, I built and open-sourced sql-database-admin-utility-scripts: a focused collection of production-grade T-SQL scripts that automate the most critical — and most neglected — database administration tasks in SQL Server environments.
👉 View the Repository on GitHub
The Problem: DBA Tasks That Fall Through the Cracks
In enterprise environments, database administrators and developers are often juggling application delivery alongside infrastructure health. Routine but essential maintenance tasks — index management, file size optimization, fragmentation analysis — are frequently deferred until performance incidents occur.
The consequences are real:
- Fragmented indexes degrade query execution plans, increasing I/O and CPU overhead
- Oversized data and log files consume storage unnecessarily, raising cloud infrastructure costs
- Undetected index fragmentation in columnstore indexes silently undermines analytical query performance
What's needed isn't just documentation of best practices — it's executable, reusable tooling that makes doing the right thing the easy thing.
What the Scripts Do
The repository provides four focused T-SQL utility scripts, each targeting a distinct operational concern:
🗜️ 1. Shrink Data File and Log Files
Log files in SQL Server can grow unbounded if not managed. This script automates the safe shrinking of both data (.mdf) and log (.ldf) files — a task that, when done manually and ad hoc, frequently introduces risk through incorrect syntax or improper sequencing.
-- Example: Shrink log file to reclaim space
DBCC SHRINKFILE (DatabaseLog, 1);
⚠️ Note: Shrinking is applied judiciously in this toolkit — targeted for log files post-backup or after bulk operations, not as routine maintenance that could cause page fragmentation.
🔄 2. Reorganize an Index
Index reorganization is an online, low-impact operation suited for indexes with moderate fragmentation (typically 10–30%). Unlike a full rebuild, it doesn't lock the table — making it safe to run against production workloads during business hours.
ALTER INDEX [IndexName] ON [Schema].[TableName] REORGANIZE;
This script makes reorganization scriptable and schedulable — ready to plug into SQL Server Agent jobs or CI/CD database pipelines.
🔨 3. Rebuild an Index
For heavily fragmented indexes (>30%), a full rebuild is necessary. This script automates index rebuilds with options for ONLINE mode where supported, preserving availability during maintenance windows.
ALTER INDEX [IndexName] ON [Schema].[TableName]
REBUILD WITH (ONLINE = ON);
Rebuilding updates index statistics as a side effect — directly improving query optimizer decisions across the database.
🔍 4. Check Rowstore & Columnstore Index Fragmentation
This is arguably the most analytically valuable script in the collection. Using sys.dm_db_index_physical_stats, it surfaces fragmentation metrics for both rowstore (traditional B-tree) and columnstore (analytical) indexes — giving DBAs and engineers a clear, data-driven basis for deciding between reorganize and rebuild operations.
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;
Columnstore index support is a deliberate inclusion — most open-source DBA toolkits focus exclusively on rowstore, leaving data warehouse and hybrid OLTP/OLAP environments underserved.
Why Open Source?
Database administration knowledge is often siloed — locked in internal runbooks, tribal knowledge, or expensive vendor tooling. By releasing these scripts publicly under an open-source model, the goal is to:
- Lower the barrier for developers who maintain databases without dedicated DBA support
- Standardize common maintenance operations across teams and organizations
- Provide a reference implementation for engineers learning SQL Server internals
The scripts are written to be readable and educational — not just functional. Every operation is intentional and can be understood, adapted, and extended by the community.
Practical Use Cases
| Scenario | Recommended Script |
|---|---|
| Scheduled weekly maintenance job | Fragmentation Check → Reorganize or Rebuild |
| Post-bulk-insert cleanup | Shrink Log + Rebuild Index |
| Performance incident investigation | Fragmentation Check (Rowstore + Columnstore) |
| Storage cost optimization | Shrink Data File |
| Pre-migration health check | Full fragmentation report |
Who This Is For
- Backend developers managing their own SQL Server databases
- Data engineers working with hybrid OLTP/OLAP workloads
- DBAs looking for scriptable, version-controlled maintenance tooling
- DevOps engineers integrating database health checks into CI/CD pipelines
What's Next
Planned additions to the repository include:
- 📊 Automated maintenance decision logic (reorganize vs. rebuild threshold evaluation)
- 🕐 SQL Server Agent job templates for scheduling
- 📁 Statistics update scripts
- 🔔 Alerting queries for critical fragmentation thresholds
Get Involved
The repository is open for contributions. Whether you want to add scripts, improve documentation, or raise issues — all input is welcome.
👉 sql-database-admin-utility-scripts on GitHub
If this toolkit has saved you time or helped your team, leave a ⭐ on the repo — it helps others discover it.
What T-SQL maintenance scripts do you rely on that aren't widely shared? Let's build a better open-source DBA toolkit together. Drop your thoughts in the comments 👇
#sql #sqlserver #database #dba #tsql #opensource #devops #dataengineering #backend #performance
Top comments (0)