sql server hub / production readiness guide

SQL Server production readiness guide

Production readiness means the SQL Server can be operated when backups, failover, changes, alerts, and capacity all matter at the same time.

Use this guide before launch, audit, handover, migration, upgrade, or a bigger production workload. For a wider review, start with a SQL Server health audit.

backupsmonitoringaccessHA/DRrollback

Guide

Operational guide~18 min readUpdated 23 May 2026

LinkedIn X Email

In this guide

1. What a SQL Server production readiness review should prove
2. SQL Server production readiness baseline
3. SQL Server backup and restore readiness
4. SQL Server job and maintenance readiness
5. SQL Server monitoring and alert readiness
6. SQL Server access and ownership readiness
7. SQL Server HA, DR, and failover readiness
8. SQL Server deployment, patch, and rollback readiness
9. SQL Server capacity and workload readiness
10. SQL Server production readiness scorecard
11. Common SQL Server production readiness mistakes
12. When a SQL Server production readiness review helps

First checks

Check whether backups and restores are proven, not just scheduled.

Confirm jobs, alerts, access, and HA/DR have real owners.

Review rollback rules before the next launch, patch, migration, or config change.

Find capacity and workload pressure before the system carries more production load.

1 / Output

What a SQL Server production readiness review should prove

A readiness review should say whether the SQL Server is ready, ready with fixes, not ready, or still unknown because proof is missing. Quiet production is not enough.

The review should name what would fail first during an outage, deployment, patch, restore, failover, or growth spike, then turn that into a fix order.

Area	What it proves	Caution
Backups	Recent full, differential, and log backups match the recovery model.	Does not prove restore success.
Restore history	Recent restore tests or test runs prove the path and timing.	No restore history usually means recovery is still theoretical.
Jobs	Critical SQL Agent jobs succeed, finish on time, and alert when they fail.	Green final status can hide retried or failed steps.
Monitoring	Failures reach someone who can act early enough.	A dashboard is not readiness if nobody watches the right signals.
Access	Privileged access, job owners, and disabled logins are understood.	Do not change access blindly during readiness review.
HA/DR	Replica health, sync state, failover path, and restore path are clear.	HA does not replace backups or restore testing.
Rollback	Patch, deploy, migration, and config rollback rules are written down.	A rollback plan must include stop points and validation.
Capacity	Waits, active workload, file latency, file growth, and tempdb have headroom.	Current availability is not the same as production headroom.

2 / Baseline

SQL Server production readiness baseline

Start with version, uptime, edition, CPU, memory, virtualization, database state, recovery model, compatibility level, and Query Store state. Uptime matters because many counters and waits reset after restart.

Reads the current SQL Server build, uptime, CPU, memory, and virtualization context.

SQL

Instance build, uptime, CPU, and memory

T-SQL · 14 lines

SELECT    @@SERVERNAME AS server_name,    SERVERPROPERTY(N'Edition') AS edition,    SERVERPROPERTY(N'ProductVersion') AS product_version,    SERVERPROPERTY(N'ProductLevel') AS product_level,    SERVERPROPERTY(N'ProductUpdateLevel') AS product_update_level,    SERVERPROPERTY(N'ProductUpdateReference') AS product_update_reference,    si.sqlserver_start_time,    DATEDIFF(hour, si.sqlserver_start_time, SYSDATETIME()) AS uptime_hours,    si.cpu_count,    si.scheduler_count,    si.physical_memory_kb / 1024 AS physical_memory_mb,    si.virtual_machine_type_descFROM sys.dm_os_sys_info AS si;

Review before runningT-SQLUTF-814 lines

Recent uptime limits how much history waits and runtime counters can show. Build and update level matter before patching, upgrading, or opening a vendor case. CPU and memory shape help interpret capacity readiness. This is context, not a readiness decision by itself.

Lists user database state, compatibility level, recovery model, and Query Store flag.

SQL

Database readiness baseline

T-SQL · 12 lines

