Will the server keep working when the office gets quiet? A change freeze checklist verifies backups, integrity checks, storage headroom, scheduled jobs, and serious errors before that quiet period starts. It gives you evidence that routine operations can continue unattended.
Start the Change Freeze Checklist with Deadlines
A freeze stops planned changes. Backups, log growth, and scheduled processing keep going. Decide the freeze dates, expected workload, and backup deadlines before reviewing results. A green status without an agreed deadline tells you very little.
I check recoverability before reviewing the rest of a freeze plan. Almost every unattended period exposes an assumption about who receives an alert. The server does not read the office calendar.
Run these checks with an authorized DBA account. You need access to msdb job and backup history, server metadata, and error logs. SQL Server 2022 and later use VIEW SERVER PERFORMANCE STATE for the volume DMV. Reading the error log on those versions also works with VIEW ANY ERROR LOG. Save the results with the review time and assigned owner.
Find the Last Completed Backup for Every Database
Start from sys.databases so a missing history row stays visible. This query finds completed native full and log backups with no recorded damage. It excludes snapshot entries. A copy-only full still provides a restore starting point, though it does not establish a differential base.
SELECT d.name, d.state_desc, d.recovery_model_desc,
f.backup_finish_date AS LastFullBackup,
f.has_backup_checksums AS FullHasChecksums,
f.is_copy_only AS FullIsCopyOnly,
l.backup_finish_date AS LastLogBackup,
DATEDIFF(minute, f.backup_finish_date, GETDATE()) AS FullAgeMinutes,
CASE WHEN d.recovery_model_desc <> N'SIMPLE'
THEN DATEDIFF(minute, l.backup_finish_date, GETDATE())
END AS LogAgeMinutes
FROM sys.databases AS d
OUTER APPLY
(
SELECT TOP (1) b.backup_finish_date,
b.has_backup_checksums, b.is_copy_only
FROM msdb.dbo.backupset AS b
WHERE b.database_name = d.name AND b.type = 'D'
AND b.backup_finish_date IS NOT NULL
AND b.is_damaged = 0 AND b.is_snapshot = 0
ORDER BY b.backup_finish_date DESC, b.backup_set_id DESC
) AS f
OUTER APPLY
(
SELECT TOP (1) b.backup_finish_date
FROM msdb.dbo.backupset AS b
WHERE b.database_name = d.name AND b.type = 'L'
AND b.backup_finish_date IS NOT NULL
AND b.is_damaged = 0 AND b.is_snapshot = 0
ORDER BY b.backup_finish_date DESC, b.backup_set_id DESC
) AS l
WHERE d.name <> N'tempdb'
ORDER BY d.name;Healthy results meet your full-backup deadline. FULL and BULK_LOGGED databases also meet the log-backup deadline. SIMPLE databases do not require log backups. Include system databases in the review. Resolve unexpected offline states rather than filtering those databases out.
History proves a recorded operation, not a usable restore chain. Confirm the backup files remain accessible and retain restore-test evidence. For an availability group, review history on every replica that takes backups. Database-name reuse and purged history also require checking the actual backup identity and records.
Check how the next backup failure reaches the on-call contact. A successful full backup today does not excuse a broken notification route tomorrow. Match the retained backup files to the required recovery window. Include the log files between the restore starting point and the target time. Someone else should be able to follow those restore instructions.
Read the Evidence of a Completed CHECKDB
A scheduled integrity job proves intent. A completed check proves execution. Read CHECKDB entries without filtering out failures. The next block reads the current error log and shows recent matching entries. Change the log number to 1 or higher to inspect retained archives.
DROP TABLE IF EXISTS #FreezeCheckDb;
CREATE TABLE #FreezeCheckDb
(
LogDate datetime,
ProcessInfo nvarchar(50),
LogText nvarchar(max)
);
INSERT #FreezeCheckDb
EXEC master.sys.sp_readerrorlog 0, 1, N'CHECKDB';
SELECT LogDate, ProcessInfo, LogText
FROM #FreezeCheckDb
WHERE LogDate >= DATEADD(day, -7, GETDATE())
ORDER BY LogDate DESC;The seven-day window is an example. Match it to your integrity policy. For each database, locate its latest completed check and confirm it found zero errors. A later failed or terminated check needs action even when an earlier clean result exists.
Startup entries can report an older clean-check date. Read the date inside the message, not just the log timestamp. Empty output proves no retained matching evidence. It does not prove integrity. Check archived logs and saved job output, including whether PHYSICAL_ONLY restricted the check’s scope.
Compare Drive Headroom with Actual File Growth
Free space needs context. Keep a file-size sample before the freeze review, then compare another sample near the cutoff. Run this block in your DBA utility database. It creates one small history table and saves the current sizes of online data and log files.
IF OBJECT_ID(N'dbo.FreezeFileSize', N'U') IS NULL
BEGIN
CREATE TABLE dbo.FreezeFileSize
(
CaptureTimeUtc datetime2(3) NOT NULL,
PhysicalName nvarchar(260) NOT NULL,
FileType nvarchar(60) NOT NULL,
SizeMB decimal(19,2) NOT NULL
);
END;
DECLARE @CurrentTime datetime2(3) = SYSUTCDATETIME();
DECLARE @PreviousTime datetime2(3) =
(SELECT MAX(CaptureTimeUtc) FROM dbo.FreezeFileSize);
INSERT dbo.FreezeFileSize (CaptureTimeUtc, PhysicalName, FileType, SizeMB)
SELECT @CurrentTime, f.physical_name, f.type_desc,
CONVERT(decimal(19,2), f.size / 128.0)
FROM sys.master_files AS f
JOIN sys.databases AS d ON d.database_id = f.database_id
WHERE f.type IN (0, 1) AND d.state_desc = N'ONLINE';
;WITH Files AS
(
SELECT v.volume_mount_point, v.available_bytes,
c.SizeMB, c.FileType,
CASE WHEN b.SizeMB IS NULL THEN NULL
WHEN c.SizeMB > b.SizeMB THEN c.SizeMB - b.SizeMB
ELSE 0 END AS GrowthMB
FROM sys.master_files AS f
JOIN dbo.FreezeFileSize AS c
ON c.PhysicalName = f.physical_name
AND c.CaptureTimeUtc = @CurrentTime
LEFT JOIN dbo.FreezeFileSize AS b
ON b.PhysicalName = c.PhysicalName
AND b.CaptureTimeUtc = @PreviousTime
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS v
)
SELECT volume_mount_point,
@PreviousTime AS PreviousCaptureUtc,
@CurrentTime AS CurrentCaptureUtc,
CONVERT(decimal(19,2), MIN(available_bytes) / 1048576.0) AS FreeMB,
SUM(CASE WHEN FileType = N'ROWS' THEN SizeMB ELSE 0 END) AS DataMB,
SUM(CASE WHEN FileType = N'LOG' THEN SizeMB ELSE 0 END) AS LogMB,
CASE WHEN SUM(CASE WHEN GrowthMB IS NULL THEN 1 ELSE 0 END) = 0
THEN SUM(GrowthMB) END AS PositiveFileGrowthMB,
SUM(CASE WHEN GrowthMB IS NULL THEN 1 ELSE 0 END) AS UnmatchedFiles
FROM Files
GROUP BY volume_mount_point
ORDER BY volume_mount_point;Healthy headroom covers expected growth throughout the freeze, plus operating room. Compare the measured interval with the freeze duration and expected peaks. FreeMB appears once per volume. Do not add repeated drive totals from individual files.
The first run has unknown growth. Unmatched files remain visible. File-size changes measure allocated growth between samples, not every autogrowth operation. Shrink and regrowth can hide activity. Include known peak growth, backup-drive needs, tempdb demand, and other applications using the same volume.
Review file growth settings and maximum sizes in SSMS too. Drive space alone does not guarantee that a file can expand. A disabled growth setting or a reached file limit needs its own decision. If the expected demand exceeds headroom, resolve capacity before the cutoff and save the new evidence after that work finishes.
Add Disabled Jobs to the Change Freeze Checklist
A disabled backup job does not generate a fresh failure. Check enabled state alongside history. This query returns disabled jobs, jobs without a recorded outcome, and jobs with recent failed or canceled steps or outcomes. It also shows the latest recorded whole-job result.
DECLARE @SinceDate int =
CONVERT(int, CONVERT(char(8), DATEADD(day, -7, GETDATE()), 112));
SELECT j.name, j.enabled,
last_run.run_date AS LastOutcomeDate,
last_run.run_time AS LastOutcomeTime,
last_run.run_status AS LastOutcomeStatus,
last_run.message AS LastOutcomeMessage,
bad_run.run_date AS RecentIssueDate,
bad_run.step_id AS RecentIssueStep,
bad_run.message AS RecentIssueMessage
FROM msdb.dbo.sysjobs AS j
OUTER APPLY
(
SELECT TOP (1) h.run_date, h.run_time, h.run_status, h.message
FROM msdb.dbo.sysjobhistory AS h
WHERE h.job_id = j.job_id AND h.step_id = 0
ORDER BY h.instance_id DESC
) AS last_run
OUTER APPLY
(
SELECT TOP (1) h.run_date, h.step_id, h.message
FROM msdb.dbo.sysjobhistory AS h
WHERE h.job_id = j.job_id AND h.run_date >= @SinceDate
AND h.run_status IN (0, 3)
ORDER BY h.instance_id DESC
) AS bad_run
WHERE j.enabled = 0 OR last_run.run_status IS NULL
OR last_run.run_status <> 1 OR bad_run.run_date IS NOT NULL
ORDER BY j.name;Healthy results have no unexplained exceptions for required jobs. Document intentional disabled jobs rather than enabling everything. A later successful retry does not erase an earlier failed step from this review. Confirm the issue was resolved and recurrence is monitored.
I review backup and integrity schedules even when this query returns nothing. Enabled jobs still need enabled schedules, valid credentials, and a running Agent service. Confirm those settings in SSMS and check the next expected run. Purged history needs separate execution evidence.
Read Serious Errors with the Surrounding Message
Resource and engine errors deserve review before unattended operation. The next block extracts severity from current-log entries using the standard English severity label. Keep the full text. Error details also appear on adjacent lines, which you can review in SSMS Log File Viewer.
DROP TABLE IF EXISTS #FreezeErrors;
CREATE TABLE #FreezeErrors
(
LogDate datetime,
ProcessInfo nvarchar(50),
LogText nvarchar(max)
);
INSERT #FreezeErrors
EXEC master.sys.sp_readerrorlog 0, 1;
SELECT e.LogDate, e.ProcessInfo, s.ErrorSeverity, e.LogText
FROM #FreezeErrors AS e
CROSS APPLY (VALUES (CHARINDEX(N'Severity:', e.LogText))) AS p(LabelPosition)
CROSS APPLY (VALUES
(SUBSTRING(e.LogText, p.LabelPosition + LEN(N'Severity:'), 12))
) AS t(SeverityTail)
CROSS APPLY (VALUES
(TRY_CONVERT(int, LTRIM(RTRIM(LEFT(t.SeverityTail,
CHARINDEX(N',', t.SeverityTail + N',') - 1)))))
) AS s(ErrorSeverity)
WHERE p.LabelPosition > 0 AND s.ErrorSeverity >= 17
AND e.LogDate >= DATEADD(day, -7, GETDATE())
ORDER BY e.LogDate DESC;Healthy evidence contains no unresolved serious errors within the review window. Repeat against retained archives when the window crosses a log rollover. Localized messages need the corresponding severity label. Some failures lack this format, so review other relevant warnings and alerts too.
Close the Change Freeze Checklist with an Owner
Finish with named ownership for every unresolved item. Keep the review time, backup deadlines, storage assumptions, and exception decisions together. Set a cutoff for resolving problems before the freeze begins.
Make the handoff usable during a problem. Record where the saved outputs live, which jobs protect recovery, and which exceptions require immediate escalation. Confirm the contact has the access needed to investigate. A telephone number without access to the server leaves the next person reading an alert without a practical next step.
A checklist shows one moment. Backups fail later, volumes fill later, and jobs stop later. Test notification delivery and record the on-call contact plus an escalation contact. Confirm how emergency changes get approved. A change freeze checklist works when someone can act on the next alert.
Related reading on this blog: Index Optimization CheckList and Checklist for Analyzing Slow-Running Queries.
A change freeze is not a pause in responsibility, it is a plan for unattended operation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
