The Scripts Worth Having on Every Server You Touch

The most useful DBA scripts answer common questions without making hidden changes. Keep a small set you understand, and record the context needed to interpret each result.

What Is Running Right Now

Start with active requests rather than a list of every connected session. Capture the observation time and instance name with the output. Server-level visibility requires the appropriate monitoring permission for your SQL Server version.

SELECT session_id, status, command, blocking_session_id,
       wait_type, wait_time, cpu_time, total_elapsed_time,
       DB_NAME(database_id) AS database_name
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID
ORDER BY total_elapsed_time DESC;

This is a snapshot of active work. A request can finish between samples, and a sleeping session can still own an open transaction. Use repeated observations or retained monitoring when the question is historical.

What Is Blocking That Work

SELECT r.session_id AS waiting_session,
       r.blocking_session_id, r.wait_type, r.wait_time,
       b.status AS blocker_status,
       b.open_transaction_count AS blocker_open_transactions,
       b.login_name AS blocker_login
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.dm_exec_sessions AS b
  ON b.session_id = r.blocking_session_id
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

The join helps include a blocking session even when it has no active request. It is a starting point for following the blocking chain. Special negative blocking identifiers require separate interpretation and are excluded here.

Do not attach automatic KILL commands to this script. Identify transaction ownership, business impact, and the likely rollback cost first. A diagnostic result is evidence for a decision, not permission to terminate work.

Which Tables Occupy the Most Space

SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
       t.name AS table_name,
       SUM(CASE WHEN p.index_id IN (0,1)
                THEN p.row_count ELSE 0 END) AS approximate_rows,
       SUM(p.reserved_page_count) * 8.0 / 1024 AS reserved_mb
FROM sys.tables AS t
JOIN sys.dm_db_partition_stats AS p ON p.object_id = t.object_id
GROUP BY t.schema_id, t.name
ORDER BY reserved_mb DESC;

Run this in the database being reviewed. The row count uses the heap or clustered index to avoid counting every nonclustered index again. Reserved space includes indexes represented in the partition statistics.

These values are useful for prioritization, not an exact business-row reconciliation. Special storage types may need additional views. Compare dated samples when the question concerns growth rather than current size.

Which Indexes Deserve a Usage Review

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name,
       COALESCE(u.user_seeks,0) AS user_seeks,
       COALESCE(u.user_scans,0) AS user_scans,
       COALESCE(u.user_lookups,0) AS user_lookups,
       COALESCE(u.user_updates,0) AS user_updates
FROM sys.indexes AS i
JOIN sys.tables AS t ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u
  ON u.database_id = DB_ID() AND u.object_id = i.object_id
 AND u.index_id = i.index_id
WHERE i.index_id > 0 AND i.is_hypothetical = 0
ORDER BY user_seeks + user_scans + user_lookups, user_updates DESC;

Zero recorded reads do not prove an index is unnecessary. Usage counters have a limited observation window and can reset. An index may also enforce uniqueness or support infrequent but essential work.

Keep the server start time and workload coverage with this output. Review constraints, plans, and a representative business cycle before proposing removal. Never turn this inventory directly into DROP INDEX statements.

What Backup History Is Recorded

SELECT database_name, type,
       MAX(backup_finish_date) AS latest_recorded_finish
FROM msdb.dbo.backupset
GROUP BY database_name, type
ORDER BY database_name, type;

Interpret backup type and compare the latest finish with the database's actual recovery policy. A missing row may reflect purged history or another backup location. Also compare against the current database inventory to find databases absent from this result.

Successful history is not proof that a restore meets the recovery objective. Keep separate restore-test evidence and the required backup chain. Include encryption keys and certificates when they are part of recovery.

Which Jobs Reported Failure

SELECT TOP (30) j.name AS job_name,
       h.run_date, h.run_time, h.run_duration, 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
ORDER BY h.instance_id DESC;

Step zero records the overall job outcome in this history view. These are historical failures, so check whether a later run succeeded. Jobs that never started need a separate expected-completion check.

Keep each script's permissions, scope, and limitations in a short header. Review the collection after platform changes and test it in a suitable lab. Six understood questions are more useful than hundreds of unexplained commands.

A script collection is not a substitute for diagnosis, it is a dependable starting point for the next question.

This post was rewritten from scratch in September 2026. The original, published on 2009-05-20, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

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