Post reply

Filesystem free space

  • November 24, 2011 at 9:13 am

    #246750

    How to find free space on data files for a database by SQL query

    Thanks in advance.

    satya

  • November 24, 2011 at 9:14 am

    #1412744

    Free space in the data file or free space in the filesystem?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 24, 2011 at 9:26 am

    #1412751

    Use DB

    Select * from sys.sysfiles

  • November 24, 2011 at 9:41 am

    #1412759

    sysfiles is deprecated, included only for backward compatibility with SQL 2000 and should not be used any longer.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 24, 2011 at 10:47 am

    #1412770

    Here is updated query ..

    select a.FILEID,

    [FILE_SIZE_MB] =

    convert(decimal(12,2),round(a.size/128.000,2)),

    [SPACE_USED_MB] =

    convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),

    [FREE_SPACE_MB] =

    convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,

    NAME = left(a.NAME,15),FILENAME = left(a.FILENAME,100)from sys.sysfiles a

  • November 25, 2011 at 5:23 am

    #1412963

    Thanks Vikki 🙂

  • November 25, 2011 at 8:14 am

    #1412998

    GilaMonster (11/24/2011)

    sysfiles is deprecated, included only for backward compatibility with SQL 2000 and should not be used any longer.

    For This reason

    http://msdn.microsoft.com/en-us/library/ms178009.aspx

    I Thinks use should use this :

    select a.file_id,

    [FILE_SIZE_MB] =

    convert(decimal(12,2),round(a.size/128.000,2)),

    [SPACE_USED_MB] =

    convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),

    [FREE_SPACE_MB] =

    convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,

    NAME = left(a.NAME,15),FILENAME = left(a.physical_name,100)from sys.database_files a

  • November 25, 2011 at 9:38 am

    #1413019

    Thanks to you all , can you help me to get the file space details for all the DBS.

  • November 25, 2011 at 2:39 pm

    #1413069

    use the same query with a exec sp_msforeachdb ' ... '

  • November 29, 2011 at 1:43 am

    #1413849

    gelimontana143 (11/29/2011)

    SPAM!

  • November 29, 2011 at 3:04 am

    #1413886

    Pouliot Philippe (11/25/2011)

    use the same query with a exec sp_msforeachdb ' ... '

    Try this link for some info

    http://www.sqlservercentral.com/Forums/Topic1195196-392-4.aspx#bm1196262

  • November 29, 2011 at 4:53 am

    #1413917

    Dev (11/29/2011)

    gelimontana143 (11/29/2011)

    SPAM!

    Sure is, but now you're just giving him more of what he came here for!

    Better now to quote him, other than his nickname.

  • November 29, 2011 at 5:50 am

    #1413969

    Ninja's_RGR'us (11/29/2011)

    Dev (11/29/2011)

    gelimontana143 (11/29/2011)

    SPAM!

    Sure is, but now you're just giving him more of what he came here for!

    Better now to quote him, other than his nickname.

    Thanks! Edited my last post.

Viewing 13 posts - 1 through 12 (of 12 total)

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