DEV Community: Georg Piwonka

Enterprise SQL Server Index Maintenance: Multi-Database Automation with Email Reporting

Georg Piwonka — Sun, 22 Jun 2025 20:14:51 +0000

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) = 'dba@company.com;admin@company.com';
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) = 'dba-team@company.com;infrastructure@company.com;manager@company.com';



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

From WordPress to ASP.NET Core: A Development Adventure during Le Mans

Georg Piwonka — Sun, 15 Jun 2025 18:33:09 +0000

It's time for the 24 Hours of Le Mans and the Formula 1 Canadian Grand Prix are here. For a motorsport heart, there's hardly anything better.

The Idea: Breaking Free from WordPress

I've been carrying the idea for a while to liberate my website from WordPress and instead create a simple, self-developed solution. This would save me all the overhead. As a C# developer, PHP and Python are rather off-putting to me – admittedly a very personal opinion. Anyone who has programmed in these languages for years probably finds C# just as terrible.

To turn the more or less sensible "driving around in circles stupidly" (as Niki Lauda once put it) into something constructive, I thought: Why not build something in ASP.NET Core and C# during Le Mans? I had started some time ago but didn't get further due to time constraints.

The Requirements

My ideas were deliberately kept simple:

A simple blog with categories, but without comment functionality
A few static pages – basically a small CMS
An admin area for content entry
As a challenge: multilingual support
Everything as simple as possible
Claude as Development Partner
While experimenting, I noticed that Claude can generate pretty good code and often helps more effectively with problems than a Google or Bing search. So why not get Claude to help?

When Le Mans started, I began with a naive prompt: "I want to build my website with .NET Core Razor. I want a very simple design and a small backend for my posts. For the backend, I want MSSQL with Entity Framework. Can you help me with this?"

And off we went. Claude produced code and I could watch Le Mans in parallel. 😊

First Successes

The generated code was comprehensive and really well-suited for a start. I created a Visual Studio project, copied the code in – and everything worked. It doesn't get faster than this: I had a site with blog functionality and a corresponding admin area.

Since I wasn't precise enough, I still had to adjust the namespaces. This is essential, by the way: The more specific requirements, the better the code becomes.

Interesting Observations

From here it got really interesting. The longer the chat lasted, the more inconsistent the code became and the more variable the patterns that Claude applied. Sometimes with ViewModels, sometimes without, sometimes services, sometimes static classes. I found this fascinating since I had actually expected consistent code.

But it wasn't a big problem – until after a few hours the maximum chat length was reached.

The Challenge: Chat Limitations

This is where it got complicated. Unfortunately, Claude can't build on previous chats, but knowledge of already created code would have been advantageous.

Claude Projects sounded like a solution, but neither chat publishing nor the GitHub reference worked for Claude. So I added the entire source code file by file to the project knowledge. In retrospect, it would have been better to start with a project right away – but this way I could watch more Le Mans...

Development Loops and Misunderstandings

Further development went quite smoothly, except for some loops where Claude and I didn't understand each other. There were quite amusing misunderstandings: When I wanted to simplify the language model and handling, Claude made an entire translation system out of it.

Sometimes he was also stubborn and absolutely refused to rebuild certain code parts.

Recurring Errors
It's interesting that certain errors persist stubbornly. Claude likes to use inline styles, which is basically okay. However, with @media and @keyframes, he occasionally forgets the first or second @ because these need to be escaped in Razor pages.

It proved particularly difficult to undo certain design decisions by Claude. But in the end, everything worked out well.

Code Review with an AI Partner

At times it felt like a long code review session. I left most of the code as Claude had produced it. However, in hindsight, the code still needs to be reworked with regard to performance and standardization.

Conclusion

It was an entertaining experiment – especially when Claude gets fixated on topics and absolutely refuses to listen to his "master." This reminds me of some of my juniors.

And Le Mans? That was also very exciting: no long safety car phases and everyone always pushing. Robert Kubica, Yifei Ye, and Phil Hanson won – very impressively. The Austrian Richard Lietz triumphed together with Ryan Hardwick (USA) and Riccardo Pera (ITA) in a Manthey Porsche in the LMGT3 category.

A perfect weekend for motorsport and code!

Site: piwonka.cc

Site: repository

Building SmartReceiptOrganizer: An AI-Powered Email Receipt Processing System

Georg Piwonka — Wed, 04 Jun 2025 21:18:19 +0000

This is a submission for the Postmark Challenge: Inbox Innovators.

What I Built

I created SmartReceiptOrganizer - an automated receipt processing system that turns your email inbox into a smart financial database. The system receives receipt emails via Postmark's inbound webhooks, automatically detects PDF attachments, and uses AI to extract key financial data like amounts, currencies, dates, and merchant names.

Think of it as your personal AI accountant that never sleeps! 🤖💰

Key Features:

📧 Automatic Email Processing: Receives receipts via Postmark webhooks
🔍 Smart PDF Detection: Handles various content types (even when PDFs are sent as application/octet-stream)
🤖 AI Data Extraction: Uses Mindee AI to extract amounts, currencies, dates, and merchants
💾 Intelligent Storage: Saves everything in a searchable database
🛡️ Robust Error Handling: Bulletproof DateTime parsing and duplicate detection
📊 REST API: Full CRUD operations and statistics endpoints

