Script to Gather Disk Space Details and Database File Free Space Information

It is a common requirement to find the details about the total free disk space, database files total size, free space available in database files, name of the database files, type of database file and the physical location they are located at.

Below is the code which returns Free disk space on a drive, name of database, logical and physical file names of all databases, total size, free space available inside the database files and the physical location where they are stored.

USE tempdb
    GO
      CREATE TABLE #TMPFIXEDDRIVES (
    DRIVE CHAR(1),
    MBFREE INT)

    INSERT INTO #TMPFIXEDDRIVES
    EXEC xp_FIXEDDRIVES

    CREATE TABLE #TMPSPACEUSED (
    DBNAME VARCHAR(1500),
    FILENME VARCHAR(500),
    SPACEUSED FLOAT,
    growth int)

INSERT INTO #TMPSPACEUSED
    EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed,growth from sysfiles''')

SELECT C.DRIVE,
    CASE 
    WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
    ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
    END AS DISKSPACEFREE,
    A.NAME AS DATABASENAME,
    B.NAME AS FILENAME,
    CASE B.TYPE 
    WHEN 0 THEN 'DATA'
    ELSE TYPE_DESC
    END AS FILETYPE,
    CASE 
    WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
    ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
    END AS FILESIZE,
    CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE_MB,
    (d.growth*8)/1024 as AutoGrowth_MB,
    B.PHYSICAL_NAME
    FROM SYS.DATABASES A
    JOIN SYS.MASTER_FILES B
    ON A.DATABASE_ID = B.DATABASE_ID
    JOIN #TMPFIXEDDRIVES C
    ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
    JOIN #TMPSPACEUSED D
    ON A.NAME = D.DBNAME
    AND B.NAME = D.FILENME
    ORDER BY databasename

/*<b>*/DROP/*</b>*/ TABLE #TMPFIXEDDRIVES
/*<b>*/DROP/*</b>*/ TABLE #TMPSPACEUSED

Output returned will be in below format

DRIVE

DISKSPACEFREE

DATABASENAME

FILENAME

FILETYPE

FILESIZE

SPACEFREE_MB

AutoGrowth_MB

PHYSICAL_NAME

S

923.48 GB

DBName

LogicalFileName

DATA

218.16 GB

8500.25

200

S:\DataFile.mdf

DRIVE – Drive where the database file resides.
DISKSPACEFREE – Free disk space available in the drive.
DATABASENAME – Name of the database.
FILENAME – Logical database File Name.
FILETYPE – What type of file it is, Data File, Log File.
FILESIZE – Total size of the database file.
SPACEFREE_MB – Free space available inside the database file.
AutoGrowth_MB – Auto_Growth size of the database file.
PHYSICAL_NAME – Physical location and physical database file name.

Below script will provide with space details of a specific database and its files. This information will be useful to find which database files are large, how much free space is still available in the database files, so that we can shrink the file to release the available free space.

select
       name
     , filename
     , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
     , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
     , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
     from dbo.sysfiles a 

name

filename

FileSizeMB

SpaceUsedMB

FreeSpaceMB

tempdev

T:\Data\tempdb.mdf

40960.00

4.75

40955.25

templog

T:\Log\templog.mdf

2048.00

365.86

1682.14

tempdata

T:\Data\tempdata.mdf

40960.00

4.56

40955.44

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *