Post reply

Monitor Table Grow

  • November 5, 2012 at 7:23 am

    #284155

    Hello

    I need to Monitor which table growing for Database.

    so please help me to do this.

    I have no idea how to monitor growth of the tables.

    Thanks

  • November 5, 2012 at 7:30 am

    #1556048

    Hi,

    you could use the script @:

    http://blogs.technet.com/b/mdegre/archive/2009/10/14/determining-sql-server-table-size.aspx

    Ensure that the data is stored in a persistent table instead of a #temp table.

    Then you can also setup a SQL job to run the stored procedure at a set interval, e.g. every morning, which will append tot he table (add a date column to the table).

    And finally you could create a small SSRS report against that table to view how the table(s) are growing over time.

    HTH,

    B

  • November 5, 2012 at 7:30 am

    #1556049

    Something like the following would work

    create table tablemonitor (name sysname, rows bigint, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100), datecollected datetime default getdate())

    insert into tablemonitor (name, rows, reserved,data,index_size,unused) exec sp_msforeachtable 'sp_spaceused [?]'

    Wrap the insert into a SQL job to execute daily, then you can query the table and see which tables are growing day by day.

  • November 5, 2012 at 10:39 am

    #1556154

    Thanks

    This is Awesome, help me a lot.

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

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