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
- 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.
If you haven't been capturing and storing those metrics somehow then there isn't a way to see history. However you can pretty easily see which tables are using the most disk now if that helps.
I run this daily on a customer database to track individual table growth over time.
DECLARE @names TABLE ( tableName VARCHAR(128) ) INSERT INTO @names SELECT [name] FROM customerdb.dbo.sysobjects WHERE xtype='U' WHILE (SELECT COUNT(tableName) FROM @names) > 0 BEGIN DECLARE @thisTable VARCHAR(128) SET @thisTable = (SELECT TOP 1 tableName FROM @names) INSERT INTO mydb.dbo.mytable ([name], [rows], [reserved], [data], [index_size], [unused]) EXEC sp_spaceused @objname=@thisTable DELETE FROM @names WHERE tableName = @thisTable END
I Have a SQL script schedule on a job to run every week. Try writing a script that gets all the info from sp_spaceused, sys.schemas and sys.tables. and Put that in a table with the created on date. (and you can start monitoring for the future)
But If you want to find out the table growth for previous months and if you did not set up any script or job, you would probably need to restore the back up in a test environment and look at the highest table and compare how much it grew through time.
How i do it in MySQL without extra tools:
Run a query like "SHOW TABLE STATUS" and save the result in a table with an extra timestamp field.
With this data you have a history of each table and can pick your tables for data length, rows or biggest diff. over time.
As the status data isn't much bytes you can run it with a daily cronjob. Runs perfect, easy and for free with onboard commands.
Same procedure like the link Marian posted before. The MS T-SQL command should be something like "SELECT * FROM INFORMATION_SCHEMA.TABLES" - just google. I found 2 possible solutions in seconds:
Listing all tables in a database and their row counts and sizes: http://www.sqlmatters.com/Articles/Listing%20all%20tables%20in%20a%20database%20and%20their%20row%20counts%20and%20sizes.aspx
SQL SERVER – Query to find number Rows, Columns, ByteSize for each table: http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/