Schema modified

| 5 Comments | 7,992 views
Categories:

VN:F [1.9.17_1161]
Rating: 2.0/5 (1 vote cast)

This metric is not important in its own right, and viewing the data points on the Analysis page will not provide useful information. The metric is a necessary step in order to achieve the key purpose of this metric, which is to create an alert that will be raised when something is added to the schema, or the existing schema is modified.

Note: If you delete a schema object such as a table, constraint or index, an alert is not raised.

Schema modified, 2.0 out of 5 based on 1 rating

Metric definition

Metric Name:
Schema modified
Description:
Spikes indicate that something has been added to the database schema, or the existing schema has been modified. A spike will not occur if a schema object is deleted. For more information, see http://msdn.microsoft.com/en-gb/library/ms190324.aspx.
Enter the T-SQL query that will collect data:
SELECT DATEDIFF(ss, '1970', MAX([modify_date]))
  FROM [sys].[objects];
Select instances to collect from:
the relevant instance
Choose databases to collect from:
Specify databases and type
the database you're interested in
Set collection frequency:
Collect data every 30 minutes
Use collected or calculated values:
Turn on the Use a calculated rate of change between collections check box
Metric collection is:
Enabled

Alert definition

Alert name:
Schema modified
Description:
Raised when the schema of this database is modified. If this change was not authorized, you may want to investigate further. Note: If you delete a schema object such as a table, constraint or index, an alert is not raised.
Raise an alert when the metric value goes:
above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:Turn off check box
Medium:0
Low:Turn off check box
Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 2.0/5 (1 vote cast)
  •  This will not do deletes or renames. The only way it would detect a delete is if the deleted object just happened to be the latest object modified. Also not every part of the database metadata is in the sys.objects table, or will flag an increase in the modified_date

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Eelco Drost

    combine this with a dml trigger on the database and this would also be valueable for deletes

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Eelco Drost

    Sorry, DDL trigger, that is 🙂

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Pingback: Quick Schema Auditing | Voice of the DBA()

  • Matt Laffoon

    The modify_date changes anytime a clustered index is rebuilt so if you’re trying to track down a schema change you could chase your tail a lot if you are rebuilding indexes frequently.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)