A table can grow quietly until a maintenance window or storage alert makes the change obvious. Recording table row counts every day gives you a simple trend and a place to investigate sudden jumps.
Choose an Approximate Daily Measure for Recording Table Row Counts
sys.dm_db_partition_stats reports row counts from partition metadata. It is efficient for a daily trend, but it is not an exact transactionally consistent count at every instant. Sum index_id zero or one so each table’s rows are counted once. Do not add nonclustered index rows to the table total. Capture at a consistent time to reduce workload-cycle noise.
I use this measure to find change, not to settle a financial reconciliation. For an exact count, a filtered COUNT_BIG query on the actual table can be needed, with the cost and isolation choice understood. Recording table row counts daily should be light enough to keep running. The perfect count that nobody can afford to collect is not a useful monitor.
Create a Small History Table
Put the history in a utility database with clear ownership and retention. Store capture time, database name, schema, table, and row count. If database or table names change, decide whether the trend should follow an object ID or begin a new series. The simple design below uses names because they remain readable across exports. Adjust the table location to your administration standards.
I keep the raw snapshots. They let you revisit a suspicious day without relying on a chart that averaged it away. Do not put the history table inside a database you plan to retire based on the same trend.
CREATE TABLE dbo.TableRowCountHistory
(
capture_time datetime2(0) NOT NULL,
database_name sysname NOT NULL,
schema_name sysname NOT NULL,
table_name sysname NOT NULL,
row_count bigint NOT NULL
);Capture One Database
Run a snapshot query within each target database. The example inserts into a utility database named DBAUtility; create and protect that database before running it, or replace the target with your approved location. The index filter prevents double counting. Some partitioned tables produce multiple partition rows, and the SUM handles them.
I test the statement on one database and compare a few known tables before scheduling it broadly. Record the execution context. A job that lacks rights to one database can leave a partial snapshot, so the collector should log failures as well as successes. Partial data should never draw a complete-looking trend.
INSERT DBAUtility.dbo.TableRowCountHistory
(capture_time, database_name, schema_name, table_name, row_count)
SELECT SYSDATETIME(), DB_NAME(), s.name, t.name,
SUM(ps.row_count)
FROM sys.tables AS t
JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
JOIN sys.dm_db_partition_stats AS ps
ON ps.object_id = t.object_id
WHERE ps.index_id IN (0, 1)
GROUP BY s.name, t.name;Schedule Recording Table Row Counts With a Job
Create a SQL Agent job that runs at the same local time each day, outside heavy maintenance when possible. Use a controlled list of databases rather than dynamic SQL over every database without review. Include an alert for job failure. If the instance is in an availability group, decide which replica should collect and how to avoid duplicate rows after failover.
I write down the collector’s scope and expected runtime. A job that silently skips a new database creates a blind spot. Review the database list after deployments. If you use a central collector, record both source instance and database so names do not collide. The history needs enough identity to remain useful after a migration.
Find Sudden Growth
Compare each table’s current snapshot with its preceding snapshot. A sharp increase can be legitimate after a load or suspicious after a deployment. Calculate absolute change and percentage, but guard against division by zero for newly populated tables. The following query returns recent history for a named table. It leaves the interpretation to the reader rather than asserting a universal threshold.
I review the largest absolute changes first, then the largest relative changes. A tiny table doubling can matter less than a large table growing steadily. The business event behind the change is the key.
SELECT capture_time,
row_count,
row_count - LAG(row_count) OVER
(ORDER BY capture_time) AS change_since_previous
FROM dbo.TableRowCountHistory
WHERE database_name = N'YourDatabase'
AND schema_name = N'dbo'
AND table_name = N'YourTable'
ORDER BY capture_time;Account for Deletes and Rebuilds
Row counts can fall after retention cleanup, partition switching, or application changes. A decrease deserves the same owner check as an increase. Metadata counts can lag transactionally during active workloads, so compare repeated snapshots before declaring data loss. Index rebuilds and partition operations can also change the timing of metadata observations.
I ask whether a scheduled load or purge ran near the capture time. A trend is a conversation starter. It does not tell you whether rows were valid, duplicated, or deleted under policy. If the count changed unexpectedly, use targeted queries and application logs to verify the affected data. Do not launch a broad COUNT_BIG across every large table during peak hours.
Retain Enough History When Recording Table Row Counts
Keep daily raw snapshots long enough to cover seasonal and quarterly cycles. Define a retention period and an archival rule. The history table itself will grow, so index it for the queries you actually run and monitor its size. Avoid keeping dozens of redundant snapshots per day unless the workload needs that detail.
I have seen monitoring tables become the next growth problem. That has a certain symmetry, but little charm. Use daily granularity for a daily question. If a table changes rapidly within hours, add a separate targeted capture for that table rather than increasing frequency for the whole estate.
Add a Useful Alert
An alert should identify the table, previous and current capture, change, and owner. Require consecutive evidence or a reviewed threshold to avoid paging on known bulk loads. Keep expected events in a calendar or exception table. A row-count jump with a known import is information; an unexplained jump in a sensitive table is an investigation.
I send a short exception list rather than a full inventory. The owner should know which table to inspect and what time window to review. If the collector failed, alert on the missing snapshot too. No row can mean no change, or it can mean no collection. The job outcome settles that distinction.
Connect Counts to Capacity
Row count is not storage size. A table with wide rows, LOB values, or indexes can use far more space than another table with the same count. Pair the count trend with file allocation and object size when planning capacity. Use the count to find which business entity is expanding, then measure the bytes from the actual server.
What new data should the application have created yesterday? Ask that question before calling a jump abnormal. Recording table row counts daily gives you a date and object to investigate. Its real value is making a change visible while the explanation is still fresh.
Related reading on this blog: List Tables with Size and Row Counts: Part 2 and Fastest Way to Retrieve Rowcount for a Table: SQL in Sixty Seconds #096.
A row-count trend is not proof of data quality, it is an early signal that a table changed.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
