SQLTeam.com | Weblogs | Forums

T-SQL needed for datagrowth

SQL Team,

I am looking for T-SQL to find out the monthly database growth. If any one have this information please share with me.

Thanks in advance

If you just need the size of the database, you can query:

select database_name, backup_finish_date, backup_size from msdb.dbo.backupset where type='D';

That will give you the size of the full backups made of the database. If you need individual tables:

select getdate() current_date, s.name table_schema, t.name table_name, p.*
from sys.schemas s
inner join sys.tables t on s.schema_id=t.schema_id
inner join sys.dm_db_partition_stats p on t.object_id=p.object_id

That will list each table and it's data and index sizes, plus row counts. You may not need all the columns. Also know that space is listed by number of 8 KB pages, if you want bytes you need to multiply by 8192.

Note that neither query shows growth, you'd have to query regularly and store results in a separate table, and compare data across multiple dates to determine growth.

Robert,

Thank You.

You have T-SQL that will give us to track monthly growth of databases. If so please share.