SELECT    name AS database_name,    state_desc,    compatibility_level,    recovery_model_desc,    is_read_only,    is_auto_close_on,    is_auto_shrink_on,    is_query_store_onFROM sys.databasesWHERE name NOT IN (N'master', N'model', N'msdb', N'tempdb')ORDER BY name;

Review before runningT-SQLUTF-812 lines

Unexpected database states need review before launch or change. Recovery model must match backup and restore expectations. Auto close and auto shrink are warning flags on most production SQL Servers. Query Store helps validate workload behavior after changes.

Shows Query Store actual state and storage usage for user databases.

SQL

Query Store state

T-SQL · 13 lines

SELECT    d.name AS database_name,    d.is_query_store_on,    qso.actual_state_desc,    qso.desired_state_desc,    qso.readonly_reason,    qso.current_storage_size_mb,    qso.max_storage_size_mbFROM sys.databases AS dLEFT JOIN sys.database_query_store_options AS qso    ON qso.database_id = d.database_idWHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')ORDER BY d.name;

Review before runningT-SQLUTF-813 lines

Actual state can differ from desired state. A full or read-only Query Store may not capture validation data. No Query Store means you need another way to compare workload behavior. Do not change Query Store settings blindly during a production incident.

3 / Backup and restore

SQL Server backup and restore readiness

Backups are not production readiness unless restore path, timing, and dependencies are known. Use the backup guide and recovery guide when this is the weakest area.

Shows latest backup by type plus restore history recorded in msdb.

SQL

Backup and restore history

T-SQL · 43 lines

WITH LastBackups AS (    SELECT        bs.database_name,        bs.type,        bs.backup_start_date,        bs.backup_finish_date,        bs.backup_size,        bs.compressed_backup_size,        ROW_NUMBER() OVER (            PARTITION BY bs.database_name, bs.type            ORDER BY bs.backup_finish_date DESC        ) AS rn    FROM msdb.dbo.backupset AS bs    WHERE bs.backup_finish_date >= DATEADD(day, -60, GETDATE()))SELECT    d.name AS database_name,    d.recovery_model_desc,    CASE lb.type        WHEN 'D' THEN 'Full'        WHEN 'I' THEN 'Differential'        WHEN 'L' THEN 'Log'        ELSE lb.type    END AS backup_type,    lb.backup_finish_date,    DATEDIFF(hour, lb.backup_finish_date, GETDATE()) AS hours_since_backup,    DATEDIFF(minute, lb.backup_start_date, lb.backup_finish_date) AS duration_minutes,    CONVERT(decimal(18,2), lb.backup_size / 1048576.0) AS backup_size_mb,    CONVERT(decimal(18,2), lb.compressed_backup_size / 1048576.0) AS compressed_size_mbFROM sys.databases AS dLEFT JOIN LastBackups AS lb    ON lb.database_name = d.name   AND lb.rn = 1WHERE d.name <> N'tempdb'ORDER BY d.name, backup_type; SELECT    destination_database_name,    MAX(restore_date) AS last_restore_date,    COUNT(*) AS restore_recordsFROM msdb.dbo.restorehistoryGROUP BY destination_database_nameORDER BY last_restore_date DESC;

Review before runningT-SQLUTF-843 lines

Backup history proves SQL Server recorded backups, not that files are restorable. Restore history is strongest when it reflects recent controlled tests. Backup duration affects maintenance windows and rollback planning. Compressed size changes can reveal workload or data-growth changes.

Flags missing full backups, missing log backups, and older full backups.

SQL

Backup gap readiness review

T-SQL · 23 lines

