Check database size and free space in SQL Server

Every day we have to once check database free space and below is the best query to give detail

Here is a simple SQL script to retrieve the free spaces on db files (data, log) of any database.

Query:  Quick Download


if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%'
SELECT [name], fileid, filename, [size]/128 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128)/([size]/128))*100)) AS 'percentage Used'
FROM sysfiles
else
SELECT [name], file_id, physical_name, [size]/128 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128)/([size]/128))*100)) AS 'percentage Used'
FROM sys.database_files



Output:



namefile_idphysical_nameTotal Size in MBAvailable Space In MBUsed Space In MBpercentage Used
master1C:\DATA\master.mdf41.1252.87550
mastlog2C:\DATA\mastlog.ldf10.468750.531250


You can refer below for more scripts handy:

Frequently used DMV's in SQL Server

3 comments:

  1. Nice concept, but your calculations for percent used are wrong. percent used is simply used/available*100 Try this:

    if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%'

    SELECT [name], fileid, filename, [size]/128 AS 'Total Size in MB',
    [size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
    (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 / ([size]/128)) * 100 as ' Correct Percent Used'
    FROM sysfiles
    else
    SELECT [name], file_id, physical_name, [size]/128 AS 'Total Size in MB',
    [size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
    (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS 'Used Space In MB',
    ((CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) / ([size]/128))* 100 as 'Correct Percent Used'
    FROM sys.database_files

    ReplyDelete
  2. Nice article, I found another post for the same: http://www.sqlservercentral.com/blogs/zoras-sql-tips/2016/01/15/four-ways-to-find-free-space-in-sql-server-database-/

    ReplyDelete
  3. Thank you for your guide to with upgrade information.
    Sql server DBA Online Training

    ReplyDelete