Monitor Database Size using SQL-Server

Inevitably, your database will increase in size. The files in a SQL Server database can be configured to grow automatically or configured to require a DBA to manually increase the size. Even when the files used in a database can automatically grow, it is still useful to know how much space is in use and which objects are using the space. Two stored procedures are useful when monitoring the size of a database.

1. sp_spaceused
2. sp_helpdb

The sp_spaceused stored procedure is used to either report information on all tables in the current database, or to report on individual tables. The syntax of sp_spaceused is:

sp_spaceused [ [ @objname = ] table_name 
[,[@updateusage =] ‘TRUE|FALSE’] ]

When running sp_spaceused, if you do not specify a table or set the table name to NULL, you will receive a summary report for the complete database.
You can also use sp_spaceused to report on individual tables by setting the @objname parameter. If the @updateusage parameter is TRUE, sp_spaceused will make sure that the information it is using to generate its output is correct.

The stored procedure sp_helpdb is used to display information about a database and all of its files. The syntax is as follows:

sp_helpdb [[@db_name=] database_name]

If you run sp_helpdb without any parameters, it will provide a one-line list for every database. This list will show the total size for all files used in the database. If you pass in a database name, it will return the one-line list for that database, and you will also get a line for each file in the database containing the file size along with other file information. The next lesson will cover how to monitor a database size with SQL-EM.