Monitoring Databases  «Prev  Next»

Lesson 2 Monitor the size of a database with SQL
Objective Monitor the size of a database.

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

sp_spaceused

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.

sp_spaceused for Database

The following output is generated when running sp_spaceused without a table name parameter. 
database_name      database_size      unallocated space  

------------------ ------------------ ------------------ 

pubs               2.00 MB            0.73 MB
 
reserved      data           index_size     unused         

------------- -------------- --------------------------- 

1296 KB       472 KB         712 KB         112 KB

  1. database_name is the name of your current database.
  2. database_size is the total size of all files used in the database, include transaction log files.
  3. unallocated space is the total size that is not assigned, including all tables and indexes.
  4. reserved is the number of KB that is assigned to tables and indexes.
  5. data is the number of KB that has data.
  6. index_size is the number of KB that is used for index.
  7. unused is the number of KB that is assigned to data, but currently does not have any data stored on it.

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.

sp_spaceused for Database Table

The following output is generated when running sp_spaceused on a table. 
name       rows   reserved    data     index_size   unused 

---------- ------ ----------- -------- ------------ -------
Customer   230    48 KB       8 KB     40 KB        0 KB

  1. name is the table you are gathering information about.
  2. rows is the number of rows in the table.
  3. reserved is the total amount of space assigned to the table and its indexes.
  4. data is the amount of space used to store data.
  5. index_size is the amount of space used to store indexes.
  6. unused is the space assigned, but does not have any data stored on it.

sp_helpdb

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.

sp_helpdb: no parameters

Running sp_helpdb with no parameters produces a list like this:
exec sp_helpdb
go
name        db_size   owner  dbid   created        status
---------   -------  -----  -----  -----------  -----------------------------------------
master      9.25 MB     sa     1      Nov 13 2008    trunc. log on chkpt.
model       1.50 MB     sa     3      Sep 1 2009      select into/bulkcopy, trunc. log on chkpt.
msdb        9.25 MB     sa     4      Sep 1 2009      select into/bulkcopy, trunc. log on chkpt.
Northwind   3.94 MB   sa     6      Sep 1 2009      select into/bulkcopy, trunc. log on chkpt.
pubs        2.13 MB       sa     5      Sep 1 2009      select into/bulkcopy, trunc. log on chkpt.
tempdb     8.50 MB     sa     2      Sep 30 2009    select into/bulkcopy, trunc. log on chkpt.

The output of sp_helpdb contains the following information:
  1. name is the database name.
  2. db_size is the total size of all files for the database.
  3. owner is the database owner.
  4. created is the day the database was created.
  5. status is the status of the database.

sp_helpdb: with a Database Name


When you run sp_helpdb with a database name the following output is produced: 
exec sp_helpdb pubs
go
name  db_size owner dbid  created     status                                                                   
----- ------- ----- ----- ----------- ------------------------------------------
pubs  2.13 MB sa    5     Sep 1 2009  select into/bulkcopy, trunc. log on chkpt.


	
name  fileid filename                    filegroup  size    maxsize   growth usage     
----- ------ --------------------------- ---------- ------- --------- ------ --------- 
pubs     1   C:\MSSQL7\DATA\pubs.mdf     PRIMARY    1408 KB Unlimited 10%    data only
pubs_log 2   c:\mssql7\DATA\pubs_log.ldf NULL       768 KB  Unlimited 10%    log only

Besides the one-line list, you get a line for each file containing the following information:
  1. name is the files logical name.
  2. fileid is the file identifier.
  3. filename is the files physical name.
  4. filegroup is the filegroup that the file belongs to; if filegroup is null then it is a log file.
  5. size is the current size of the file.
  6. maxsize is the maximum size to which the file can grow.
  7. growth is the growth rate defined for the file.
  8. usage is data only or log only .

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.