How can I determine which tables are causing the database to grow over time?

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.

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.

StackExchange Database Administrators Q#102828, answer score: 17