Checking the disk space used by each database on a server

Category : Tips User Rating : 4.5 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
As part of a recent server consolidation exercise we needed to check the amount of file space used by each database on our live SQL Servers. The following query lists all databases on a server together with the total file size of the associated files (data files and transaction logs), truncated to the nearest MB.
SELECT      sys.databases.name,
            CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]
FROM        sys.databases
JOIN        sys.master_files
ON          sys.databases.database_id=sys.master_files.database_id
GROUP BY    sys.databases.name
ORDER BY    sys.databases.name
This works for SQL Server 2005 and 2008. The size of the files in sys.master_files is expressed in pages which is assumed to be 8k. As an alternative to the above to determine the size of the current database execute the following stored procedure :
exec sp_spaceused
This returns two recordsets, the database_used column in the first recordset should give the current database size.

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Checking the disk space used by each database on a server.aspx

Keywords

SQL,disk,drive,database


Comments
Post by rani on Fri 22 Nov 2013 11:41. Report Inappropriate Post

good
Post by Anvesh on Wed 02 Mar 2016 08:57. Report Inappropriate Post

Nice Article !

Really this will help to people of SQL Server Community.
I have also prepared small note on this, Script to find the of database files and log files.

http://www.dbrnd.com/2015/11/sql-server-script-to-find-the-size-of-database-files-and-log-files/
Post by Joseph on Mon 16 May 2016 14:50. Report Inappropriate Post

Thanks. We had to convert size to BIGINT first to get this to work.
Post by Raj on Tue 21 Jun 2016 14:31. Report Inappropriate Post

this is only showing Initial size of a DB. the size actually occupied may be different !!
Post by pavankumar on Sat 12 Nov 2016 06:27. Report Inappropriate Post

Hello ,
I would like to know the few information about files movement.
After moving db files from old location to new location, if i want to know those db files of old location(previous location) how to find out ??

can you please let me know the information about that.

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
 Not displayed Optional, but displayed if entered