SELECT    d.name AS database_name,    d.recovery_model_desc,    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup,    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff_backup,    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup,    CASE        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL            THEN 'No full backup found'        WHEN d.recovery_model_desc IN ('FULL', 'BULK_LOGGED')             AND MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL            THEN 'Full/Bulk-logged with no log backup found'        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) < DATEADD(day, -7, GETDATE())            THEN 'Full backup older than 7 days'        ELSE 'Review against RPO'    END AS review_noteFROM sys.databases AS dLEFT JOIN msdb.dbo.backupset AS bs    ON bs.database_name = d.name   AND bs.backup_finish_date >= DATEADD(day, -60, GETDATE())WHERE d.name <> N'tempdb'GROUP BY d.name, d.recovery_model_descORDER BY d.name;

Review before runningT-SQLUTF-823 lines

Thresholds are starting points, not universal policy. Full or bulk-logged recovery without log backups is usually a serious readiness problem. Compare backup age to RPO and restore expectations. Use recovery readiness when restore/failover confidence is the main risk.

4 / Jobs and maintenance

SQL Server job and maintenance readiness

Production readiness fails when critical jobs fail quietly, run too long, depend on old owners, or hide the real maintenance work. Use the maintenance plan guide for a deeper job review.

Shows disabled jobs plus recent failed, retried, canceled, or long-running SQL Agent job steps.

SQL

Failed, long, disabled, and owner-risk jobs

T-SQL · 27 lines

SELECT TOP (150)    j.name AS job_name,    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,    SUSER_SNAME(j.owner_sid) AS job_owner,    h.step_id,    h.step_name,    CASE h.run_status        WHEN 0 THEN 'Failed'        WHEN 1 THEN 'Succeeded'        WHEN 2 THEN 'Retry'        WHEN 3 THEN 'Canceled'        WHEN 4 THEN 'In progress'        ELSE CONVERT(varchar(20), h.run_status)    END AS run_status,    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_start_time,    ((h.run_duration / 10000) * 3600)      + (((h.run_duration % 10000) / 100) * 60)      + (h.run_duration % 100) AS duration_seconds,    h.messageFROM msdb.dbo.sysjobs AS jLEFT JOIN msdb.dbo.sysjobhistory AS h    ON h.job_id = j.job_id   AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -30, GETDATE()), 112))WHERE j.enabled = 0   OR h.run_status <> 1   OR h.run_duration >= 3000ORDER BY job_status, run_start_time DESC, duration_seconds DESC;

Review before runningT-SQLUTF-827 lines

Disabled jobs should be intentional and documented. A final success can hide failed or retried job steps. Job owners can break after account changes. Long jobs can collide with backup, maintenance, or business windows.

Finds job steps that appear to run CHECKDB, backups, index/statistics work, or cleanup.

SQL

Maintenance command readiness

T-SQL · 30 lines

SELECT    j.name AS job_name,    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,    SUSER_SNAME(j.owner_sid) AS job_owner,    s.step_id,    s.step_name,    CASE        WHEN s.command LIKE '%DBCC CHECKDB%' THEN 'CHECKDB'        WHEN s.command LIKE '%BACKUP DATABASE%' THEN 'Database backup'        WHEN s.command LIKE '%BACKUP LOG%' THEN 'Log backup'        WHEN s.command LIKE '%ALTER INDEX%' THEN 'Index maintenance'        WHEN s.command LIKE '%UPDATE STATISTICS%' THEN 'Statistics update'        WHEN s.command LIKE '%sp_delete_backuphistory%' THEN 'Backup history cleanup'        WHEN s.command LIKE '%sp_purge_jobhistory%' THEN 'Job history cleanup'        WHEN s.command LIKE '%xp_delete_file%' THEN 'File cleanup'        ELSE 'Other possible maintenance'    END AS maintenance_type,    LEFT(REPLACE(REPLACE(s.command, CHAR(13), ' '), CHAR(10), ' '), 700) AS command_previewFROM msdb.dbo.sysjobs AS jJOIN msdb.dbo.sysjobsteps AS s    ON s.job_id = j.job_idWHERE s.command LIKE '%DBCC CHECKDB%'   OR s.command LIKE '%BACKUP DATABASE%'   OR s.command LIKE '%BACKUP LOG%'   OR s.command LIKE '%ALTER INDEX%'   OR s.command LIKE '%UPDATE STATISTICS%'   OR s.command LIKE '%sp_delete_backuphistory%'   OR s.command LIKE '%sp_purge_jobhistory%'   OR s.command LIKE '%xp_delete_file%'ORDER BY j.name, s.step_id;

