How to determine used/free space within SQL database files?
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 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
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)
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(size/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(size/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;
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.
The first one gives me `Arithmetic overflow error converting numeric to data type numeric.` and the last one only gives for the current database it would be nice if it gave for all databases.
@Peter Increase the decimal scale in the convert statements. There isn't an official to do this in all databases but you can get around this by using the undocumented sp_msforeachdb proc and dynamic sql.