Share via

OpsMgr 2007: Disk space monitoring of SQL DB file does not trigger an alert if DB is set to Autogrow

  • Article

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

Storage Top 10 Best Practices

J.C. Hornbeck | Manageability Knowledge Engineer

Comments

  • Anonymous
    January 01, 2003
    thank you
  • Anonymous
    January 01, 2003
    FW: OpsMgr 2007: Disk space monitoring of SQL DB file does not trigger an alert if DB is set to Autogrow