Standard DBA Reports You Can Write in T-SQL

The weekly DBA review should begin with facts, not five disconnected consoles. Standard DBA reports can bring backup, growth, job, login, and error checks into one repeatable routine.

Keep Standard DBA Reports Short and Actionable

Standard DBA reports should answer what changed and what needs attention. Start with databases in scope, backup recency, size trend, failed jobs, login changes, and notable errors. Add a link to deeper evidence in your internal report system if needed, but keep the first view readable.

I decide thresholds with the recovery and operating requirements. A backup age is concerning only relative to the agreed schedule. A database that grew is not automatically unhealthy. The report should show facts and flag deviations from the local baseline.

Ask who will act on each flagged row. If nobody owns it, the report is a collection of interesting numbers. A good weekly review makes the next action clear without inventing a universal threshold for every server.

Show the Latest Backup by Type

msdb stores backup history for backups recorded by SQL Server on that instance. Query full, differential, and log backups separately. Include completion time and database name. Confirm the backup job’s output and restore testing too. History alone does not prove that a backup file is available or restorable.

I review databases that appear in sys.databases but have no expected backup history. System databases and intentionally excluded databases need documented rules. Do not let an INNER JOIN silently hide a database with no matching backup row.

A backup report should use local recovery requirements. A full backup from last week can be fine with daily differentials and frequent logs, or inadequate under another plan. The report’s job is to expose the schedule and any gap.

SELECT d.name AS database_name, b.type AS backup_type,
       MAX(b.backup_finish_date) AS last_finish
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
  ON b.database_name = d.name
GROUP BY d.name, b.type
ORDER BY d.name, b.type;

Track Data and Log Size

Use sys.master_files to show current file sizes and growth settings. Compare saved snapshots week to week to spot unexpected growth. A single size reading cannot tell you whether a file grew yesterday or has been stable for years. Collect the baseline before calling a number unusual.

Separate data and log files. A growing log can indicate a long transaction, missed log backups, or a workload change. A growing data file can reflect normal business activity. Each needs its own investigation. Avoid shrinking as a routine response to a size increase.

I keep both allocated size and free disk capacity in the broader review. SQL Server knows its file size, but the host or storage service knows how much room remains. The two views belong together when planning capacity.

SELECT DB_NAME(database_id) AS database_name,
       type_desc, SUM(size) * 8.0 / 1024 AS allocated_mb
FROM sys.master_files
GROUP BY database_id, type_desc
ORDER BY database_name, type_desc;

List Job Failures in Standard DBA Reports

SQL Agent history can show failed executions, but a job with no recent run also deserves attention. Report enabled jobs, last outcome, and expected schedule. A failed step followed by a successful retry should be visible as a recovered failure, not erased from the week.

I look at the message and step name before restarting anything. A source file missing, deadlock, and permission change call for different fixes. The same generic failed status is the start of an investigation, not a diagnosis.

Keep the report’s time window explicit and use server time consistently. An overnight job can cross the calendar boundary. Build the report around execution times rather than a vague “yesterday” label.

SELECT j.name AS job_name, h.run_status, h.step_id,
       h.run_date, h.run_time, h.message
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobhistory AS h
  ON h.job_id = j.job_id
WHERE h.run_status = 0
  AND h.step_id = 0
ORDER BY h.run_date DESC, h.run_time DESC;

Review Login and Permission Changes

A login inventory should show new, disabled, and unexpectedly privileged logins. sys.server_principals gives the current state, while SQL Server Audit or another approved change log is needed for reliable history. A current snapshot alone cannot tell you who made a change.

Do not include passwords or secrets in a report. Restrict the output to people who need security visibility. Compare snapshots by SID and name carefully, because names can change. Document service accounts and planned access before calling every unfamiliar login a problem.

I ask whether the review includes database users and roles as well as server logins. Server access is only part of the permission story. Start with the highest risk paths, then expand the review where the environment needs it.

SELECT name, type_desc, is_disabled, create_date, modify_date
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')
ORDER BY name;

Include Errors Without Flooding the Page

Use the SQL Server error log and application logs to identify recurring failures, severity, and time. A weekly report can summarize counts by message pattern and provide a short sample. Do not paste every informational message into the review. Noise makes the important entry harder to see.

I compare error times with job and load run IDs. A connection failure at the same time as a load failure is more useful than either fact alone. If the report includes a severe error, link it to the incident record and the action taken in your internal workflow.

Error log retention matters. If logs cycle before the weekly report runs, collect the needed events earlier. The absence of an error in a short retained log does not prove the week was clean.

EXEC sys.xp_readerrorlog 0, 1, N'Error';

Save Standard DBA Reports and Review Trends

Store each weekly output with collection time and server identifier. That makes size growth and permission changes comparable. Keep the queries versioned and note when definitions change. A trend built from changing queries can mislead even when each week’s data is correct.

Review standard DBA reports with the owners of backup, jobs, security, and capacity. Some environments put all those duties on one DBA, but ownership should still be explicit. Close each flagged item or carry it forward with a reason.

A small report that people read beats a large one that nobody opens. Start with five questions, validate each query on your own instance, and add a measure only when it changes an action. The weekly habit is the real monitoring system.

Which flagged row in this week’s review has an owner and a concrete next action?

Save each weekly report with the server name and collection time. If a server is replaced or renamed, that context prevents two histories from being joined by accident. The report should be reproducible from its source queries.

Related reading on this blog: How to Know Backup History of Current Database? and FIX: Error: The Job Failed. Unable to Determine If The Owner Domain\User of Job Job_Name Has Server Access.

A DBA report is not a pile of server facts, it is a short list of facts that lead to action.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.