Database files details – SQL Server

Share via:

Database files details – SQL Server

This script will help to identify the Database files Size in MB, Space Used, Available Space, % used. If there is space crunch on Database Drive and this query will help to release space if the %used is very low and the size of file is high.

SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
CAST(FILEPROPERTY(name, ‘SpaceUsed’)/128.0 AS DECIMAL(10,2)) AS [Space Used],
CAST(size/128.0-(FILEPROPERTY(name, ‘SpaceUsed’)/128.0) AS DECIMAL(10,2)) AS [Available Space],
CAST((CAST(FILEPROPERTY(name, ‘SpaceUsed’)/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles where filename like ‘%E:\MSSQL\DATA%’


Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook :
LinkedIn :
Twitter :
YouTube :
Instagram :

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (12 votes, average: 5.00 out of 5)

Add Comment