Report to Capture Table Growth size Statistics for SQL Server on daily basis..

Question

Report to Capture Table Growth size Statistics for SQL Server on daily basis..

Database admin(DBA)

SSCertifiable

Points: 5373
More actions
March 7, 2017 at 1:52 pm

#341462

Hi All,
Need your help on Report to Capture Table Growth size Statistics for SQL Server,i would like to implement this one from our monitoring server using power shell script and input as server.stxt.
we need to capture this information at least 6 months for table size growth details information.
Can you please suggest me on how to proceed further
Thanks in advance!!
Best regards,
SQL server DBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Database admin(DBA) - Tuesday, March 7, 2017 1:52 PM
Hi All,
Need your help on Report to Capture Table Growth size Statistics for SQL Server,i would like to implement this one from our monitoring server using power shell script and input as server.stxt.
we need to capture this information at least 6 months for table size growth details information.
Can you please suggest me on how to proceed further
Thanks in advance!!
Best regards,

I'd probably want to start by defining it further such as Growth in size being row count or space used? And do you need some kind of intervals or do you just capture the information you have today and then capture it again in six months? Or per month, per week?
Are there tables to exclude such as staging tables or tables that are regularly truncated for some reason?
Where and how do you need to store the information - in a database, in files, on the monitor server or another server?
There are no right or wrongs on any of those, just a few things to maybe think about and solidify.

Sue

Ricky Valencia SSCommitted Points: 1907 More actions · Answer 2

Hi

How about creating a SQL Agent Job to capture Table Sizes and output as .txt file using the T-sql script provided in the link below.

http://sqlconjuror.com/sql-server-get-table-sizes/

And then create a powershell script that would grab the text file and send it via email. The procedure on how to create the powershell script is outlined in the link below

http://sqlconjuror.com/powershell-send-smtp-email-and-add-message-body-sent-from-script-using-powershell/

I Hope this helps.

Thank you.

Best Regards,
Ricky

joeroshan SSChampion Points: 10377 More actions · Answer 3

I use the following query to monitor my tables

Select SCHEMA_NAME(t.schema_id) SchemaName,t.name,s.rows,p.reserved_page_count*8.0/1024 SizeMB from sys.partitions S join sys.tables t on s.object_id=t.object_id and s.index_id in (0,1) join [sys].[dm_db_partition_stats] P on p.object_id=t.object_id and p.index_id=s.index_id order by reserved_page_count desc

You might want to persist this to a table to monitor the trend over a period of time

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]