Managing Database Space Usage

Although you can set databases to grow automatically and to shrink automatically, most of the time you want to monitor database size and manage it yourself. In addition, even when files are allowed to grow automatically, there is the potential of running out of disk space. Overhead is associated with automatically growing databases, and even more associated with automatically shrinking them. You want to leave the autogrow options set for each file, however, so that databases do not shut down because they are out of space. In general, if the data files are nearing 90% full, and you anticipate further additions of data, you want to consider increasing the amount of space available to the database. It is harder to give a figure for the Transaction log, because it tends to be somewhat "self-cleaning" (as discussed in Chapter 6). You can monitor how much space is used in a database or any given file with Enterprise Manager, Transact-SQL, and DMO. The following sections tell you how to monitor database space.

Monitoring Space with Transact-SQL

To find out how much space is allocated for a file, as well as other attributes of the file, make sure that the database you are concerned about is selected in the drop-down list in Query Analyzer, and then use the following command: