DBHealthHistory log file max size is too large

What this check looks for

The max_size of the transaction log file in the DBHealthHistory database. It only runs where that database exists, which is on instances where Database Health Monitor history collection has been set up.

Why it matters

DBHealthHistory is a repository. It collects monitoring samples in small batches, it is written by a scheduled collector rather than by an application, and nothing about how it is used calls for a large transaction log.

A large log file on this database is almost always one of two things:

A one-off event that grew it. The usual causes are an initial load, a large purge of old history, or an index rebuild across the collection tables. In full recovery with no log backups the log then stays at whatever size that operation reached, because SQL Server does not reclaim log space on its own. The size is a historical record of one busy afternoon, not a statement of need.

The recovery model. If DBHealthHistory is in full recovery and nothing takes log backups, the log cannot truncate at all and it grows until something stops it. That is the case worth checking first, and it is the more common one, because a database created without specifying a recovery model inherits whatever model is set to.

What it costs:

• Disk space, on whatever volume holds it, often the same one as the user databases.
• Slow startup and recovery, because the log’s virtual log files are walked at recovery, and a log that grew in small increments has a great many of them.
• Slow restore, for the same reason.
• It competes with the databases you actually care about, which is the real argument. A monitoring repository consuming space and I/O that the production databases need has its priorities inverted.

And a full log stops collection. If the log cannot grow any further, the collector’s inserts fail, and monitoring stops silently. A monitoring system that has stopped collecting looks exactly like a healthy system with nothing to report, which is the worst failure mode available.

The right configuration is straightforward: simple recovery model, a log sized for the largest single operation the repository performs, which is the purge, and a fixed growth increment in megabytes.

How to confirm it yourself

The file configuration:

SELECT mf.[name]                                     AS [logical_name],
       mf.[type_desc],
       CAST(mf.[size] * 8.0 / 1024 AS DECIMAL(12,1)) AS [current_mb],
       CASE WHEN mf.[max_size] = -1 THEN 'unlimited'
            WHEN mf.[max_size] = 268435456 THEN 'unlimited (log)'
            ELSE CAST(mf.[max_size] * 8 / 1024 AS VARCHAR(20)) + ' MB' END AS [max_size],
       CASE WHEN mf.[is_percent_growth] = 1 THEN CAST(mf.[growth] AS VARCHAR(10)) + ' %'
            ELSE CAST(mf.[growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth],
       mf.[physical_name]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[database_id] = DB_ID('DBHealthHistory');

The recovery model and whether anything is blocking truncation, which is the first thing to check:

SELECT [name], [recovery_model_desc], [log_reuse_wait_desc], [state_desc]
  FROM sys.databases WITH (NOLOCK)
 WHERE [name] = 'DBHealthHistory';

FULL with log_reuse_wait_desc of LOG_BACKUP is the explanation for a large log, and it is the thing to fix rather than the size itself.

How much of the log is actually used:

USE [DBHealthHistory];
GO
SELECT CAST([total_log_size_in_bytes] / 1048576.0 AS DECIMAL(12,1)) AS [log_size_mb],
       CAST([used_log_space_in_bytes]  / 1048576.0 AS DECIMAL(12,1)) AS [used_mb],
       CAST([used_log_space_in_percent] AS DECIMAL(5,1))             AS [used_pct]
  FROM sys.dm_db_log_space_usage;

A very low used percentage on a very large log is the finding exactly.

The VLF count, which is the cost the growth left behind:

USE [DBHealthHistory];
GO
SELECT COUNT(*) AS [vlf_count] FROM sys.dm_db_log_info(DB_ID());

And the size of the data, for context, since the log should be a fraction of it:

SELECT t.[name]                                     AS [table_name],
       SUM(p.[rows])                                AS [rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb]
  FROM [DBHealthHistory].sys.tables          AS t WITH (NOLOCK)
 INNER JOIN [DBHealthHistory].sys.partitions AS p WITH (NOLOCK) ON p.[object_id] = t.[object_id]
 INNER JOIN [DBHealthHistory].sys.allocation_units AS a WITH (NOLOCK)
         ON a.[container_id] = p.[partition_id]
 WHERE p.[index_id] IN (0, 1)
 GROUP BY t.[name]
 ORDER BY [size_mb] DESC;

How to fix it

Set simple recovery, shrink the log once, then grow it back deliberately to a sensible size.

1. Simple recovery model, which is correct for a monitoring repository. The data is reconstructable by collection, so point in time recovery buys nothing:

ALTER DATABASE [DBHealthHistory] SET RECOVERY SIMPLE;

2. Shrink the log once:

USE [DBHealthHistory];
GO
DBCC SHRINKFILE (N'DBHealthHistory_log', 1);

3. Grow it back in one deliberate step, so it has a healthy VLF count rather than thousands from incremental growth:

ALTER DATABASE [DBHealthHistory]
  MODIFY FILE (NAME = N'DBHealthHistory_log', SIZE = 512MB, FILEGROWTH = 128MB, MAXSIZE = 4GB);

Step 3 is the one people skip. Leaving the log small means it grows back under the next purge, in small increments, generating VLFs again.

What size? Large enough for the biggest single transaction the repository runs, which is normally the history purge. If the purge deletes a month of samples in one statement, size the log for that; if it deletes in batches, it needs far less.

Set a MAXSIZE so the repository cannot consume the whole volume. That converts an unbounded risk into a bounded one: collection fails rather than the disk filling, and a failed collection is much easier to recover from than a full drive.

4. Use a fixed growth increment in megabytes, not a percentage, for the same reason as any other database.

5. Purge history in batches, which is what keeps the log small in the first place. A delete covering months in one statement is a single enormous transaction; the same delete in daily batches barely touches the log:

-- batched, rather than one large delete
DECLARE @batch INT = 1;
WHILE @batch > 0
BEGIN
    DELETE TOP (5000) FROM [dbo].[YourHistoryTable]
     WHERE [logTime] < DATEADD(DAY, -90, GETDATE());
    SET @batch = @@ROWCOUNT;
END;

6. Consider where the repository lives. A collection database on the same volume as production data competes with it. Moving DBHealthHistory to its own volume, or to a dedicated monitoring instance, removes that competition entirely and is worth doing if the repository is collecting from several servers.

And check the collection is still running afterwards, since the point of all of this is that the monitoring keeps working.

How long it takes

About half an hour. The recovery model change and the resize are online and immediate.

Related reports

Related checks

Frequently asked questions

Why should DBHealthHistory be in simple recovery? It is a monitoring repository. The data is collected samples, reconstructable by collecting again, so point in time recovery of it has no value while full recovery with no log backups guarantees the log grows.

Can I just shrink the log and leave it small? It will grow back on the next purge, in small increments, producing a high VLF count. Shrink once and grow it back deliberately to the size it needs.

Should I set a MAXSIZE? Yes. It means a runaway repository fails its own inserts instead of filling the volume, which is a much better outcome and a much easier one to recover from.

Should the repository be on its own instance? If it is collecting from several servers, yes. It removes the competition with production data entirely and makes the monitoring survive a problem on any one monitored instance.