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

4 comments:

  1. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept. Thank you for the post.

    Doreen
    www.gofastek.com

    ReplyDelete
  2. 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
  3. 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
  4. This is really interesting and knowledgeable. Thanks for sharing. I really appreciate it a lot. Please do more blogs in the future. Thank you and God bless to the blogger!

    www.imarksweb.org

    ReplyDelete