Review before runningT-SQLUTF-830 lines

This helps reveal maintenance hidden behind generic job names. Command previews are for review only; do not edit jobs from this output alone. CHECKDB, backup, index, statistics, and cleanup work need separate review. No rows may mean the logic is in external scripts or stored procedures.

5 / Monitoring

SQL Server monitoring and alert readiness

Monitoring is ready when failures reach someone who can act. SQL Agent failures, backup failures, blocking, file growth, tempdb pressure, and error log patterns should not depend on someone manually looking later.

Use the SQL Server monitoring guide when alert coverage is unclear.

Shows job notification settings, email operators, and Database Mail profile/account setup.

SQL

SQL Agent notification and Database Mail readiness

T-SQL · 25 lines

SELECT    j.name AS job_name,    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,    SUSER_SNAME(j.owner_sid) AS job_owner,    j.notify_level_email,    o.name AS email_operator,    o.enabled AS operator_enabledFROM msdb.dbo.sysjobs AS jLEFT JOIN msdb.dbo.sysoperators AS o    ON o.id = j.notify_email_operator_idORDER BY j.notify_level_email, j.name; SELECT    p.name AS profile_name,    pp.is_default,    a.name AS account_name,    a.email_addressFROM msdb.dbo.sysmail_profile AS pLEFT JOIN msdb.dbo.sysmail_principalprofile AS pp    ON pp.profile_id = p.profile_idLEFT JOIN msdb.dbo.sysmail_profileaccount AS pa    ON pa.profile_id = p.profile_idLEFT JOIN msdb.dbo.sysmail_account AS a    ON a.account_id = pa.account_idORDER BY p.name, a.name;

Review before runningT-SQLUTF-825 lines

No notification may be acceptable only if external monitoring covers the same risk. A configured mail profile does not prove email delivery works. A personal mailbox is fragile if the person leaves or stops watching it. Test alert delivery through approved procedures before relying on it.

6 / Access

SQL Server access and ownership readiness

Production readiness depends on knowing who has privileged access and which accounts own jobs. Review access before changing it. Use the ownership gap guide when responsibility is unclear.

Lists server principals, disabled state, password policy flags, and server role membership.

SQL

Server role and sysadmin readiness

T-SQL · 18 lines

SELECT    sp.name AS login_name,    sp.type_desc,    sp.is_disabled,    sl.is_policy_checked,    sl.is_expiration_checked,    STRING_AGG(rp.name, ', ') WITHIN GROUP (ORDER BY rp.name) AS server_rolesFROM sys.server_principals AS spLEFT JOIN sys.sql_logins AS sl    ON sl.principal_id = sp.principal_idLEFT JOIN sys.server_role_members AS srm    ON srm.member_principal_id = sp.principal_idLEFT JOIN sys.server_principals AS rp    ON rp.principal_id = srm.role_principal_idWHERE sp.type IN ('S', 'U', 'G')  AND sp.name NOT LIKE '##%'GROUP BY sp.name, sp.type_desc, sp.is_disabled, sl.is_policy_checked, sl.is_expiration_checkedORDER BY CASE WHEN STRING_AGG(rp.name, ', ') LIKE '%sysadmin%' THEN 0 ELSE 1 END, sp.name;

Review before runningT-SQLUTF-818 lines

Review sysadmin membership before launch, audit, or handover. Disabled logins may still own jobs or database users. Do not remove access based only on this query. Older SQL Server versions without STRING_AGG need an adjusted version of this query.

Run inside a user database to find database users without matching server principals.

SQL

Orphaned database users starting point

T-SQL · 15 lines

