Home » Database File Free space and checking Database Growth Trends

Database File Free space and checking Database Growth Trends

sql server DBA 999

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 *