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.
            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
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.
Link back to this article : the disk space used by each database on a server.aspx



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

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.
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