Blog on software engineering
Display used and free space with TSQL in Microsoft SQL Server 2008 mdf and ldf file

Display used and free space with TSQL in Microsoft SQL Server 2008 mdf and ldf file

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.