Database file size

| 0 comments | 6,043 views
Categories:

VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)

It’s important to measure the growth of databases so you can plan future space requirements, prepare for time periods when heavy volume traffic is expected, and take action in advance to prevent problems such as highly fragmented databases. It should also help prevent multiple autogrowth events which can negatively affect performance.

The metric data collected can be analyzed over time, so you can calculate the database’s maximum file size in advance, and then configure autogrowth size accordingly.

For more information, see SQL Server Database Growth and Autogrowth Settings.

For details of maximum capacity specifications for SQL Server, see http://msdn.microsoft.com/en-us/library/ms143432.aspx

Metric definition

Metric Name:
Rate of change of database file size in MB/hour
Description:
Monitors the rate of change of the database file size.
Enter the T-SQL query that will collect data:
SELECT cntr_value
  FROM sys.dm_os_performance_counters
  WHERE [instance_name] = DB_NAME()
    AND object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL
                           THEN 'SQLServer'
                           ELSE 'MSSQL$'
                                + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)
                      END + ':Databases'
    AND [counter_name] = 'Data File(s) Size (KB)';
Select instances to collect from:
Select the instance that contains the database you’re interested in.
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:
Database file size increased
Description:
Raised when an increase in the database file size is detected. Automatic size increases to accommodate new data can quickly lead to highly fragmented databases and poor query performance. To avoid automatic increases, a database's maximum file size should be estimated in advance, and sufficient storage capacity should be allocated accordingly.
Raise an alert when the metric value goes:
above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:100
Medium:10
Low:1
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: 0.0/5 (0 votes cast)