CODEWITHSUNDEEP
sqlsql-serversql-server-2014
thephez
thephez

Reputation: 372

Checking for unused space in SQL Server database file

I am trying to determine how much space is available in a SQL Server database file using this slightly modified version of query I found somewhere on SO. 

SELECT
    GETDATE() AS [Timestamp],
    [TYPE] = A.TYPE_DESC,
    [FILE_Name] = A.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),
    [FILEGROUP_NAME] = fg.name,
    [File_Location] = A.PHYSICAL_NAME
FROM
    sys.database_files A 
LEFT JOIN 
    sys.filegroups fg ON A.data_space_id = fg.data_space_id 
WHERE 
    A.TYPE_DESC NOT LIKE 'LOG'
ORDER BY 
    A.TYPE desc, A.NAME;

Since the database file does not shrink when data is deleted from the database, the query does not quite provide all the information I am looking for.

Is there any way to tell how much actual data is contained within the SpaceUsed file property (vs. available space due to deleted records, etc.)? Or is the only option to do something like DBCC SHRINKDATABASE and change the physical size of the file?

I have looked at sp_spaceused, sys.dm_db_index_physical_stats, and quite a few other things but so far have not come up with a solution.

Update: I've found something that I think is a bit closer to what I need, although I'm not sure it is quite right. Calculating the space used from the page count / average page space used looks like a value closer to what I'm expecting.

SELECT 
    page_count,
    avg_page_space_used_in_percent
FROM 
    sys.dm_db_index_physical_stats(db_id('TestDB'), NULL, NULL, NULL, 'Detailed')

Upvotes: 1

Views: 9873

Answers (2)

mokszs
mokszs

Reputation: 41

There are a few ways to do this, my personal favorite is the undocumented DBCC command ShowFileStats. It outputs a list of data files for a database, the total number of extents on disk, and the actual number of extents being used.

For those that do not remember, an extent is 8 pages, and a page is 8 kilobytes in size. So a little math on these two outputs should give you the information you are looking for. Here is a quick sample script.

Note that the DBCC command runs in context of the current database. You could modify this and turn it into a little loop. 

IF OBJECT_ID('tempdb..#tempDataUsage') IS NOT NULL DROP TABLE #tempDataUsage
CREATE TABLE #tempDataUsage
(
    [Fileid] INT,
    [FileGroup] INT,
    [TotalExtents] BIGINT,
    [UsedExtents] BIGINT,
    [Name] sysname,
    [FileName] varchar(256)
)
DECLARE @SQLStatement NVARCHAR(400) = 'USE msdb
DBCC ShowFileStats'
INSERT INTO #tempDataUsage
EXEC (@SQLStatement)

SELECT 
    (TotalExtents * 8 * 8) AS totalSpaceOndiskKB,
    (UsedExtents * 8 * 8) AS SpaceActuallyUsedKB,
    Name AS DbName,
    FileName 
FROM 
    #tempDataUsage

Upvotes: 1

Jason
Jason

Reputation: 945

In SSMS, try right clicking on the database, go to reports, then standard reports, and then select disk usage. If that doesn't work for you, maybe one of the other standard reports will.

enter image description here

Below the pie charts on the standard disc usage report, you can see the disc space used by data files

enter image description here

Upvotes: 1

Related Questions