Top 3 Queries Every DBA Should Automate

As a Database Administrator (DBA), automation is key to efficiency, reliability, and proactive monitoring. By automating routine queries, you can save time, reduce human error, and quickly identify potential issues before they escalate.

Here are the top 3 queries every DBA should automate to maintain a healthy database environment:

1. Database Health & Performance Monitoring

Why Automate It?
Performance bottlenecks can slow down applications and frustrate users. Automating this query helps track key metrics like CPU usage, memory pressure, and I/O latency over time.

Sample Query (SQL Server):

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    CAST(COUNT(*) * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeInGB,
    user_seeks, user_scans, user_lookups,
    last_user_seek, last_user_scan, last_user_lookup
FROM sys.dm_db_index_usage_stats
JOIN sys.databases ON sys.dm_db_index_usage_stats.database_id = sys.databases.database_id
GROUP BY database_id, user_seeks, user_scans, user_lookups, last_user_seek, last_user_scan, last_user_lookup
ORDER BY SizeInGB DESC;

Automation Tips:

• Schedule via SQL Agent, PowerShell, or Python.

• Store results in a log table or alert if usage exceeds defined thresholds.

2. Backup Status Verification

Why Automate It?
Backups are critical for disaster recovery. Automating this ensures no backup failures go unnoticed.

Sample Query:

SELECT 
    database_name,
    MAX(CASE WHEN type = 'D' THEN backup_finish_date ELSE NULL END) AS LastFullBackup,
    MAX(CASE WHEN type = 'I' THEN backup_finish_date ELSE NULL END) AS LastDiffBackup,
    MAX(CASE WHEN type = 'L' THEN backup_finish_date ELSE NULL END) AS LastLogBackup
FROM msdb.dbo.backupset
GROUP BY database_name
ORDER BY database_name;

Automation Tips:

• Run daily and flag backups older than expected (e.g., >24 hours).

• Send alerts via email or Slack using SQL Agent notifications.

3. Blocking & Deadlock Detection

Why Automate It?
Blocking and deadlocks can cause application failures. Real-time detection can prevent escalation.

Sample Query:

SELECT 
    blocking.session_id AS BlockingSessionID,
    blocked.session_id AS BlockedSessionID,
    blocking.sql_handle AS BlockingSQLHandle,
    blocked.sql_handle AS BlockedSQLHandle,
    blocking.wait_time AS WaitTimeMS,
    blocking.wait_type AS WaitType
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id
WHERE blocked.blocking_session_id <> 0;

Automation Tips:

• Schedule every 5–10 minutes.

• Store blocking incidents in a report table for weekly review.

• Trigger SMS/email if block time exceeds a threshold (e.g., 30 seconds).

Final Thoughts

Automating these three essential queries helps you:

• Be proactive instead of reactive

• Spot issues before they hit production

• Save time and eliminate repetitive manual checks

What queries do YOU automate?

Share your go-to automation scripts in the comments! Let's learn from each other.