Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

I normally use the following method to determine the free/used space within each file of a database:

Select *, fileproperty(name, 'SpaceUsed') as Used
From dbo.sysfiles

This returns total and used space in pages, which I then multiply by 8 to get KB (or divide by 128.0 to get MB).

I found another script than instead uses DBCC showfilestats and dbcc sqlperf(logspace) to return the TotalExtents and UsedExtents, which can then be multiplied by 64 to get KB (or divided by 16.0 to get MB).

Ignoring the extra columns, will these two always give identical values for free/total space? What about sp_spaceused?

Does their accuracy both depend on a recent DBCC UPDATEUSAGE?

Is there another, better method for determining used/free space? (I need this script to work on SQL 2000, 2005, and 2008 servers)

Partially related: Can you have a partially-allocated extent? (only 3 of the 8 pages within an extent are allocated, for example)

share|improve this question

marked as duplicate by Paul White Aug 27 at 10:54

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

This one works for me and seems to be consistent on SQL 2000 to Denali CPT3.

SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [Allocated Size in MB],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used in MB],
   CAST([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max in MB],
   CAST([maxsize]/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB],
   CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST([maxsize]/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.