Reducing the Size of a Reporting Database

The reporting database is growing, and a shrink command is tempting. Reducing the size starts by finding which data and indexes earn their space.

Measure What Occupies Space Before Reducing Size

Start with data and log file sizes, then look at tables and indexes. A large file is not the same as a large table. It can contain free space reserved for future growth. A large log has a different cause and remedy from a large reporting fact table.

I take a baseline before changing anything. Record file size, used pages, table row counts, index size, and expected growth. Compare with prior snapshots when available. A one day jump suggests a load or maintenance event. Steady growth suggests retention or capacity planning.

Ask what space you need to reclaim: storage billing, backup time, refresh time, or query memory? Each points to different work. A shrink can return file space to the operating system while leaving the same rows and indexes to grow again.

SELECT name, type_desc, size * 8.0 / 1024 AS allocated_mb
FROM sys.database_files
ORDER BY type_desc, name;

Reducing the Size With Compression Where It Fits

Row and page compression can reduce rowstore storage. Columnstore can be effective for large analytic scans, but it changes load and query behavior. Check feature and edition support for the instance, then estimate savings and test representative reports. Compression can increase CPU work even while it reduces I/O.

Use sys.sp_estimate_data_compression_savings on candidate tables or indexes as an estimate, not a promise. Test the actual change on a copy or partition, then compare size, load time, and query plans. A narrow lookup table is unlikely to repay a complex rebuild.

I do not compress everything because the option exists. Start with the biggest stable tables, especially older partitions that are read more than written. Keep the measurement alongside the decision so the next DBA knows why the setting is there.

EXEC sys.sp_estimate_data_compression_savings
     @schema_name = N'dbo',
     @object_name = N'FactSales',
     @index_id = 1,
     @partition_number = NULL,
     @data_compression = N'PAGE';

Archive Data the Reports No Longer Need

Retention is a business rule. Decide which periods must remain queryable in the reporting database and which can move to an archive. Keep archive retrieval and restore procedures documented. Deleting old rows without a recovery path is not a storage strategy.

Partitioning by date can help move whole old ranges when the schema and indexes support switching. For smaller tables, delete in controlled batches and monitor log growth. Either way, preserve control totals and a manifest of archived periods. A report should not silently show fewer years after the change.

I ask report owners for the oldest date they actually use and the oldest date they are required to keep. Those answers can differ. Reducing the size should not depend on guessing from a dashboard’s current default filter.

SELECT MIN(SalesDate) AS oldest_date,
       MAX(SalesDate) AS newest_date,
       COUNT_BIG(*) AS rows_present
FROM dbo.FactSales;

Review Indexes Before Dropping Them

Indexes consume space and increase load maintenance. Review usage statistics and query plans to find candidates that duplicate other indexes or receive writes without helpful reads. Usage counters reset after events such as restarts, so a zero read count is not proof that an index is unused.

Check unique constraints, primary keys, foreign key support, and infrequent reports before dropping anything. A monthly close query can be important despite little weekly usage. Document the index definition so it can be restored if needed. Test load and report performance after each removal.

I compare index keys and includes, not just names. Two indexes with overlapping leading columns can sometimes be consolidated, but one can support an ORDER BY or selective predicate the other does not. The plan evidence decides.

SELECT i.name, i.index_id, ps.used_page_count,
       us.user_seeks, us.user_scans, us.user_updates
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS ps
  ON ps.object_id = i.object_id AND ps.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
  ON us.database_id = DB_ID()
 AND us.object_id = i.object_id
 AND us.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.FactSales')
ORDER BY ps.used_page_count DESC;

Fix the Load That Keeps Recreating Bloat

A reporting table can grow because each refresh appends duplicate periods instead of replacing them. Check business keys and run receipts. If a load is replayed after failure, it should not double facts. Fix that behavior before compressing the duplicates. Otherwise the next load recreates the problem.

Large intermediate staging tables can remain after a failed run. Give them a cleanup rule tied to run status and retention. Do not drop a stage still needed for recovery. Measure tempdb and log pressure separately from the reporting database file.

I check the top growth objects across several snapshots. A single snapshot tells me where space is now. A trend tells me which process is driving it. The right fix can be a better retention rule, not a file command.

Shrink Only After a Real Cleanup

Shrinking a data file can create index fragmentation and repeated growth if the working set still needs the space. Shrink only after a one-time data removal or storage move has created durable free space that the database will not soon reuse. Choose a target with room for expected growth.

Log shrink decisions separately from log backup and log reuse questions. A transaction log that cannot reuse space needs its cause resolved first. Shrinking without fixing a long transaction or backup gap can lead to immediate regrowth.

I plan a maintenance window and compare file size, free space, index state, and query behavior afterward. The goal is to return genuinely unneeded capacity. A database file with useful free space is not a moral failure. It is sometimes the cheapest growth plan.

Verify the Result of Reducing the Size

After compression or archiving, run the same report totals and key checks as before. Compare the intended date range and grain. A smaller database that omits required facts is not an improvement. Keep a restore point and a written rollback path for structural changes.

Review query plans and load duration under representative workload. Compression can change costs. An index removal can help loads but hurt a monthly report. Use evidence from both sides before keeping the change.

Space work is complete when the data contract still holds and the growth trend is understood. Find the large objects, remove data only under an approved retention rule, and make shrink the final targeted step if file space truly needs to leave the database.

Check backup size and duration after a storage change, but do not assume a smaller data file means the same proportional backup saving. Compression, backup settings, and changed pages affect the result. Use the next real backup evidence.

Related reading on this blog: Manage Database Size with DBCC SHRINKDATABASE and WAIT_AT_LOW_PRIORITY and Script to Estimate Compression.

Reducing database size is not squeezing every file, it is removing or storing each byte for a reason.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.