This is one of the simplest query that I wrote for one of my consulting customers who asked me. When I was discussing with the customer on some of the best practices of working with SQL Server, I was telling him not to use the autogrowth as a regular feature to use. I wanted the DBA to make sure that the autogrowth never gets called. The immediate question was – how do I know I need to increase my database files? When should I be doing them? In What maintenance cycle should this be done to find Free Space.
It was easy on first call because I immediately opened up the Database properties and show where he can get the valuable information. But I was questioned quickly on how to do the same via a code in the backend so that he can use some automation to this whole exercise.
On sitting with the team for about 5 minutes, this is the code I gave them to use it as handy for this particular scenario.
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_%] = 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]
The output of the above query is going to look like below:
Now with this information, now one can easily see how much Free Space one has on a given file and where these files are stored. I am sure each one of you is already using something similar in your environments. It would be great if you can share some of your experiences because I will be surely able to build on top of it to make more sense for customers I meet on a day-today basis.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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.
Thank you- very helpful