Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2003-05-05 : 07:31:58
|
| Hello ,I want to have a mechanism on SQL Servers where I can get notifications on the database growth . Suppose if the database growth reaches 75 % then is it possible by any means that SQL server can trigger a message to me ? Is it possible through stored procedure or Enterprise manager ? Thank you . |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-05-05 : 08:37:35
|
| You can get advantage of SQL Alerts.SQL Server Agent ---> Alerts.Just define a new alert, give it a name and then choose:Type = Sql server performance condition alertObject = SQL Server:DatabasesCounter = Data File(s)Size (KB)Instance: Your DatabaseAlert if counter = rises above ---> Your value.In the response tab choose to have a net send message to an operator that you have previously created.HTHFranco |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-05-05 : 10:17:17
|
| I choose to write a stored procedure that performs file growth notification. The first time the procedure runs, it records all db file sizes in a table. Each subsequent time it runs it compares the current size to the last recorded size. If there is a variation, the stored procedure notifies you of the database name along with the new and old sizes. The proc is scheduled to run every hour on all production servers. I choose to do this instead of SQL Alerts because the alerts require constant management. If a db grows past your specified value, you get an alert, but then you have to modify the alert and set a new threshold. Plus you need to have a seperate alert for each db. For some shops, this is maintainable, for others it is not.Jeff Banschbach, MCDBA |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-05 : 16:39:04
|
| I'm not sure either of those answers the question (at least as I understand it). I think they want to know before SQL Automaically grows the file. I think this is a good strategy, because you don't want SQL auto growing your files during busy times.I have never set up any kind of alerts like this, but I imagine you could do something similar to what Jeff suggested, but use sp_spaceused in there somewhere to get an alert when you are getting close to a grow.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-05-06 : 01:09:21
|
| Thanks a lot friends for your valuable suggestions .This gives me a fair idea on how to proceed and how to customise the stored procedure or even write a trigger for the same . Notifications through alerts could be also a good option depends upon the scope of manageability . Thanks once again . |
|
|
|
|
|
|
|
|
Monitoring Database growth .