LKBEN10675: Howto know how much space is used in my database and how much is still free
Symptom
You need to know if your database file has unused space and how much is still free
Cause
You need to know size of your database because you are running out of free space.
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!About the Author
Author: - Keskon GmbH & Co. KG
Wim Peeters is electronics engineer with an additional master in IT and over 30 years of experience, including time spent in support, development, consulting, training and database administration. Wim has worked with SQL Server since version 6.5. He has developed in C/C++, Java and C# on Windows and Linux. He writes knowledge base articles to solve IT problems and publishes them on the Lubby Knowledge Platform.
Latest update: 29.12.2021