OpsMgr 2007: Disk space monitoring of SQL DB file does not trigger an alert if DB is set to Autogrow
Here's an interesting issue that was sent to me by Rohit Kaul on our OpsMgr 2007 team. This one concerns an issue where the disk space monitoring of a SQL database file does not trigger an alert if the database is set to Autogrow:
========
Issue: The disk space monitor for a SQL database file may not trigger an alert in Operations Manager admin console:
Monitor Name: Db space free
Default Tareget: SQL DB FILE
Cause: This can occur if the database to be monitored is set to Autogrow. This is by design.
Resolution: The only way to use triggers for SQL file size monitoring is to disable autogrow on the database in question. To do this follow these steps:
1. Open SQL Management Studio if using SQL 2005 servers - SQL Enterprise Manager for SQL 2000 servers.
2. Expand SQL INSTANCE---Databases--choose the DB for which an alert is not getting triggered.
3. Right Click DB and choose properties and click FILE --under AUTOGROWTH COLUMN--click on Browse Button and uncheck ENABLE Autogrowth--click OK.
4. Re-cycle the Health service on the targeted server [SQL server in this case].
5. Refresh your OpsMgr console and check for disk space alerts for SQL DB FILE.
Note: Alerts may not show up until the configuration has been completely updated. To verify this check for event IDs 1201 and 1210 on the targeted server.
========
Thanks Rohit, and before you make any changes be sure you fully understand the implications of having autogrow disabled. For more information see the following:
KB315512 - Considerations for the "autogrow" and "autoshrink" settings in SQL Server
J.C. Hornbeck | Manageability Knowledge Engineer