sql Database file sizes

Use [DatabaseName];
Select
    [fileid] As [FileID],
    [name] As [Name],
    [filename] As [FileName],
    CONVERT(decimal(12,2),ROUND([size]/128.000,2)) As [FileSizeMB],
    CONVERT(decimal(12,2),ROUND(fileproperty([name],'SpaceUsed')/128.000,2)) As [SpaceUsedMB],
    CONVERT(decimal(12,2),ROUND(([size] - fileproperty([name],'SpaceUsed'))/128.000,2)) As [FreeSpaceMB],
    CONVERT(decimal(12,2),ROUND(((CAST(fileproperty([name],'SpaceUsed') As decimal) / [size]) * 100), 0)) As [PercentageUsed],
    CONVERT(decimal(12,2),ROUND(100 - ((CAST(fileproperty([name],'SpaceUsed') As decimal) / [size]) * 100), 0)) As [PercentageFree]
From dbo.sysfiles;
Determines the used and free space of a databases' files

Updated: Friday 13th April 2012, 05:59pm

There are 0 comments

Leave a comment of your own

Comments are currently closed.