02 July 2012

Database file size

by Redgate Monitor Team

12,406 0 Storage

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.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

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

Name

Rate of change of database file size in MB/hour

Description

Monitors the rate of change of the database file size.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			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)';
			
				
					
				
					1
2
3
4
5
6
7
8
9
				
						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)';

Instances to collect from

Select the instance that contains the database you’re interested in.

Databases to collect from

the database you're interested in

Collection frequency

1800

Use collected or calculated values

Check the Use a calculated rate of change between collections check box

Metric collection

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 threshholds

Default threshold values

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

1 collection

Alert is

Enabled

12,406 Rate

Click to rate this post!

[Total: 0 Average: 0]

Database file size

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics