October 17, 2011 at 1:01 pm
Hi,
I would like to find the below:
1. Daily growth rate for the Physical drives
2. Daily growth for the database files
Basically, I want to have all data in an excel sheet every day and want to just refresh the excel sheet and see the growth pattern when ever we want to take look at the growth pattern.
What are best possible options (scripts) available to achieve this
Thanks
October 17, 2011 at 4:21 pm
Couple of things. And I'm totally not trying to be a smart alec.
1. Your physical drives grow... daily? Or are you speaking to overall data consumption rates of the physical (i.e. logical drives) present in the Windows environment? If the latter is your case, your speaking of monitoring a Windows object, not a SQL object, so you're limited to perfmon or something more robust such as System Center Operations Manager (SCOM) or Solarwinds (there are other options out there, these are just the two I am most familiar with). If it's to be perfmon you can hop in, set up your counters of concern and dump the trace to a file that you should be able to manipulate into Excel.
2. Let me introduce you to a fellow called sys.dm_os_performance_counters. He's a dynamic management view introduced back in SQL 2005. He's directly queryable and you can pipe the output into tables.
What I might suggest you do is investigate what it has to offer by experimenting with some SELECT statements to take a peek at the bevy of counter info it has to offer (from the SQL perspective, aka why it can't show you free space out on the drives). Surrounding your specific concern at the moment I would say...
Take a look at the counter object higherarchy:
SELECT distinct object_name FROM sys.dm_os_performance_counters
Then maybe take a peek at all of the counters in say... Databases:
SELECT * from sys.dm_os_performance_counters where object_name = '<insert your instance information here>:Databases'
(hint... you should see the info for <insert your instance information here> from the list of object names in the first query)
After that you can just build your WHERE around the counters you like, db's you like, etc...
Here's the part that I would suggest instead of just dumping it to Excel every day.
Once you find the counters you like, why not just dump them to a table with say a date stamp column up front? You say you'd like to review this data on a per-day rhythm, so this should work out nicely. Shedule this dump in a job that runs once a day at a time of your liking, then when you come in you can just run a SELECT against this aggregation table and WHERE on the dates, db's, counters as you like to get a historical perspective. You might also want to build an aging process into this job to where data say... older than month... gets deleted? Or say math is done on it to aggregate averages into another, longer term, historical look?
Quick Edit: I would also suggest placing these tables into a dedicated "DBA" database for this and other such home-grown monitoring tasks. Easy to keep track of everything, and who knows... it might just grow into a formal monitoring process (with the right documentation and usage).
October 17, 2011 at 5:31 pm
Which scripts have you tried to use thus far? Have you looked through the scripts section here on this site?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 17, 2011 at 8:02 pm
Thanks David..the information provided is very helpful
October 18, 2011 at 8:38 am
Never a problem. 🙂
October 19, 2011 at 4:34 am
SQLRNNR (10/17/2011)
Which scripts have you tried to use thus far? Have you looked through the scripts section here on this site?
There are a few useful scripts on this site that will satisfy what you are looking for.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply