Large tables in DBHealthHistory database

What this check looks for

Individual tables over 1 GB, measured from sys.allocation_units through sys.partitions and sys.indexes, excluding Microsoft shipped tables and anything named sys%. The message names the schema, the table and the size in gigabytes.

The size reported is total pages, so it includes the table’s indexes rather than the data alone.

Why it matters

This is the more useful of the two size checks, because it points at something specific.

The whole database being large is a proportionality question with several possible answers. One table being large is a much narrower statement: one collection is producing far more rows than the others, and that is either a setting or a symptom.

  • A setting: that collection is sampling more often than it needs to, or its retention is longer than the rest.
  • A symptom: something on the monitored instance is generating a great deal of whatever that table records. A wait statistics table that has grown to several gigabytes is telling you about the instance, not about the monitoring. So is a blocking history table, or a deadlock table, or a job failure table.

The second case is worth more than the finding itself. A monitoring table that has grown out of proportion is a piece of evidence about the thing being monitored, and it is the only check in this report that finds a problem by noticing how much was written about it.

The direct costs are the same as any large table: it is in every backup, it is checked by every CHECKDB, and the history reports that read it get slower.

How to confirm it yourself

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 / 1024 AS DECIMAL(10,2)) AS [total_gb],
       CAST(SUM(a.[used_pages])     * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS [used_gb],
       CAST(SUM(a.[data_pages])     * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS [data_gb]
  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]
HAVING SUM(a.[total_pages]) * 8.0 / 1024 / 1024 > 1
 ORDER BY [total_gb] DESC;

Then find out how far back that one table goes. Most of these tables carry a timestamp column, and the oldest row tells you whether this is a retention problem or a volume problem:

USE [DBHealthHistory];

SELECT COUNT(*) AS [rows],
       MIN([collectionTime]) AS [oldest],
       MAX([collectionTime]) AS [newest]
  FROM [dbo].[TheTableNamedInTheFinding] WITH (NOLOCK);

Divide the row count by the number of days covered. A table holding two years of history is a retention setting. A table holding two weeks and several gigabytes is a volume signal about the monitored instance.

How to fix it

Work out which of the two it is first, because the fixes are opposite.

If the table holds more history than you need:

  • Shorten the retention for that collection in Database Health Monitor’s historic options.
  • Confirm the purge is running. A retention setting with no working purge changes nothing.

If the table holds a normal period and is still large:

  • The instance is generating that much of whatever it records, and that is the finding. A large wait statistics table means a lot of waits. A large blocking table means a lot of blocking. Go and look at the corresponding report.
  • Consider sampling that collection less often, but do so knowing you are turning down the resolution on something the instance is doing a lot of.

In either case, reclaiming the space after a purge is a one off DBCC SHRINKFILE on this database, which is defensible here in a way it is not on a production database, because this is a monitoring store. Do it once, afterwards, and do not schedule it.

How long it takes

About an hour, mostly the query above and the decision that follows it.

Report Why you would go there
Table Sizes Every table in the database ranked by size.
Historic Options The retention and interval settings for each collection.
Historic Overview What is being collected and how much of it there is.
Historic Waits If the large table is the wait statistics history, this is what it holds.
Table Space Breakdown Data against index against unused, for one table.
Large Tables The same instinct applied to the monitored databases.
Check
DBHealthHistory data file is too large The whole database rather than one table.
Error log history in DBHealthHistory Collector errors, which sometimes explain runaway growth.
Excessive sysmail_allitems Unbounded history in a system database.

Frequently asked questions

Which table is it? The finding names the schema and the table. The query above ranks them all so you can see the shape of the whole database.

Is 1 GB really large? For one table in a monitoring history database, yes. It means that one collection dominates everything else being kept.

The table is large because we monitor many instances. That is expected, and the useful number is gigabytes per instance per month rather than the total. Work that out before changing any settings.

Can I drop the table? No. Adjust the retention and let the purge do it. The tables are related and dropping one breaks the history reports that read it.