Tags

, , ,


Many times, we have to get the information about space available in each database files or their respective locations or their total size for regular monitoring purpose or for doing various DBA activities. I often use the below query and I hope you will also find this query useful:

USE AdventureWorks
GO

SELECT a.FILEID
,[FILE_SIZE_MB] = convert(DECIMAL(12, 2), round(a.size / 128.000, 2))
,[SPACE_USED_MB] = convert(DECIMAL(12, 2), round(fileproperty(a.NAME, ‘SpaceUsed’) / 128.000, 2))
,[FREE_SPACE_MB] = convert(DECIMAL(12, 2), round((a.size – fileproperty(a.NAME, ‘SpaceUsed’)) / 128.000, 2))
,NAME = a.NAME
,[FILENAME ]
FROM dbo.sysfiles a

Cheers,

Subhro Saha

Advertisements