DBHealthHistory data file is too large

What this check looks for

When a DBHealthHistory database exists, the size of its data file, from DBHealthHistory.sys.database_files where type_desc is ROWS. The check fires above 16 GB, and the message reports the actual size in megabytes.

Why it matters

DBHealthHistory is where Database Health Monitor keeps the history it collects. It holds sampled counters, wait statistics, query history, job outcomes and the rest of the collected record, and it is deliberately designed to be small: a monitoring history database should be a fraction of the size of the databases it is monitoring.

Past 16 GB, something is out of proportion, and it is worth finding out which of these it is:

• Retention is longer than intended, so several years of samples are being kept when months were wanted.
• The collection interval is shorter than intended on a busy instance, so far more rows are being written than expected.
• A purge job has stopped running, and nothing has trimmed the history for a long time.
• One table has grown out of proportion to the rest, which is a different question and has its own check.
• The file is large but mostly empty, because the data was purged and the file was never reclaimed.

The consequences are modest, which is why this is Low: a larger backup, a slower CHECKDB, and reports over the history running more slowly than they should. The monitoring should not be a meaningful load on the instance it monitors, and at this size it is starting to be.

How to confirm it yourself

The file sizes, and how much of each is actually used:

USE [DBHealthHistory];

SELECT f.[name]                                       AS [logical_name],
       f.[type_desc],
       CAST(f.[size] * 8.0 / 1024 AS DECIMAL(12,1))   AS [size_mb],
       CAST(FILEPROPERTY(f.[name], 'SpaceUsed') * 8.0 / 1024 AS DECIMAL(12,1)) AS [used_mb],
       CAST(FILEPROPERTY(f.[name], 'SpaceUsed') * 100.0 / NULLIF(f.[size], 0) AS DECIMAL(5,1)) AS [pct_used],
       f.[physical_name]
  FROM sys.database_files AS f WITH (NOLOCK)
 ORDER BY f.[type_desc], f.[name];

Read the pct_used column before doing anything else. A 20 GB file that is 10 percent used is a completely different problem from one that is 95 percent used, and the fix is different too.

Where the space has gone, if the file is genuinely full:

USE [DBHealthHistory];

SELECT OBJECT_SCHEMA_NAME(t.[object_id]) AS [schema_name],
       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 sys.tables AS t WITH (NOLOCK)
 INNER JOIN sys.indexes AS i WITH (NOLOCK)
         ON i.[object_id] = t.[object_id]
 INNER JOIN sys.partitions AS p WITH (NOLOCK)
         ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id]
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK)
         ON a.[container_id] = p.[partition_id]
 WHERE t.[is_ms_shipped] = 0
 GROUP BY t.[object_id], t.[name]
 ORDER BY [size_mb] DESC;

And how far back the history goes, which is usually the answer:

USE [DBHealthHistory];

SELECT MIN([logTime]) AS [oldest], MAX([logTime]) AS [newest]
  FROM [dbo].[ErrorLog] WITH (NOLOCK);

How to fix it

In this order, because the first two are free.

1. Check the retention settings in Database Health Monitor’s collection options. If history is being kept far longer than anybody looks at, shortening the retention and letting the purge catch up is the whole fix.
2. Check the purge is running. If the collector’s cleanup job is disabled or failing, nothing has been trimmed and the retention setting is irrelevant.
3. Reduce the collection frequency for anything sampling more often than you need. The sampling rate is the main lever on row volume.
4. Deal with one oversized table if the breakdown above shows the space concentrated rather than spread. That is the large tables check.
5. Only then consider the file itself. If the data has been purged and the file is mostly empty, a one off DBCC SHRINKFILE on this database is defensible in a way it is not on a production database, because this is a monitoring store and its index fragmentation matters much less. Do it once, after the purge, and then leave it alone.

What not to do: do not put DBHealthHistory on a nightly shrink schedule. The same objections apply here as anywhere else, and the file will simply grow back.

How long it takes

About half an hour, mostly waiting for a purge to run.

Related reports

Related checks

Frequently asked questions

Why 16 GB? It is well above what a normally configured history database reaches, so reaching it means something is worth looking at rather than that the number is too low.

We monitor 40 instances. Is a large history normal? More instances mean more rows, so a larger database is expected. Look at the growth rate and the retention rather than the absolute size, and use the table breakdown to see whether it is spread evenly.

Can I just delete old rows directly? Use the product’s retention settings rather than deleting from the tables. The tables are related and the purge understands the relationships.

Does this affect the instance being monitored? Only through backups, CHECKDB and the I/O of collection. That is why it is Low. It is a proportionality problem, not an outage.