8

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.

  • You can use a combination of T-SQL and PowerShell - Monitoring Table Growth with PowerShell to monitor and trend it over time. – Kin Shah May 29 '15 at 19:03
  • How far back do you have backups for? You can restore the older ones and look at the tables, record the info and compare that way after restoring each. – Ali Razeghi May 29 '15 at 19:22
  • What kind of information do you have in your tables? If it happens to include auditing-type-info, like a last_updated_at timestamp, you could make some good educated guesses. – Clockwork-Muse May 30 '15 at 14:01
  • You can check this previous answer for a starter. But most likely Aaron's list is the way to go, pick a solution and build onto it. Also, check what's growing, is it the data file or it may be the log file? – Marian Jun 1 '15 at 12:13
15

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.

13

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.

RightClick

2

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
1

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.

  • 5
    Why not share your script? – Erik Darling Jan 25 '18 at 17:45
-2

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.