Free Space inside data files differs from unused space on the Windows volume. I check both when planning growth.
A consulting customer wanted to plan file growth before autogrowth became an urgent event. After showing Database Properties, we wrote a query the team could use in its monitoring.
SELECT file_id, name, physical_name,
CONVERT(decimal(18,2),size * 8.0 / 1024) AS file_size_mb,
CONVERT(decimal(18,2),FILEPROPERTY(name,'SpaceUsed') * 8.0 / 1024) AS used_mb,
CONVERT(decimal(18,2),(size - FILEPROPERTY(name,'SpaceUsed')) * 8.0 / 1024) AS free_inside_file_mb,
CONVERT(decimal(9,2),100.0 * (size - FILEPROPERTY(name,'SpaceUsed')) / NULLIF(size,0)) AS free_inside_file_percent
FROM sys.database_files
WHERE type = 0
ORDER BY file_id;
The new query uses current catalog names, guards division by zero, and limits FILEPROPERTY allocation calculations to data files. Log utilization needs the appropriate log-space view, not the same data-file assumption.
Plan capacity from both measurements
Unused pages inside an MDF or NDF can be reused by the database. They don’t establish how much unallocated storage the host volume has. A drive-letter substring also misses mounted volumes, so retain the physical path and inspect the actual volume separately.
Plan suitable initial size and growth increments from measured demand. Autogrowth remains a useful safety mechanism; eliminating it at any cost is not the goal. This query changes no file size.
Free space inside a data file is not free disk capacity, it is capacity the database can reuse.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
15 Comments. Leave new
i remember using dbcc showfilestats to get db file size information, but that is in no of pages.
Super..
I am also using the same DMV to get this information. Thanks for sharing Pinal with all of us.
Regards,
Girijesh
Does this include the size of FileStream, if used?
Thanks Dave. Is there a way to get this to return the results for all DB’s on the server without having to use the drop down and change from master to each one of them and run this?
Not directly though there is a system procedure called sp_MSforeachdb that can be used in conjunction with a statement.
Something around like this:
— build command
SET @Command =
— for each database name
‘USE [?]; ‘
— insert into temp table
+ ‘INSERT INTO #DBFile (DatabaseName, DBFileName, FileID, DriveCode, ‘
+ ‘FileSizeMax, FileSizeNow, FileSizeUsed, FileSizeFree, FileSizeNext, FlagPctGrwth, ‘
+ ‘FlagPrime, FlagLog, FlagReadOnly, FlagDiskFile, FlagsStatus, DatetimeRecord) ‘
— results of each execution, divide size by 128.000 to invoke result in float instead of integer
+ ‘SELECT DB_Name(), DBFileName = [Name], [FileID], DriveLetter = Left([FileName], 1), ‘
— as sizes are held in 8k pages division by 128 = divide by 1024, multiply with 8 to get to MB
+ ‘MaxMB = Convert( money, Round([maxsize] / 128.000, 3)), ‘
+ ‘SizeMB = Convert( money, Round([size] / 128.000, 3)), UsedMB = Convert(money, Round(FileProperty([name], ”SpaceUsed”) / 128.000, 3)), ‘
+ ‘FreeMB = Convert(money, Round(([size] – FileProperty([name], ”SpaceUsed”)) / 128.000, 3)), ‘
— status is bitwise values set for 0x000002 = disk file, 0x000040 = log file, 0x100000 = growth is percentage
+ ‘NextExt = growth / (128 – ((status & 0x100000) / 0x100000) * 127.000), FlagPctGrowth = (status & 0x100000) / 0x100000, ‘
+ ‘FlagPrimary = FileProperty([name], ”IsPrimaryFile”), FlagLogFile = (status & 0x000040) / 0x000040, ‘
+ ‘FlagReadOnly = FileProperty([name], ”IsReadOnly”), FlagDiskFile = ((status & 0x000002) / 0x000002), status, ‘
+ ‘DatetimeRecord = Convert(datetime, GetDate(), 112) ‘
+ ‘FROM sys.sysfiles’;
— execute command
BEGIN TRY
EXECUTE master.sys.sp_MSforeachdb @Command;
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = Error_Number(),
@ErrorMessage = Error_Message();
SET @Message = N’!!! Error ‘ + Cast(@ErrorNumber As nvarchar) + N’ ‘ + @ErrorMessage + N’ encountered loading data into #DBFile’;
RAISERROR(@Message, 16, 1) WITH NOWAIT;
END CATCH;
#DBFile as a temporary table needs to be defined first:
— create temp table for capture
IF Object_ID(N’tempdb..#DBFile’) Is Not Null
BEGIN
DROP TABLE #DBFile;
END;
BEGIN TRY
CREATE TABLE #DBFile
(
DatabaseName nvarchar(128) NOT NULL,
DBFileName nvarchar(128) NOT NULL,
FileID int NOT NULL,
DriveCode char(1) NOT NULL,
FileSizeMax money NOT NULL, — for the rounding
FileSizeNow money NOT NULL, — for the rounding
FileSizeUsed money NOT NULL, — for the rounding
FileSizeFree money NOT NULL, — for the rounding
FileSizeNext money NOT NULL,
FlagPctGrwth bit NOT NULL,
FlagPrime bit NOT NULL,
FlagLog bit NOT NULL,
FlagReadOnly bit NOT NULL,
FlagDiskFile bit NOT NULL,
FlagsStatus int NOT NULL,
DatetimeRecord datetime NOT NULL
);
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = Error_Number(),
@ErrorMessage = Error_Message();
SET @Message = N’!!! Error ‘ + Cast(@ErrorNumber As nvarchar) + N’ ‘ + @ErrorMessage + N’ encountered creating temp #DBFile’;
RAISERROR(@Message, 16, 1) WITH NOWAIT;
END CATCH;
The use of money for the file sizes is odd – the translated definition as numeric(18,4) serves well though if I have to rewrite this procedure I would use decimal(18,4) now.
Looks good :)
Thank you!
@alan.. guess u can loop it with the help of cursor and an get the info for each db
Good one Dave. But don’t forget to look for heap tables with wasted after records have been deleted space won’t be released until the heaps are rebuilt. I also find people forget backup or copy tables that perhaps should be deleted before you expand the data files or add drive space.
Thanks a lot , I am using yours script to get the size for All databases.
#########################################################
DECLARE @DB_size TABLE
(Drive varchar(max),FILE_SIZE_MB int,SPACE_USED_MB int ,FREE_SPACE_MB int,FREE_SPACE_Persent int ,NAME varchar(max) ,FILENAME varchar(max))
INSERT @DB_size
EXEC sp_MSforeachdb
‘
use [?]
SELECT
SUBSTRING(a.FILENAME, 1, 1) Drive,
[FILE_SIZE_MB] = convert(decimal(12,2),
round(a.size/128.000,2)),
[SPACE_USED_MB] = convert(decimal(12,2),
round(fileproperty(a.name,”SpaceUsed”)/128.000,2)),
[FREE_SPACE_MB] = convert(decimal(12,2),
round((a.size-fileproperty(a.name,”SpaceUsed”))/128.000,2)) ,
[FREE_SPACE_Persent] = convert(decimal(12,2),
(convert(decimal(12,2),round((a.size-fileproperty(a.name,”SpaceUsed”))/128.000,2))
/ convert(decimal(12,2),round(a.size/128.000,2)) * 100)),
a.NAME, a.FILENAME
FROM dbo.sysfiles a
ORDER BY Drive, [Name]
‘
select Drive,FILE_SIZE_MB,SPACE_USED_MB,FREE_SPACE_MB,FREE_SPACE_Persent,NAME,FILENAME from @DB_size
Go
Thanks!
I am running out of free space disk has enough space on it. should I worry? my File_SIZE_MB is 250 and FREE_SPACE_MB IS 1.23 AND FREE_SPACE_% IS 0.00.
250 MB file? Look for some other big files on the OS.
Thank you- very helpful