A Daily DBA Checklist You Can Run as One Script

A busy morning is exactly when a check kept in your head disappears. This daily DBA checklist puts six read-only checks into one script and returns exceptions. You get a short investigation list instead of another screen full of healthy rows.

Define What the Daily DBA Checklist Flags

Choose thresholds from your recovery and service requirements. The example allows thirty six hours since a full backup, twenty minutes since a log backup, and five minutes of current lock waiting. Those values are example policy inputs, not universal healthy limits. Change them before scheduling the script. A weekly full backup policy needs a different first threshold.

I begin a morning review with recovery coverage. A quick server is little comfort when the required recovery point is missing. The script assumes a SQL Server instance on Windows with Agent available. Express does not provide Agent. Run from master under an authorized monitoring identity, and test that every section can read its required metadata.

Understand the Backup Exceptions

The first result finds online user databases whose full backup history is missing or stale. For FULL and BULK_LOGGED recovery, it also checks the latest completed log backup. Completed, nondamaged history is the operational signal here. Partial backups are recorded as type P, so the type D filter already leaves them out. Only a restore test establishes that the backup chain and media actually recover your database.

Availability group backups can occur on another replica. This local msdb query does not collect those records. Route the monitoring to the intended source or merge the authorized history first. Exclude databases only through a reviewed policy list, rather than suppressing every read only database. Restoring and offline databases are handled separately by the state check.

Read Job Outcomes Without Losing Step Failures

Agent history records steps and a job summary. Step zero is the overall outcome. The main script reports failed summaries in the last day and the recorded message. A failed step followed by an approved retry can produce a successful summary. Review step level failures separately when that behavior matters to your application.

I look for repeated failures and missing executions, not just the latest red icon. A job that never ran has no failed history row. Schedule compliance requires an expected schedule inventory and last execution comparison. Keep that additional rule specific to your estate. Also monitor Agent availability outside this script, because stopped Agent cannot run its own morning check.

Treat Database State and Errors as Clues

The state result returns every database not ONLINE, including system databases. Investigate the expected maintenance window before taking action. A deliberate restore is different from an unexpected SUSPECT state. The script changes no database state and issues no repair command. That keeps an initial observation separate from the response it eventually needs.

For the error log, the script reads the current log over the last day. It filters lines containing Error: and login failures. This is a useful shortlist, not an exhaustive interpretation of every serious message. SQL Server rotates error logs. Read retained archives as well when the current log starts after your review window. A quiet fragment is not a quiet day.

Check Physical Volume Space and Lock Waiting

Volume free space comes from sys.dm_os_volume_stats for data files. DISTINCT prevents several files on the same volume from printing the same finding repeatedly. The example flags either low percentage space or a low absolute byte reserve. Log only and backup only volumes require additional inventory, because this section specifically follows data volumes.

The blocking result reads current requests waiting on locks. wait_time is the current wait duration in milliseconds, not a history of the entire blocking incident. A sleeping blocker can hold an open transaction, so the query joins sessions for the blocking identity. Negative blocking identifiers represent special cases and deserve separate diagnosis. This result focuses on identifiable positive session IDs.

Run the Daily DBA Checklist Script

Paste the whole block into one query window. Temporary tables hold only the log excerpt for filtering. The script does not write user data, change configuration, or kill a connection. Each SELECT returns an independently labeled result set. Save collection time with the output so a later reviewer can distinguish a live condition from a historical observation.

USE master;
SET NOCOUNT ON;
DECLARE @now datetime=GETDATE();
DECLARE @since datetime=DATEADD(day,-1,@now);
DECLARE @full_hours int=36,@log_minutes int=20,@blocking_ms int=300000;
SELECT N'Backup coverage' AS CheckName,d.name AS DatabaseName,
       d.recovery_model_desc,b.LastFull,l.LastLog
FROM sys.databases AS d
OUTER APPLY
(
    SELECT MAX(backup_finish_date) AS LastFull
    FROM msdb.dbo.backupset
    WHERE database_name=d.name AND type='D' AND is_damaged=0
) AS b
OUTER APPLY
(
    SELECT MAX(backup_finish_date) AS LastLog
    FROM msdb.dbo.backupset
    WHERE database_name=d.name AND type='L' AND is_damaged=0
) AS l
WHERE d.database_id>4 AND d.state=0 AND d.source_database_id IS NULL
  AND (b.LastFull IS NULL OR b.LastFull<DATEADD(hour,-@full_hours,@now)
       OR (d.recovery_model_desc IN(N'FULL',N'BULK_LOGGED')
           AND (l.LastLog IS NULL OR l.LastLog<DATEADD(minute,-@log_minutes,@now))));
