SQL Server Script to Check Free & Used Space for Data and Log Files

    As a SQL Server DBA, monitoring the free and used space in database files is crucial for performance and capacity planning. Below is a T-SQL script that retrieves the size, used space, and free space for all databases in an instance.

Create Table #dbInfo (

    dId smallint, 

    dbName sysname, 

    gId smallint NULL, 

    segName varchar(256) NULL, 

    filName varchar(520) NULL, 

    sizeMB decimal(10,2) null, 

    usedMB decimal(10,2) null, 

    freeMB decimal(10,2) null, 

    pcntUsed decimal(10,2) null, 

    pcntFree decimal(10,2) null

)

Declare @sSql varchar(1000)

Set @sSql = 'Use [?];

Insert #dbInfo (dId, dbName, gId, segName, filName, sizeMB, usedMB)

Select 

    db_id(), 

    db_name(), 

    groupid, 

    rtrim(name), 

    filename, 

    Cast(size/128.0 As Decimal(10,2)), 

    Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))

From dbo.sysfiles 

Order By groupId Desc;'

Exec sp_MSforeachdb @sSql

Update #dbInfo 

Set freeMB = sizeMB - usedMB,

    pcntUsed = (usedMB/sizeMB)*100,

    pcntFree = ((sizeMB-usedMB)/sizeMB)*100

Select * from #dbInfo 

-- To filter by database:

-- WHERE dbname IN ('YourDatabaseName')

-- To filter by drive (e.g., G: drive)

-- WHERE filName LIKE '%G:\%' ORDER BY freeMB DESC 

Drop Table #dbInfo