Post reply

Getting Avaibale Free Space In database

  • June 30, 2015 at 2:20 am

    #309536

    Hi Everyone,

    I have confusion related to free space in database, i have a database when i am checking available free space through database properties wizard it shows me 1609MB , but when i am calculating using following scripts

    1) SELECT DB_NAME() AS DbName,

    name AS FileName,

    size/128.0 AS CurrentSizeMB,

    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB

    FROM sys.database_files;

    2) DBCC SHOWFILESTATS WITH NO_INFOMSGS

    i am getting 1513MB free space

    So my question is why i am getting different values from property wizard and script.

    Please help..

    Thanks

    -----------------
    Aditya Rathour
    SQL DBA

    Not Everything that is faced can be changed,
    but nothing can be changed until it is faced.

  • June 30, 2015 at 2:34 am

    #1809001

    Hi

    In SSMS 2008 the total size displayed in the properties of the database is including the LOG space. The free space displayed in the properties of the database is excluding the free space in the LOG file. That could be the difference between the properties and the query on sys.database_files.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • June 30, 2015 at 2:39 am

    #1809004

    Thanks for quick reply,

    I am also calculating by excluding logfile free space and getting it 1513 MB

    but in database properties wizard it shows 1609MB

    -----------------
    Aditya Rathour
    SQL DBA

    Not Everything that is faced can be changed,
    but nothing can be changed until it is faced.

  • July 11, 2015 at 6:44 am

    #1811800

    What Hanshi is saying is, the db properties include logfile space as well, so that's why it shows more than your query results.

    Regards,
    SQLisAwe5oMe.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply