Monitoring SQL data growth

Symptoms : Total database size increased and is now larger than the designated threshold.

Impact : Medium

Rapid database growth may be a sign of some pending problem, such as a loop inside some executing code that is inserting large numbers of records incorrectly, etc.

Expected behavior :

There is no standard metric for database growth. This alert indicates unexpected behavior and needs to be investigated.

Possible causes

Incorrect Coding  Priority : High
If the sudden increase is noted during normal operations, it may be an indication that a query is inserting new data in an invalid manner – possibly a loop.
Recommended action :
Examine all currently running queries, especially ones that appear to have remained active for unexpected lengths. Investigate the code of the query, and if necessary terminate.

DBREINDEX procedure is running  Priority : Medium
Reindexing can increase the size of database. The reason for this is that when you rebuild an index, there must be enough empty room in the database to create a copy of the largest table in the database as the index rebuilding occurs.
Recommended action :
This is a normal effect of reindexing, but good practice is for the DBCC DBREINDEX process to be run at times when normal activity is lowest. The reindexing process should be followed by the process to shrink the database (DBCC SHRINKFILE). Normal database size should be seen after that has finished.

Index corruption  Priority : Medium
Recommended action :
Run procedures DBCC DBREINDEX followed by DBCC SHRINKFILE

Background

This metric is intended to identify any case where the database file has grown to be bigger than expected. The threshold sets a level beyond which file size growth triggers a warning. Database file size is highly dependent on level of activity, and the overall health of the database. The trigger levels should be set in accordance with past behavior, and also take into account capacity levels to absorb such growth.