Addicted2AX / Addicted2SQL

In this post I will show you a quick and easy way to find out how much space a specific database occupies on the hard drive.
Sure, you can check the properties of the database. But this is neither fast nor convenient.

In this case the build-in system stored-procedure sp_spaceused is useful.
Just select the database of interest and type in EXEC sp_spaceused and you will get a result like this:



The meaning of the columns as following:

database_name: The name of the database
database_size: The size of the database in Megabytes (including data & logs)
unallocated space: Space in database which is not allocated to database-objects

reserved: Total amount of space allocated by objects in the database
data: Total amount of space used by data
index_size: Total amount of space used by database indexes
unused: Reserved for database-objects but not used

Ok, not bad. But what would you do if you need this kind of information for every database on the SQL Server?
Of course you can execute sp_spaceused for every database. But this is also not very convenient.

For this kind of job there is another system stored-procedure. It is undocumented and therefore not officially supported by Microsoft. So please handle it with care.

The name of it is: sp_MSforeachDB.
Usage: EXECUTE master.sys.sp_MSforeachDB 'USE [?]; EXEC sp_spaceused'

The result would be like this:



You can perform a lot of other interesting and useful actions on your SQL Server with sp_MSforeachDB. In one of the next posts I will show you some other examples of usage.