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

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