Sql-server – How to determine which tables are causing the database to grow over time

sql serversql-server-2008

I have a database in SQL Server 2008 that is growing all the time, and I'd like to know which tables have caused the most growth over the past 6 months.

Best Answer

SQL Server doesn't track this information. You'd need some kind of monitoring (whether it be a tool or home-grown) that keeps snapshots of table space over time, going forward. This can be simple or complex:

  • Viewing the report @Zane highlights in his answer, and saving the file every so often;
  • Taking your own periodic snapshots of views like sys.dm_db_partition_stats;
  • Looking at restored copies of older backups, as suggested by @Ali - however who knows how far your backups go, and if disk space is an issue, you might not have the luxury of restoring a bunch of them for side-by-side comparisons anyway.
  • Using an external tool like PowerShell to automate it, like the article @Kin points out;
  • Using a full-blown monitoring tool which tracks space, fragmentation, etc. over time (Disclaimer: I work for SQL Sentry).

Short of having already implemented one or more of these methods, or someone inventing a time machine, there's not really any good way to figure out how big a table was 6 months ago, or two weeks ago, or 20 minutes ago.

Related Question