Demo

Live API Endpoints:

GET http://postmandevto.runasp.net/api/receipts - View all processed receipts
GET http://postmandevto.runasp.net/api/receipts/stats - Financial statistics
GET http://postmandevto.runasp.net/api/mindee-debug/config - System health check

How to Test:

Send an email with a PDF receipt to any of these addresses:
- devto_postmark_invoice@piwonka.cc (special address for dev.to community testing!)
Check the processed data via the API endpoints above
The system automatically extracts financial data from the PDF and responds within seconds

Sample API Response:

{
  "success": true,
  "receiptsCount": 5,
  "receipts": [
    {
      "id": 3,
      "merchant": "MTEL",
      "amount": 29.99,
      "currency": "EUR",
      "transactionDate": "2025-06-01T00:00:00Z",
      "originalEmailSubject": "WG: MTEL Monatsrechnung",
      "attachmentCount": 1,
      "hasMindeeData": true
    }
  ]
}

Code Repository

The complete source code is available on GitHub: SmartReceiptOrganizer

Key files to explore:

Controllers/PostmarkReceiptController.cs - Main webhook handler
Models/Postmark/PostmarkInboundMessage.cs - Postmark integration models
Controllers/MindeeDebugController.cs - AI debugging tools

How I Built It

Tech Stack

Backend: ASP.NET Core 8 (C#)
Database: SQL Server with Entity Framework Core
Email Processing: Postmark Inbound Webhooks
AI: Mindee Receipt API for data extraction
Hosting: monsterasp.net (Free Tier)

The Journey

Challenge #1: Postmark DateTime Formats 🕒
Postmark sends dates in RFC 2822 format ("Tue, 3 Jun 2025 21:46:05 +0000"), but .NET's JsonSerializer expects ISO 8601. My solution was elegantly simple:

[JsonPropertyName("Date")]
public string? DateString { get; set; } // Parse as string first!

public void ParseDate()
{
    // Multiple format parsing with fallbacks
    var formats = new[]
    {
        "ddd, d MMM yyyy HH:mm:ss zzz",
        "ddd, dd MMM yyyy HH:mm:ss zzz",
        // ... more formats
    };
    // Robust parsing logic
}

Challenge #2: PDF Content Type Detection 📄
Discovered that Postmark sometimes sends PDFs as application/octet-stream instead of application/pdf. Built smart detection:

private static bool IsPdfAttachment(PostmarkAttachment attachment)
{
    // Check ContentType
    if (attachment.ContentType?.Contains("pdf", StringComparison.OrdinalIgnoreCase) == true)
        return true;

    // Fallback: check filename extension
    if (!string.IsNullOrEmpty(attachment.Name) && 
        attachment.Name.ToLowerInvariant().EndsWith(".pdf"))
        return true;

    return false;
}

Challenge #3: AI Integration 🤖
Integrating Mindee's Receipt API was surprisingly smooth. The trickiest part was handling the multipart form data properly:

using var formContent = new MultipartFormDataContent();
var fileContent = new ByteArrayContent(Convert.FromBase64String(attachment.Content));
fileContent.Headers.ContentType = new MediaTypeHeaderValue("application/pdf");
formContent.Add(fileContent, "document", attachment.Name ?? "receipt.pdf");

_httpClient.DefaultRequestHeaders.Add("Authorization", $"Token {mindeeApiKey}");
var response = await _httpClient.PostAsync(url, formContent);

Challenge #4: Free Hosting Constraints 💰
Running on free tier means no HTTPS, but Postmark's flexible webhook configuration made this a non-issue. The HTTP endpoint works perfectly for webhook delivery!

Postmark Experience

Working with Postmark was a fantastic experience! The inbound webhook system is incredibly reliable and well-documented. What impressed me most:

Flexible Webhook Configuration: Works seamlessly with HTTP endpoints (perfect for free hosting!)
Consistent JSON Structure: The webhook payload is always predictable
Rich Attachment Support: Base64 encoding makes file handling straightforward
Excellent Documentation: Clear examples and troubleshooting guides
Reliable Delivery: Never missed a webhook during development
Multiple Email Address Support: Easy to set up multiple inbound addresses for different use cases

The only "gotcha" was the DateTime format difference, but that's more about different standards than a Postmark issue.

Architecture Highlights

Idempotent Processing: Duplicate detection prevents reprocessing the same email
Graceful Degradation: System works even if AI extraction fails
Comprehensive Logging: Detailed request tracking for debugging
RESTful API: Clean endpoints for integration with other systems
Multi-Address Support: devto_postmark_invoice@piwonka.cc are processed

Real-World Testing

The system is actively processing receipts! Here's what happens when you send a PDF receipt:

Email arrives at either address
Postmark webhook fires within seconds
PDF is extracted and saved to database
Mindee AI analyzes the receipt content
Structured data (amount, merchant, date) is extracted
Everything is stored and immediately queryable via API

This project showcases how Postmark's reliable email infrastructure combined with modern AI services can create powerful automation solutions. It's been an absolute joy to build!

The system is now processing real receipts and saving me hours of manual data entry.

Try it yourself by sending a PDF receipt to devto_postmark_invoice@piwonka.cc and watch the magic happen!

Built with ❤️ using Postmark, ASP.NET Core, and a healthy dose of debugging coffee ☕