John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

Free space in SQL server filegroups query

25th March 2022 By John McCormack 1 Comment

I just realised that in all my scripts that I use on a regular basis, I didn’t have one for working out free space in SQL Server filegroups. It’s not something that comes up too often but it’s handy to know. For methods of working out space in individual files, you could refer to this post on mssqltips.

-- Free space by filegroup
SELECT
FILEGROUP_NAME(data_space_id) as FilegroupName,
SUM(size/128.0) AS CurrentSizeMB, 
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS FreeSpaceMB,
(
	SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) / -- FreeSpaceMB
	(SUM(size/128.0))  -- CurrentSizeMB
	* 100 -- Convert to percentage
) AS FilegroupPercentFree,
COUNT(*) as NumberOfFilesInFilegroup
FROM sys.database_files
WHERE data_space_id <> 0
GROUP BY FILEGROUP_NAME(data_space_id);
colourful box files

POST #100

I just realised as well that this is post #100 on my blog. I started it just to save some of my queries and ideas. Post #1 was a really simple query but it got me going.

Share this:

  • Tweet
  • Email

Related

Filed Under: front-page, T-SQL Tagged With: data file, filegroup, SQL server

About John McCormack

John McCormack is an experienced SQL DBA with extensive knowledge of the two largest public clouds: AWS and Azure.

Trackbacks

  1. Finding Free Space in a SQL Server Filegroup – Curated SQL says:
    29th March 2022 at 1:05 pm

    […] John McCormack does some digging: […]

    Reply

Leave a ReplyCancel reply

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

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2025

 

Loading Comments...