Monitor Data and Lof Files sizes once every 2 hours 1

[topub]

Hi All,

I searched previous posts, but couldn't find solution to my current scenario.

We have about 80 SQL Server Instances on about 50 Boxes. Equal percentages of 2000 & 2005. Total of about 490 DBs

I need:

1: A way to identify the pattern of size increase [both TRN log and Data file] in each database over time. At the end of each month, I need to generate a Growth Graph [GG] of all the DBs for the Management to consider purchasing more space. I have a DB called "DBA" to store necessary information that I get, after performing calculations on the results from commands like "DBCC SQLPERF(LOGSPACE)" and "DBCC SHOWFILESTATS". This "DBA"-DB also has a bunch of tables that store information about location and login details of all the 490 DBs.

So I need to build a process that queries all the DB's, performs calculations and then inserts into a table in "DBA"-database. Since this process must talk to all the DBs, both SQL2000 & SQL2005, I am thinking of either a Windows Service Process or a VB exe file that runs from Scheduler.

Question: I need your suggestions or lessons learned from you guys, before I start this project.


2: Also, when log files comes close to full, an alert must be sent to a bunch of folks [e-mail, SMS, etc]. For SQL2005 I could use alerts, what about SQL2000. Ideally, I'd like to have one process do all of this, instead of having over 100 alerts.

Any suggestions are greatly appreciated,
_ub

 

[mutley1]

Hi Topub,

For the warning when a log is becoming full, you can use alerts in SQL 2000 as well. Create a new alert:

type = SQ Lserver performance condition alert
object = SQL Server Databases
counter = Percent Log Used
Instance = whatever DB you want
Alert if counter = rises above
Value = whatever % you want.

As for size monitoring, I posted a script before under thread

http://www.tek-tips.com/viewthread.cfm?qid=1316443

so have a look at the entry on 28 DEC '06 05:20. Will give you a good start point. We have this on every server and out monitoring server pulls the info in on a daily basis. It does truncate the table each time it runs, but if you have a play I'm sure you can get something to work out history / growth etc.

HTH,

M.

 

[topub]

Thanks a lot Mutley1. I have a procedure close to your script. But for this to work, I'd have to run this procedure from each instance and populate records into a "DBA"-database created in all the 80 instances, which is not an easy task.

Ideally, I'd like to have one process that does it all, if possible.

Any suggestions are welcome,

thanks again Mutley....

_ub

 

[mutley1]

Hi Topub,

As I say, we have a monitoring server and it pulls everything in via a DTS package, The best way to do it is create a holding table on the main monitoring server (DBA) and pull the data in to that table.

HTH.

M.

 

[topub]

I am not sure I understand what you say. But I'll try to make it work for us.
thanks,
_ub

 

[mutley1]

We have 40 servers and 1 server solely for monitoring those 40. I loaded the stored proc on each of the 40 servers and it runs at 6am to populate the table. Then at 7am, a job on the monitoring server collects all the data into a main Holding Table and spits it out into Excel. I know its not the best design but I am looking to change it. I'm planning on creating a new package that basically connects to the server, runs the stored proc, copies the data to the main table then move on to the next server etc.etc.etc.

Cheers,

M.

 

[topub]

Thanks for the extra explanation...

Thats exactly what I thought..
Have one process that talks to all the servers, and stores in one database [DBA-db].

For now, I am working on a VB.Net EXE process to run on a schedule.

Let me know, if you want to share or exchange any ideas,
_ub