Monitoring Databases  «Prev 

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 .