Database File Free space and checking Database Growth Trends

Database File Free space and checking Database Growth Trends is one of the important tasks for a DBA for Pre-Sizing the Database to avoid the Autogrowths plus to have a idea of Space Requirements on the SQL Server etc.

Below Query can be really helpful for store this information into a Central location and monitor my Database Growth Trends.


SELECT S.FILEID,
CONVERT(decimal(12,2),ROUND(S.size/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty(S.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND((S.size-fileproperty(S.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
S.name as [DATABASENAME],
S.FILENAME as [FILENAME]
FROM dbo.sysfiles S

Leave a Reply

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