DEV Community: Dominic Robinson

Automating SQL Server Database Administration with T-SQL Utility Scripts

Dominic Robinson — Wed, 06 May 2026 18:17:53 +0000

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

[Boost]

Dominic Robinson — Wed, 06 May 2026 18:06:50 +0000

Dominic Robinson

May 6

Stop Manually Wiring Azure AD Auth — Here's a Secure-by-Default .NET 8 Template

#showdev #azure #dotnet #security

Comments

2 min read

Stop Manually Wiring Azure AD Auth — Here's a Secure-by-Default .NET 8 Template

Dominic Robinson — Wed, 06 May 2026 18:04:15 +0000

If you've ever spent days configuring OAuth 2.0 and OIDC just to get enterprise authentication working, you're not alone. It's tedious, error-prone, and honestly — it shouldn't be this hard.

That's why I built and open-sourced AzureAdRazorLogin: a ready-to-deploy C# .NET 8 Razor Pages template that handles Azure Active Directory integration out of the box.

The Problem Worth Solving

In distributed, cloud-native architectures, identity is the new perimeter. Yet most teams still hand-roll their authentication setup — misconfiguring redirect URIs, mishandling token validation, or unknowingly introducing security drift across environments.

The result? Technical debt baked into your security layer before you've shipped a single feature.

What AzureAdRazorLogin Does Differently

Rather than following a tutorial and stitching together middleware, this solution gives you a secure baseline from line one.

🔐 Zero-Trust by Default

The app binds natively to Microsoft Entra ID (Azure AD) and enforces a global authorization fallback — every endpoint requires authentication unless explicitly exempted. No accidental public routes.

🔄 Full OIDC Lifecycle Handled

Token acquisition, encrypted cookie session persistence, and centralized logout — including terminating the session at the Azure AD identity provider — are all wired up and working on first run.

⚙️ Environment-Agnostic Config

Tenant IDs and Client IDs are abstracted via structured appsettings.json templates, making transitions between local dev, staging, and production seamless and predictable.

Why This Matters for Engineering Teams

Without This	With AzureAdRazorLogin
Days of OIDC configuration	Deployed in minutes
Inconsistent security posture	Deterministic, standards-aligned baseline
Manual compliance checks	Microsoft-recommended security posture built-in
Custom boilerplate per project	Reusable, versioned open-source artifact

The template is also CI/CD-ready and containerization-friendly — drop it into Azure App Services or AWS without any additional scaffolding.

Built for the Long Haul

This isn't just a snippet — it's governed like a real open-source project:

📄 MIT License
🤝 Contributor Covenant
🔀 Defined pull-request guidelines

The goal is a living, community-maintained security baseline that evolves with the .NET ecosystem.

Get Started

👉 Check out the full solution and docs: AzureAdRazorLogin on GitHub

If you've been burned by OIDC misconfigurations before, this is for you. Clone it, use it, contribute to it.

How are you handling identity standardization across your .NET microservices? Drop your approach in the comments 👇

#dotnet #azure #security #opensource #webdev #csharp #zerotrust