Find the biggest database on an instance

There is always a time when a server reaches a point when there is no more disk space to restore yet another database. Or you just start thinking about adding more storage because of databases' growth. I am not talking about having an incident on a production server because of that (I hope we all have proper monitoring that helps to predict such situations ahead of time ...). I mean just reaching last 10, 20 or 30% storage usage - whatever you is the most appropriate limit.

Then, I do a quick talk to myself:

What can I delete to make more room on the disk?. I can delete a backup ... wait ... I think I cannot. That is not always a good idea. What about one of my databases? Yes ... I can try looking at this option. So which database can I delete?

 

Now, I need a list of unused databases. Unfortunately, it is almost never easy. Sometimes I prefer another approach - find a list of the biggest databases and than I can try to determine if they are needed, can be moved somewhere else or archived and deleted. Of course those options seem to be more tempting when the whole case is about non-production environment.

As we have come to a list of the biggest databases ...  

There is a system view - sys.database_files that contains information about database files including their current size on a disk. Size column contains a number of 8kB pages so you need to do a proper calculation to get a size in MB or GB.

select db_name() [db name], name [file name], size*8/1024 [size in MB]
from sys.database_files

Each database file has a separate row in the result so to get a total size of a database, sizes have to be summed up.

select db_name() [db name], sum(size*8/1024) [size in MB]
from sys.database_files

Now, you can use sp_msforeachdb to iterate through all databases.

create table ##databases (
	dbName varchar(max),
	size int
)

exec sp_msforeachdb 'insert into ##databases (dbName, size)
	select ''?'' [db name], sum(size*8/1024) [size in MB]
	from [?].sys.database_files'

select *
from ##databases
order by size desc

drop table ##databases

The result is a list of databases sorted by occupied disk space in descending order.