Understanding Target Recovery Time in SQL Server

What this check looks for

Databases whose target_recovery_time_in_seconds in sys.databases falls outside the recommended range of 30 to 90 seconds. The message names the database and the current value.

Why it matters

Target recovery time is how long SQL Server will spend redoing work after a crash, and it controls how aggressively dirty pages are written to disk in the meantime. It is a direct trade between recovery speed and steady state write activity.

How it works. With a non zero target recovery time, the database uses indirect checkpoints: a background writer continuously flushes dirty pages so that the amount of work needing redo after a crash stays within the target. With zero, the database uses the older automatic checkpoint behavior, governed by the instance level recovery interval setting, which writes dirty pages in periodic bursts.

What a value that is too high costs you:

• Crash recovery takes longer, and the target is the promise you are making about it. A target of 600 seconds means the database may spend ten minutes in recovery after an unexpected restart, during which it is unavailable.
• Recovery duration becomes unpredictable, which is worse than slow. The amount of redo work depends on how busy the database was at the moment it stopped, so the recovery you time in a test is not the one you get during an incident.
• More dirty pages accumulate, so when a checkpoint does occur it is large.

What a value that is too low costs you:

• Constant checkpoint write activity. The background writer is flushing pages all the time, including pages that would have been modified again shortly and could have been written once instead of repeatedly.
• Higher write I/O for the same work, which on slow or shared storage is a measurable penalty.
• More CPU in the checkpoint process.

The default is 60 seconds for databases created on SQL Server 2016 and later, and that default is correct for almost everything. Which means a value outside the range usually got there in one of three ways:

• A database created on SQL Server 2012 or 2014 and upgraded. Indirect checkpoints existed from 2012 but were not the default until 2016, so upgraded databases frequently still have 0.
• Somebody tuned it in response to a checkpoint related I/O problem, often setting it very low, and the underlying storage problem was the real issue.
• A vendor script applied a value without explaining it.

Zero is worth treating as its own case. It means indirect checkpoints are off and the database is using the instance level recovery interval instead, which defaults to 0 as well, meaning SQL Server chooses. That is not broken, it is the old behavior, and moving to a 60 second target usually smooths out the checkpoint I/O spikes that the old behavior produces.

How to confirm it yourself

Every database and its setting:

SELECT [name],
       [target_recovery_time_in_seconds],
       [recovery_model_desc],
       [state_desc],
       [create_date]
  FROM sys.databases WITH (NOLOCK)
 ORDER BY [target_recovery_time_in_seconds] DESC, [name];

0 means indirect checkpoints are off. 60 is the modern default. Anything above 90 or between 1 and 30 is the finding.

The instance level setting that applies when the target is 0:

SELECT [name], [value], [value_in_use], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'recovery interval (min)';

How long recovery actually took last time, which is the number the setting is a promise about:

EXEC xp_readerrorlog 0, 1, N'Recovery completed';

Those entries state the recovery time per database at the last startup, and they are the best evidence of whether the current setting is producing acceptable behavior.

What checkpoint activity looks like now:

SELECT [object_name], [counter_name], [cntr_value]
  FROM sys.dm_os_performance_counters WITH (NOLOCK)
 WHERE [counter_name] IN ('Checkpoint pages/sec', 'Background writer pages/sec',
                          'Lazy writes/sec', 'Page writes/sec');

Background writer pages/sec is the indirect checkpoint writer. A high and constant value there alongside a very low target recovery time is the too-low case.

And how much dirty data is sitting in memory, which is what recovery would have to redo:

SELECT DB_NAME([database_id])                          AS [database_name],
       COUNT(*) * 8 / 1024                             AS [cached_mb],
       SUM(CASE WHEN [is_modified] = 1 THEN 1 ELSE 0 END) * 8 / 1024 AS [dirty_mb]
  FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
 WHERE [database_id] <> 32767
 GROUP BY [database_id]
 ORDER BY [dirty_mb] DESC;

How to fix it

Set it to 60 seconds unless you have a measured reason not to. It is online and immediate.

ALTER DATABASE [YourDatabase] SET TARGET_RECOVERY_TIME = 60 SECONDS;

All databases at once:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'ALTER DATABASE ' + QUOTENAME([name])
                   + N' SET TARGET_RECOVERY_TIME = 60 SECONDS;' + CHAR(13) + CHAR(10)
  FROM sys.databases WITH (NOLOCK)
 WHERE [database_id] > 4
   AND [state_desc] = 'ONLINE'
   AND ([target_recovery_time_in_seconds] < 30 OR [target_recovery_time_in_seconds] > 90);

PRINT @sql;
-- review, then run it
-- EXEC sp_executesql @sql;

Print it first and read it. That is a habit worth keeping for any generated ALTER DATABASE loop.

When a different value is justified:

• Lower, toward 30 seconds, when recovery time is critical and the storage can absorb the extra write activity. Check Background writer pages/sec and write latency afterwards.
• Higher, toward 90 seconds, on a write heavy database on constrained storage, where the continuous flushing is measurably costing you and a longer recovery is acceptable.

Below 30 or above 90 is rarely the right answer, and if a value outside that range was set deliberately, the reason should be recorded somewhere.

Measure the effect. Take the checkpoint counters before and after, and watch write latency on the data files:

SELECT DB_NAME(vfs.[database_id]) AS [database_name],
       mf.[physical_name],
       vfs.[num_of_writes],
       vfs.[io_stall_write_ms] / NULLIF(vfs.[num_of_writes], 0) AS [avg_write_ms]
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
 INNER JOIN sys.master_files AS mf WITH (NOLOCK)
         ON mf.[database_id] = vfs.[database_id] AND mf.[file_id] = vfs.[file_id]
 WHERE mf.[type] = 0
 ORDER BY [avg_write_ms] DESC;

Set it on model too, so new databases inherit the right value:

ALTER DATABASE [model] SET TARGET_RECOVERY_TIME = 60 SECONDS;

And if checkpoint I/O was the original reason somebody changed this, the setting is treating a symptom. Slow write latency on the data volume is the finding underneath, and the slow I/O checks cover it.

How long it takes

About an hour, most of it reviewing the databases and confirming the effect afterwards. The change itself is instant and online.

Related reports

Related checks

Frequently asked questions

What does zero mean? Indirect checkpoints are off, and the database uses the older automatic checkpoint behavior driven by the instance recovery interval setting. It is not broken, it is the pre-2016 default, and moving to 60 seconds usually smooths out the checkpoint I/O spikes.

Will changing this require downtime? No. ALTER DATABASE ... SET TARGET_RECOVERY_TIME is online and takes effect immediately.

Is a lower value safer? It shortens recovery and increases continuous write activity. Below 30 seconds the write cost usually outweighs the recovery benefit, which is why the recommended floor is there.

How do I know what recovery actually costs today? The error log records the recovery time for each database at startup. Search it for “Recovery completed” and compare against the target you have set.