SELECT N'Failed job' AS CheckName,j.name AS JobName,
       msdb.dbo.agent_datetime(h.run_date,h.run_time) AS RunStarted,h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id=h.job_id
WHERE h.step_id=0 AND h.run_status=0 AND h.run_date>0
  AND msdb.dbo.agent_datetime(h.run_date,h.run_time)>=@since;
SELECT N'Database state' AS CheckName,name AS DatabaseName,state_desc
FROM sys.databases WHERE state<>0;
DROP TABLE IF EXISTS #ErrorLog;
CREATE TABLE #ErrorLog
    (LogDate datetime,ProcessInfo nvarchar(50),LogText nvarchar(max));
INSERT #ErrorLog
EXEC master.dbo.xp_readerrorlog 0,1,NULL,NULL,@since,@now,N'asc';
SELECT N'Recent error' AS CheckName,LogDate,ProcessInfo,LogText
FROM #ErrorLog
WHERE LogText LIKE N'%Error:%' OR LogText LIKE N'%Login failed%';
SELECT DISTINCT N'Data volume space' AS CheckName,v.volume_mount_point,
       v.available_bytes/1073741824.0 AS FreeGiB,
       100.0*v.available_bytes/NULLIF(v.total_bytes,0) AS FreePct
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id,f.file_id) AS v
WHERE f.type=0
  AND (v.available_bytes<10.0*1073741824
       OR 100.0*v.available_bytes/NULLIF(v.total_bytes,0)<10);
SELECT N'Long lock wait' AS CheckName,r.session_id,r.blocking_session_id,
       r.wait_type,r.wait_time,s.login_name AS BlockerLogin,
       s.host_name AS BlockerHost,s.open_transaction_count AS BlockerOpenTransactions
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id=r.blocking_session_id
WHERE r.session_id<>@@SPID AND r.blocking_session_id>0
  AND r.wait_type LIKE N'LCK[_]M[_]%' AND r.wait_time>=@blocking_ms;

Do not interpret an exception count as a severity score. One missing log chain deserves more attention than several harmless historical job failures. The result provides names and times for investigation. Add a run identifier when storing it, and keep each result set mapped to its check name in your collector.

Prove the Collector Has Enough Visibility

Recent SQL Server versions split monitoring permissions more finely than older versions. The authorized identity needs access to backup and job history, the error log, and server monitoring views. These permission checks help diagnose partial visibility. An empty result produced by inadequate access is a monitoring failure, not a healthy server.

SELECT ORIGINAL_LOGIN() AS MonitoringLogin,
       HAS_PERMS_BY_NAME(NULL,NULL,N'VIEW SERVER STATE') AS HasServerState,
       HAS_PERMS_BY_NAME(NULL,NULL,N'VIEW SERVER PERFORMANCE STATE') AS HasPerformanceState,
       HAS_PERMS_BY_NAME(NULL,NULL,N'VIEW ANY ERROR LOG') AS HasErrorLogAccess;
SELECT name,state_desc FROM sys.databases WHERE name IN(N'master',N'msdb');

Use a tested least privilege monitoring role or signed module appropriate to the checks. Do not grant sysadmin solely to make the output shorter. Verify a known test finding in a rehearsal environment. A checklist that never complains deserves a small controlled test before it receives everyone's trust.

Give Every Daily DBA Checklist Finding an Owner

Who responds when the script returns a stale backup at breakfast? Assign an owner, a response window, and an escalation path for each category. Keep approved maintenance exceptions time limited. Store the action and evidence with the finding. Otherwise tomorrow's script returns yesterday's problem and the team learns to ignore the result.

I keep the morning list short and the follow up specific. Add checks only when someone will act on them. This daily DBA checklist complements alerting and continuous monitoring. It cannot see a brief overnight outage after the evidence disappears. Its value is a repeatable review that exposes current exceptions and retained failures without relying on memory.

Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and Check Backup Reliability.

A daily checklist is not a health guarantee, it is a repeatable starting point for action.

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