| 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.
The `sp_spaceused` and `sp_helpdb` stored procedures are still available and commonly used in SQL Server 2019.
These procedures are helpful for database administrators and are part of the system stored procedures in SQL Server 2019, just as they were in earlier versions.
The `sp_spaceused` and `sp_helpdb` stored procedures are still available and commonly used in SQL Server 2019.
- `sp_spaceused`: This stored procedure is used to display information about the storage space used by a database or a table, including the total amount of space allocated, used, and free.
- `sp_helpdb`: This stored procedure provides information about the databases on an instance of SQL Server, including their name, size, owner, and the date the database was created.
These procedures are helpful for database administrators and are part of the system stored procedures in SQL Server 2019, just as they were in earlier versions.
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:
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 [ [ @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
- database_name is the name of your current database.
- database_size is the total size of all files used in the database, include transaction log files.
- unallocated space is the total size that is not assigned, including all tables and indexes.
- reserved is the number of KB that is assigned to tables and indexes.
- data is the number of KB that has data.
- index_size is the number of KB that is used for index.
- unused is the number of KB that is assigned to data, but currently does not have any data stored on it.
sp_spaceused for Database Table
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 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
- name is the table you are gathering information about.
- rows is the number of rows in the table.
- reserved is the total amount of space assigned to the table and its indexes.
- data is the amount of space used to store data.
- index_size is the amount of space used to store indexes.
- 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:
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:
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.
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:
- name is the database name.
- db_size is the total size of all files for the database.
- owner is the database owner.
- created is the day the database was created.
- 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:
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.
- name is the files logical name.
- fileid is the file identifier.
- filename is the files physical name.
- filegroup is the filegroup that the file belongs to; if filegroup is null then it is a log file.
- size is the current size of the file.
- maxsize is the maximum size to which the file can grow.
- growth is the growth rate defined for the file.
- 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.