Monitoring specific tables daily

Question

Monitoring specific tables daily

frank.pron

SSC Enthusiast

Points: 109
More actions
January 22, 2009 at 6:42 am

#199094

I have a specific sql table that I need to monitor its size on a daily basis. What would be the recommendation and how would I do this?

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

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

A lot of it depends what you want to do with the data... You could use sp_spaceused inside of a SQL agent job and write it to a table, log file or some other medium that you could refer to, to see the size over time.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

frank.pron SSC Enthusiast Points: 109 More actions · Answer 2

Thanks for the reply!

Basically I just need to see how much this tables grows over a period of time?

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

So Using sp_SpaceUsed in a SQL Agent job won't work?

If you want to see it over time, then you'll have to log it to something, I'd suggest a table in some sort of auditing database if you have one.

Schedule your job to run every day at X time and then if you really want to get fancy, you could create some sort of Reporting Services Report (or even an Excel Chart linked to that table) on that data so that you can call it up on a weekly/monthly basis and get a nice graph showing your trending info.

Here's a quick and dirty sample of how you might store this data...

CREATE TABLE #temp (

TableName Varchar(30),

NumRowsINT,

Reserved VARCHAR(20),

Data varchar(20),

IndexSize VARCHAR(20),

UnUsed VARCHAR(20)

)

INSERT #temp

exec sp_spaceused cases

SELECT *

FROM [#temp]

DROP TABLE [#temp]

Obviously, you'd need to do something like capturing a datetime or something to ehlp with your trending and such, but you get the idea...

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

CrazyMan SSChampion Points: 12560 More actions · Answer 4

I would suggest the same as Luke, Create a table and then insert columns for 7 days and then update the columns for each day, this gives you a growth over the period of a week.

If you are using 2oo5 you can use performance Dashboard to monitor this on a daily basics, but this is bit of a manual work 😀