As a DBA you may sometimes have the need to gather some stats on the sizes of files in your databases.  Once again Microsoft has been kind enough to provide us with some handy tools get get at this information without having to use the clunky GUI.

If you are just simply looking for the size of the files and are not concerned about finding the used space and free space you can simply use the sys.master_files DMV.  This DMV has a column named “size” which represents the number of 8k pages in the database file.  To get to MB you would simply multiply size times 8 and then divide the total by 1024.  I like to throw in some rounding and conversion to make my result more readable.

Here is an example.

select db_name(database_id) as [DatabaseName]
, name as [Logical_Name]
, convert(decimal(18,2), round(((size * 8) / 1024.00),2)) as [SizeMB]
from sys.master_files
where db_name(database_id) = 'AdventureWorks2014'

And here are the results:

But what if I also want to determine the used space and free space in the file?

Well, its always been a bit of a source of irritation for me that this information cannot be obtained from sys.master_files.  Just seems like it would have been something that could have been included in this DMV and it would make it so much easier when trying to include this information in reports, queries, etc… but at least there is, in fact, still a pretty simple way to get at this info.

To get database file used space and free space we can use dbcc showfilestats.  When you execute this statement you will see columns named “TotalExtents” and “UsedExtents“.  These columns represent the number of 64k extents in the databse file.  So, for example, if you wanted to convert TotalExtents to MB you would need to multiply TotalExtents by 64 and then divide the total by 1024.   Again, I like to throw in some rounding and conversion to make my result more readable.  Unfortunately dbcc showfilestats will only display information for database files in the current database and it also does not include information about the log files, and since it is a DBCC command it’s not as easy to use it in select statements.  So first we will look at the output from the dbcc command and then I will show you a query that gives you the info in a format that is more useful.

To run this command you would just simply execute the following statement:

dbcc showfilestats with no_infomsgs;

And the result:

As you can see, unless you are used to thinking in terms of EXTENTS instead of MB or GB, this isn’t very helpful.  So, as promised, here is another query that will take the results of the dbcc command and dump it into a temp table and then do the conversion for us:

use AdventureWorks2014;
go
if (select object_id('tempdb..#tempFileSize')) is not null
drop table #tempFileSize;

create table #tempFileSize (Fileid int, [FileGroup] int, TotalExtents bigint, UsedExtents bigint, Name nvarchar(128), [FileName] nvarchar(260))

declare @sql nvarchar(max);
set @sql = 'dbcc showfilestats with no_infomsgs;'

insert into #tempFileSize exec(@sql);
select db_name() as [DatabaseName],
 Name as [Logical_Name],
 convert(decimal(18,2), round(((TotalExtents * 64) / 1024.0),2)) as [TotalMB],
 convert(decimal(18,2), round(((UsedExtents * 64) / 1024.0),2)) as [UsedMB],
 convert(decimal(18,2), round((((TotalExtents - UsedExtents) * 64) / 1024.0),2)) as [FreeMB]
from #tempFileSize

And the results: