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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 '15 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.

This one works for me and seems to be consistent on SQL 2000 to SQL Server 2012 CTP3:

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
ORDER BY groupid DESC

An alternative (not compatible with SQL Server 200) that provides more information, suggested by Tri Effendi SS:

USE [database name]
GO
SELECT 
    [TYPE] = A.TYPE_DESC
    ,[FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.name
    ,[File_Location] = A.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' 
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END 
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' 
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END 
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
order by A.TYPE desc, A.NAME; 
share|improve this answer
    
I suggest instead of the edits and additions to the SQL I provided those with other versions post their own answers. And thank you Paul for rejecting some of those edit suggestions. – Bruce Feb 19 at 14:09

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