Capturing database size on a schedule and graphing?

I'm wondering if there are any other programmers/DBA's out there that have
lots of databases that they need to routinely monitor its file growth over
time. I'm looking for any VB code or scripts that accomplish this.
We have about 500 SQL Server databases on one of our servers and they extend
daily.
I would like to capture their size and save the data so it can be graphed in
Excel or something to show the growth rate of each database.
If anyone has any sample code or idea how I can do some of this -- I would
appreciate it greatly.There are multiple ways you could do this.
One would be a scheduled job in SQL Server, which uses either the
undocumented sp_MSForEachDB or a cursor, loops through the databases, and
logs the result of sp_helpfile. This can be useful if you want to leave out
irrelevant databases using the where clause for the cursor or an if
conditional.
Another way would be a windows scheduled task that calls a VBS script, using
FileSystemObject to loop through all the MDF/NDF files and logs their size
property. This can be useful if all of your relevant databases are in a
specific location, separate from the system databases and/or other databases
you are not interested in logging.
If you can wait a day or two, I will whip something up that should be a bit
more concrete than the above... in the meantime, you could take a crack at
it, and post here if you have specific issues.
Let's narrow the discussion groups down though, okay?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DavidM" <spam@.spam.net> wrote in message
news:uBE#1Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
extend
> daily.
> I would like to capture their size and save the data so it can be graphed
in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||Hi
You may want to check out the code in sp_spaceused and adapt it to suit your
purposes.
John
"DavidM" <spam@.spam.net> wrote in message
news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
> extend daily.
> I would like to capture their size and save the data so it can be graphed
> in Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||...also sp_databases would probably do this if you don't want to split up
log and data files.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eR1GFfb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi
> You may want to check out the code in sp_spaceused and adapt it to suit
> your purposes.
> John
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>> I'm wondering if there are any other programmers/DBA's out there that
>> have lots of databases that they need to routinely monitor its file
>> growth over time. I'm looking for any VB code or scripts that accomplish
>> this.
>> We have about 500 SQL Server databases on one of our servers and they
>> extend daily.
>> I would like to capture their size and save the data so it can be graphed
>> in Excel or something to show the growth rate of each database.
>> If anyone has any sample code or idea how I can do some of this -- I
>> would appreciate it greatly.
>>
>|||There are some code can be used on this web site:
http://www.sqlservercentral.com/scripts/contributions/31.asp
I tried it, seems very nice.
Good luck
"DavidM" <spam@.spam.net> wrote in message
news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they
extend
> daily.
> I would like to capture their size and save the data so it can be graphed
in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||If I run a query on the .sysfiles table, the size column shows 1704. Is
this in pages? How do I convert to bytes or megabytes?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eR1GFfb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi
> You may want to check out the code in sp_spaceused and adapt it to suit
> your purposes.
> John
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE%231Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>> I'm wondering if there are any other programmers/DBA's out there that
>> have lots of databases that they need to routinely monitor its file
>> growth over time. I'm looking for any VB code or scripts that accomplish
>> this.
>> We have about 500 SQL Server databases on one of our servers and they
>> extend daily.
>> I would like to capture their size and save the data so it can be graphed
>> in Excel or something to show the growth rate of each database.
>> If anyone has any sample code or idea how I can do some of this -- I
>> would appreciate it greatly.
>>
>|||> If I run a query on the .sysfiles table, the size column shows 1704. Is
> this in pages? How do I convert to bytes or megabytes?
SELECT
[Filename],
[SIZE IN KB] = size*8
FROM sysfiles|||Thanks for the reply. I was able to find
http://www.databasejournal.com/features/mssql/article.php/3339681 which
looks promising.
I got this to work but its a bit kludgy. Since I have a VB application that
we run daily, I'd like to incorporate the collection of stats within this
program.
Next question is, what is the best way to graph this data using Excel? Can
I have Excel read the database/table directory from SQL? If so, that is
what I want to do rather than create a .CSV file from SQL.
Opinions?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OG7fWJb7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> There are multiple ways you could do this.
> One would be a scheduled job in SQL Server, which uses either the
> undocumented sp_MSForEachDB or a cursor, loops through the databases, and
> logs the result of sp_helpfile. This can be useful if you want to leave
> out
> irrelevant databases using the where clause for the cursor or an if
> conditional.
> Another way would be a windows scheduled task that calls a VBS script,
> using
> FileSystemObject to loop through all the MDF/NDF files and logs their size
> property. This can be useful if all of your relevant databases are in a
> specific location, separate from the system databases and/or other
> databases
> you are not interested in logging.
> If you can wait a day or two, I will whip something up that should be a
> bit
> more concrete than the above... in the meantime, you could take a crack at
> it, and post here if you have specific issues.
> Let's narrow the discussion groups down though, okay?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "DavidM" <spam@.spam.net> wrote in message
> news:uBE#1Bb7EHA.2804@.TK2MSFTNGP15.phx.gbl...
>> I'm wondering if there are any other programmers/DBA's out there that
>> have
>> lots of databases that they need to routinely monitor its file growth
>> over
>> time. I'm looking for any VB code or scripts that accomplish this.
>> We have about 500 SQL Server databases on one of our servers and they
> extend
>> daily.
>> I would like to capture their size and save the data so it can be graphed
> in
>> Excel or something to show the growth rate of each database.
>> If anyone has any sample code or idea how I can do some of this -- I
>> would
>> appreciate it greatly.
>>
>|||> Next question is, what is the best way to graph this data using Excel?
Have you considered using Reporting Services?|||or you might wish to setup a sql agent job to run at the end of each business
day to collect the information and popuate a base table
-- try the simple but useful approach below
/** the following can be quite useful **/
select instance_name,cntr_value 'Size in (Kb)' from
master..sysperfinfo(nolock)
where object_name like '%databases%'
and counter_name = 'Data File(s) size (Kb)'
and instance_name not in ('_total') -- can include total to get a server
based overview
"DavidM" wrote:
> I'm wondering if there are any other programmers/DBA's out there that have
> lots of databases that they need to routinely monitor its file growth over
> time. I'm looking for any VB code or scripts that accomplish this.
> We have about 500 SQL Server databases on one of our servers and they extend
> daily.
> I would like to capture their size and save the data so it can be graphed in
> Excel or something to show the growth rate of each database.
> If anyone has any sample code or idea how I can do some of this -- I would
> appreciate it greatly.
>
>|||Is that something I have to buy?
I'm on a zero budget and I need something quick to monitor all my databases.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OJ7n$Uc7EHA.2676@.TK2MSFTNGP12.phx.gbl...
>> Next question is, what is the best way to graph this data using Excel?
> Have you considered using Reporting Services?
>|||>> Have you considered using Reporting Services?
> Is that something I have to buy?
No. If memory servers, it comes with the license of SQL Server.|||"DavidM" <spam@.spam.net> wrote in message
news:OtfnEne7EHA.2516@.TK2MSFTNGP09.phx.gbl...
> Is that something I have to buy?
> I'm on a zero budget and I need something quick to monitor all my
databases.
Nope.
Go to www.microsoft.com and you'll find it there.
I've only started to play with it, but the SQL Reports you can download for
it are cool.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OJ7n$Uc7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> >> Next question is, what is the best way to graph this data using Excel?
> >
> > Have you considered using Reporting Services?
> >
> >
>|||Can you give me exact URL. I'm not sure what product or component your
referring to and I cannot seem to find anything on MS website.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:CPHAd.79614$Uf.15857@.twister.nyroc.rr.com...
> "DavidM" <spam@.spam.net> wrote in message
> news:OtfnEne7EHA.2516@.TK2MSFTNGP09.phx.gbl...
>> Is that something I have to buy?
>> I'm on a zero budget and I need something quick to monitor all my
> databases.
> Nope.
> Go to www.microsoft.com and you'll find it there.
> I've only started to play with it, but the SQL Reports you can download
> for
> it are cool.
>
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:OJ7n$Uc7EHA.2676@.TK2MSFTNGP12.phx.gbl...
>> >> Next question is, what is the best way to graph this data using Excel?
>> >
>> > Have you considered using Reporting Services?
>> >
>> >
>>
>|||> Can you give me exact URL. I'm not sure what product or component your
> referring to and I cannot seem to find anything on MS website.
Geez, did you try using the little "search" tool? It's on the top right of
the page, in case you ever need anything from microsoft.com again...
http://www.microsoft.com/sql/reporting/default.asp
--
http://www.aspfaq.com/
(Reverse address to reply.)