SELECT    DB_NAME() AS database_name,    dp.name AS database_user,    dp.type_desc,    dp.authentication_type_desc,    dp.create_date,    dp.modify_dateFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp    ON sp.sid = dp.sidWHERE dp.type IN ('S', 'U', 'G')  AND dp.authentication_type_desc = 'INSTANCE'  AND sp.sid IS NULL  AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')ORDER BY dp.name;

Review before runningT-SQLUTF-815 lines

Run this in each important user database. Contained users and external authentication need separate interpretation. Orphaned users can matter during restore, migration, or handover. Review before changing users or mappings.

7 / HA and DR

SQL Server HA, DR, and failover readiness

HA can reduce downtime, but it does not replace restore readiness. Availability Groups still need healthy replicas, known failover behavior, valid backups, and application validation.

Use recovery readiness when restore or failover confidence is the main concern.

Checks AG replica role, replica health, database sync state, send queue, and redo queue.

SQL

Availability Group readiness

T-SQL · 18 lines

SELECT    ag.name AS availability_group_name,    ar.replica_server_name,    ars.role_desc,    ars.synchronization_health_desc AS replica_health,    DB_NAME(drs.database_id) AS database_name,    drs.synchronization_state_desc,    drs.synchronization_health_desc AS database_health,    drs.log_send_queue_size,    drs.redo_queue_sizeFROM sys.availability_groups AS agJOIN sys.availability_replicas AS ar    ON ar.group_id = ag.group_idLEFT JOIN sys.dm_hadr_availability_replica_states AS ars    ON ars.replica_id = ar.replica_idLEFT JOIN sys.dm_hadr_database_replica_states AS drs    ON drs.replica_id = ar.replica_idORDER BY ag.name, ar.replica_server_name, database_name;

Review before runningT-SQLUTF-818 lines

No rows may mean the instance does not host Availability Groups. Synchronization health matters before failover or planned maintenance. Send and redo queues can show lag that affects failover readiness. AG health does not prove restore readiness.

8 / Change and rollback

SQL Server deployment, patch, and rollback readiness

A production SQL Server is not ready for change if nobody knows when to stop, how to validate, or how to roll back. Use the SQL Server update guide before patch windows and the stability review guide when repeated issues follow changes.

Reads the current SQL Server error log for common readiness and rollback concerns.

SQL

Read-only error log searches before change

T-SQL · 7 lines

EXEC sys.xp_readerrorlog 0, 1, N'error';EXEC sys.xp_readerrorlog 0, 1, N'severity';EXEC sys.xp_readerrorlog 0, 1, N'failed';EXEC sys.xp_readerrorlog 0, 1, N'I/O';EXEC sys.xp_readerrorlog 0, 1, N'deadlock';EXEC sys.xp_readerrorlog 0, 1, N'timeout';EXEC sys.xp_readerrorlog 0, 1, N'recovery';

Review before runningT-SQLUTF-87 lines

xp_readerrorlog reads log content; it does not change SQL Server state. Review timing against patches, deployments, restarts, and failovers. Repeated I/O, recovery, severity, or timeout messages should be understood before new change. Search terms are only a starting point.

Change type	Ready means	Warning
Patch	Target build, backup point, rollback trigger, validation owner, and post-patch checks.	Use the update guide before the window.
Deployment	Release owner, database changes, rollback script/path, app validation, and stop time.	Do not discover rollback steps during the outage.
Migration	Cutover order, fallback path, data validation, DNS/app switch, and time limit.	Migration rollback often differs from patch rollback.
Config change	Current value captured, target value known, restart impact understood, revert command ready.	Some changes are not instantly reversible.
Failover	Replica health, sync state, app connection path, failback decision, and validation steps.	Failover is not a substitute for restore readiness.

9 / Capacity

SQL Server capacity and workload readiness

Production readiness includes headroom. Current availability is not enough if waits, active workload, file latency, file growth, or tempdb already show pressure. Use the sizing and tempdb guides when this section raises concerns.

Shows the largest wait categories since the last SQL Server restart, excluding common benign waits.

SQL

Wait stats readiness snapshot

T-SQL · 26 lines

SELECT TOP (40)    wait_type,    wait_time_ms,    waiting_tasks_count,    signal_wait_time_ms,    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,    CASE        WHEN waiting_tasks_count = 0 THEN 0        ELSE wait_time_ms / waiting_tasks_count    END AS avg_wait_msFROM sys.dm_os_wait_statsWHERE wait_type NOT IN (    N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',    N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',    N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',    N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',    N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',    N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'HADR_CLUSAPI_CALL',    N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',    N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',    N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',    N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',    N'SERVER_IDLE_CHECK', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',    N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')ORDER BY wait_time_ms DESC;

Review before runningT-SQLUTF-826 lines

Wait stats are cumulative since startup unless cleared. One snapshot does not prove a cause. Use deltas around important workload windows when possible. High waits should be compared with active workload and incident timing.

Shows currently running requests, waits, blocking, resource usage, and SQL text.

SQL

Active workload readiness snapshot

T-SQL · 29 lines

SELECT    r.session_id,    s.login_name,    s.host_name,    DB_NAME(r.database_id) AS database_name,    r.status,    r.command,    r.wait_type,    r.wait_time,    r.blocking_session_id,    r.cpu_time,    r.logical_reads,    r.reads,    r.writes,    r.total_elapsed_time,    SUBSTRING(        st.text,        (r.statement_start_offset / 2) + 1,        CASE            WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text))            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1        END    ) AS running_statementFROM sys.dm_exec_requests AS rJOIN sys.dm_exec_sessions AS s    ON s.session_id = r.session_idOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS stWHERE r.session_id <> @@SPIDORDER BY r.total_elapsed_time DESC;

Review before runningT-SQLUTF-829 lines

Run during a representative workload, not only during quiet periods. Blocking, high elapsed time, high reads, or repeated waits deserve follow-up. Do not kill sessions from this output without understanding the transaction. This is a point-in-time view.

Shows average read and write latency by database file from virtual file stats.

SQL

File latency readiness

T-SQL · 13 lines

SELECT    DB_NAME(vfs.database_id) AS database_name,    mf.type_desc,    mf.physical_name,    vfs.num_of_reads,    vfs.num_of_writes,    CASE WHEN vfs.num_of_reads = 0 THEN 0 ELSE vfs.io_stall_read_ms / vfs.num_of_reads END AS avg_read_latency_ms,    CASE WHEN vfs.num_of_writes = 0 THEN 0 ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_latency_msFROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfsJOIN sys.master_files AS mf    ON mf.database_id = vfs.database_id   AND mf.file_id = vfs.file_idORDER BY avg_read_latency_ms + avg_write_latency_ms DESC;

Review before runningT-SQLUTF-813 lines

Counters are cumulative since the file was opened. Separate data, log, and tempdb latency patterns. High latency can affect backups, CHECKDB, workload, and maintenance windows. Confirm with storage/platform metrics before blaming SQL alone.

Lists database file sizes, growth settings, and max size.

SQL

File growth readiness

T-SQL · 12 lines

SELECT    DB_NAME(database_id) AS database_name,    type_desc,    name AS file_name,    physical_name,    size * 8 / 1024 AS size_mb,    growth,    is_percent_growth,    max_sizeFROM sys.master_filesWHERE database_id > 4ORDER BY database_name, type_desc, file_name;

Review before runningT-SQLUTF-812 lines

Percent growth and tiny growth increments can both cause readiness issues. Autogrowth during busy windows can look like random slowness. Review growth with storage, backup, and maintenance plans. Do not shrink files as a readiness fix.

Shows tempdb file layout and current reserved space by usage category.

SQL

Tempdb readiness

T-SQL · 16 lines

SELECT    name AS file_name,    type_desc,    physical_name,    size * 8 / 1024 AS size_mb,    growth,    is_percent_growthFROM tempdb.sys.database_filesORDER BY type_desc, file_id; SELECT    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_object_mb,    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_object_mb,    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mbFROM tempdb.sys.dm_db_file_space_usage;

