Archive
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] GO select 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/