Windows DatabaseebooksStatistical Information

LWE10118 : Howto know how much space is used in my database

Symptom:

You need to know if your database file still has unused space and how much is still there

Cause:

none

Solution:

You can use a stored procedure sp_spaceused to report the sizes. SP_spaceused will return two result sets containing the database name, size, unallocated space and a breakdown of the database's size.  (how much size is reserved and how much of that is taken up by data, by indexes, and how much is still unused.
Executing sp_spaceused on the OnePoint database (which is used by Microsoft Operations Manager) delivers:

The SQL Query:

use OnePoint
exec sp_spaceused

The Result

database_name      database_size      unallocated space
------------------------------------------------------------
OnePoint           19000.00 MB        14470.13 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
542584 KB          376224 KB          156680 KB          9680 KB


In this case you can see an unallocated space of 14 GB! This means, the Database has grown till about 19 GB and afterwards a lot of records have been deleted. The database file does not shrink automatically. This is the normal behaviour.

You can even look at a certain Table in the database. e.g.

use OnePoint
exec sp_spaceused Computer

name     rows        reserved           data               index_size         unused
------------ ------------------ ------------------ ------------------ ------------------
Computer 46 KB             16 KB              96 KB              0 KB

Disclaimer:

The information provided in this document is intended for your information only. Lubby makes no claims to the validity of this information. Use of this information is at own risk!
Copyright © 2004-2011 Lubby (V3.0.10 Aug 2011)
Sponsored by Keskon.
Statistical information by Google Analytics