Review before runningT-SQLUTF-816 lines

Tempdb pressure can affect workload, jobs, and deployments. Run during realistic workload where possible. Version store, internal objects, and free space need separate interpretation. Use the tempdb guide for deeper diagnosis.

10 / Scorecard

SQL Server production readiness scorecard

The scorecard should be blunt. Ready means there is proof. Unknown means proof is missing. High risk means production should not carry more load or change until the issue is fixed.

Area	Ready	Ready with fixes	High risk	Proof missing
Recovery	Ready	Recent backups and restore test prove recovery path.	Backups exist but restore timing needs work.	Missing backups, broken log chain, or no restore path.	No one can show backup or restore history.
Jobs	Ready	Critical jobs succeed, alert, and finish in window.	Some noisy or long jobs need cleanup.	Critical jobs fail or are disabled.	Job history is missing or too short.
Monitoring	Ready	Failures reach a watched destination.	Coverage exists but has blind spots.	No alert owner or repeated missed failures.	No one knows what is monitored.
Access	Ready	Privileged access and owners are reviewed.	Some old owners or logins need follow-up.	Unknown sysadmin access or broken job owners.	No access inventory exists.
HA/DR	Ready	Failover and restore paths are both understood.	AG/DR exists but needs a drill.	Replica unhealthy or failover path unclear.	HA/DR state has not been checked.
Rollback	Ready	Stop points, revert path, and validation are known.	Rollback exists but timing is untested.	No rollback trigger for risky change.	Rollback is assumed, not documented.
Capacity	Ready	Waits, latency, tempdb, and growth have headroom.	Growth or latency needs scheduled review.	Capacity pressure is already visible.	No baseline exists.
Ownership	Ready	A named owner can act on findings.	Ownership exists but escalation is soft.	No one owns cross-area fixes.	Responsibility is unclear.

False confidence	Why it is weak
Quiet server	Demand may simply have been kind so far.
Green jobs	Final success can hide retries, skipped steps, or weak coverage.
Backup files exist	Files do not prove restore timing, dependency order, or recovery success.
Availability Group exists	HA does not prove restore readiness, app failover, or backup quality.
Dashboard is green	The dashboard may not cover jobs, backups, blocking, file growth, or tempdb.
Old access model still works	It may be over-privileged, ownerless, or dependent on stale accounts.

11 / Mistakes

Common SQL Server production readiness mistakes

Mistake	Result
Assuming quiet means ready	Readiness only matters when production gets less forgiving.
Backups without restore proof	Recovery timing and restore order remain unknown.
HA without failover testing	The system may fail in the exact moment HA is needed.
No rollback trigger	A bad change can consume the whole window before anyone stops.
No monitoring owner	The alert can be correct and still useless.
Job failures treated as background noise	Routine failures become production risk.
Access nobody reviewed	Old sysadmin access and job owners can break change control.
Unsupported build/version ignored	Patching and support risk sit under every future change.
Capacity reviewed only after launch	Headroom problems become user-facing surprises.

12 / Review help

When a SQL Server production readiness review helps

Need a second pair of eyes before SQL Server carries more production risk?

If the SQL Server is about to carry a launch, audit, handover, migration, upgrade, or heavier workload, a readiness review can check what is actually safe before the pressure arrives.

Use health audit for the wider readiness picture. Use recovery readiness when the main concern is restore, failover, or runbook confidence.

Request readiness review See health audit

Good fit when

The SQL Server is going live or taking more production load.
Backups exist, but restore or failover confidence is weak.
Jobs, alerts, access, or rollback plans have not been reviewed recently.
A patch, migration, audit, or handover is coming.

Next step

Use the SQL Server health audit when production readiness needs one findings list and a fix order.

Use recovery readiness when the main risk is restore, failover, or runbook confidence, or read the SQL Server recovery guide.