Free Space In Database Files

File growth can cause system outages as SQL Server will have to wait for the file to grow before it can resume operations.  Of course, this depends on the size of the database, the growth rate, etc; however, the size of your databases is something you will want to keep an eye on.  The below script gives you this information and so you can better monitor these rates and grow your database during the maintenance window.

This query will only grab the information for the database you are connected to.

[sourcecode language=”sql”]SELECT  s.name AS [Name]
, s.physical_name AS [FileName]
, s.size * CONVERT(FLOAT, 8) AS [Size] /*File size on Disk*/
, CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS FLOAT)
* CONVERT(FLOAT, 8) AS [UsedSpaceInKB]
, (CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS FLOAT) * CONVERT(FLOAT, 8))
/ 1024 AS [UsedSpaceInMB]
, ((s.size * CONVERT(FLOAT, 8)
– CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS FLOAT) * CONVERT(FLOAT, 8)))
/ 1024 AS ‘Available Free Space In MB’ /*Matches SSMS file report*/
, (s.size * CONVERT(FLOAT, 8)
– CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS FLOAT) * CONVERT(FLOAT, 8))
/ (s.size * CONVERT(FLOAT, 8)) * 100 AS ‘Percent Free’
, growth
, max_size
, state_desc
FROM    sys.master_files AS s
WHERE   s.database_id = DB_ID()
[/sourcecode]

Getting a little fancy, we can grab the information for each database on the instance, but you would have to insert this into a table to do any ordering.

[sourcecode language=”sql”]
EXEC dbo.sp_MSforeachdb ‘ Use ? SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size], /*File size on Disk*/
CAST(FILEPROPERTY(s.name, ”SpaceUsed”) AS float)* CONVERT(float,8) AS [UsedSpaceInKB]
, (CAST(FILEPROPERTY(s.name, ”SpaceUsed”) AS float)* CONVERT(float,8)) / 1024 AS [UsedSpaceInMB]
, ((s.size * CONVERT(float,8) – CAST(FILEPROPERTY(s.name, ”SpaceUsed”) AS float)* CONVERT(float,8))) / 1024 AS ”Available Free Space In MB” /*Matches SSMS file report*/
, (s.size * CONVERT(float,8) – CAST(FILEPROPERTY(s.name, ”SpaceUsed”) AS float)* CONVERT(float,8)) / (s.size * CONVERT(float,8)) * 100 AS ”Percent Free”
, growth, max_size, state_desc
FROM
sys.master_files AS s
WHERE s.database_id = db_id()'[/sourcecode]

**I was delayed in getting this post out and I no longer remember where I got the original code from. I am pretty sure it was from a trace file looking at what SSMS did do pull this information. If this is your script (or started as your script), I am sorry I did not note it above.**