Take the 2-minute tour ×
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.

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
add comment

1 Answer

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 [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
ORDER BY groupid DESC
share|improve this answer
    
Thanks, that's similar to one I use. Any comment on the comparison to DBCC showfilestats? –  BradC Nov 15 '11 at 21:23
    
Running both the query and dbcc showfilestats/sqlperf commands before and after running DBCC UPDATEUSAGE shows only a minor difference in the log space used. About what you’d expect from the updates to the system tables. This was a test on a 2000 server and DBCC UPDATEUSAGE made noticeable changes. I didn’t try sp_spaceused since my interest is in gross space available and overall size. What’s a couple (or 20) meg among friends? –  Bruce Nov 17 '11 at 14:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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