SQL SERVER – How Much Free Space I Have in My Database?

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.

SQL SERVER - How Much Free Space I Have in My Database? freedb-800x355

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:

SQL SERVER - How Much Free Space I Have in My Database? database-size-01

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)

,
Previous Post
Sort Query Using Dynamic Variables Without EXEC – Interview Question of the Week #074
Next Post
Can We Have NULL Value in Primary Key? – Interview Question of the Week #075

Related Posts

15 Comments. Leave new

  • Anoop Agarwal
    June 7, 2016 10:22 am

    i remember using dbcc showfilestats to get db file size information, but that is in no of pages.

    Reply
  • Super..

    Reply
  • Girijesh Pandey
    June 7, 2016 1:45 pm

    I am also using the same DMV to get this information. Thanks for sharing Pinal with all of us.

    Regards,
    Girijesh

    Reply
  • Does this include the size of FileStream, if used?

    Reply
  • 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?

    Reply
    • Knut Boehnert
      June 13, 2016 1:14 pm

      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.

      Reply
  • Looks good :)

    Reply
  • @alan.. guess u can loop it with the help of cursor and an get the info for each db

    Reply
  • Linda Wenglikowski
    June 11, 2016 2:36 am

    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.

    Reply
  • Abdul Rehman
    May 3, 2017 10:39 am

    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

    Reply
  • suraj pudasaini
    August 15, 2018 2:50 am

    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.

    Reply
  • Thank you- very helpful

    Reply

Leave a Reply

Menu