Home > SQL Tips > Identify potential free space in Database files before shrinking – TSQL Query

Identify potential free space in Database files before shrinking – TSQL Query

Today someone in office asked me:

"How can I know how much free space might be left in a Database to Shrink 
before actually Shrinking it?"


–> I told it is simple, go to SSMS, select Database -> Right click -> select Task -> select Shrink -> select either Database/File.


–> It will show you Available Free Space, of the whole Database or each mdf/ldf/ndf file(s).


–> He said he already knows it, his real ask was:

"what if you've hundreds of Databases and want to know the stats for each 
of them? Is there any SQL Query which can give you this stats, so that you can 
run it against each or all Database?"


I opened SQL Profiler, ran it against the instance and again performed all manual steps defined above to check the Free space. After the Shrink File pop-up window appeared I went back to the Profiler and stopped it, which threw me lot of typical SQL Queries generated by SQL Server DB engine. I searched and found the desired query that I was looking for. I slightly tweaked the query according to my needs and here it is:

USE [AdventureWorks2012]

	db_name()			AS [DatabaseName],
	s.name				AS [DB_File_Name],
	s.physical_name		AS [FileName],
	s.size * CONVERT(float,8) AS [TotalSize],
	CAST(CASE s.type WHEN 2 
			THEN s.size * CONVERT(float,8) 
			ELSE dfs.allocated_extent_page_count*convert(float,8) 
		END AS float)	AS [UsedSpace],
	CASE s.type WHEN 2 
		THEN 0 
		ELSE s.size * CONVERT(float,8) - dfs.allocated_extent_page_count*convert(float,8) 
	END					AS [AvailableFreeSpace] 
from sys.database_files AS s 
left outer join sys.dm_db_file_space_usage as dfs 
	ON dfs.database_id = db_id() 
	AND dfs.file_id = s.file_id
where (s.drop_lsn IS NULL)

–> Output:


To Shrink Database Files check my earlier blog post: https://sqlwithmanoj.wordpress.com/2011/07/02/shrink-database-shrink-file/

  1. DBtech
    May 29, 2014 at 1:58 pm

    I think this query will give you the same results to display data and log space information:

    SELECT file_id, name, type_desc, physical_name, size, max_size
    FROM sys.database_files ;

    • May 29, 2014 at 2:37 pm

      @DBtech, thanks for your comments.

      The query you posted will only show the Total Space taken by a Database File, the sys.database_files system view won’t show the Free Space in a DB File. You will have to JOIN with sys.dm_db_file_space_usage system view to get the Used & Free Space in a DB File.

      Also the [size] column will return total number of Pages in that DB File, so to get exact Size in KB you will have to multiply it by 8, as each Page is of 8KB.


  2. Ostati
    December 11, 2014 at 8:38 pm

    SQL 2008 does not have “allocated_extent_page_count” column.. Do you by any chance know the alternative?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: