To calculate the "size", "used size in" and "free space in" a Microsoft SQL Server 2008 mdf or ldf file use:

use master

go
select
name, cast((size/128.0) as int) as TotalSpaceInMB, cast((cast(fileproperty(name, 'SpaceUsed') as int)/128.0) as int) as UsedSpaceInMB, cast((size/128.0 - cast(fileproperty(name, 'SpaceUsed') AS int)/128.0) as int) as FreeSpaceInMB from sys.database_files

Result

image 

For more information on fileproperty, see: http://msdn.microsoft.com/en-us/library/ms188401.aspx

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.