How to find databases allocated and unallocated space per instance

  • I need the following information:

    Space Allocated and unallocated space for each database,

    Can you send me one Query that give me all this output.

    sp_spaceused is used for single database, i need a query for all databases in the instance with single query.

    Thanks in advance.

    NM

  • Feels like homework......

    IF OBJECT_ID('TempDB..#DBSizes') IS NOT NULL

    DROP TABLE #DBSizes

    CREATE TABLE #DBSizes

    (

    DBNameVarchar(128),

    FileTypeVarchar(5),

    CurrentSizeMBDec(9,1),

    SpacedUsedMBDec(9,1),

    FreeSpaceMBDec(9,1),

    SpacedUsedGBDec(9,3),

    FreeSpaceGBDec(9,3),

    SamppleDTSmallDatetime

    )

    DECLARE @SQLVarchar(1000)

    SET @SQL = 'USE [?]

    SELECTDBName= DB_NAME(),

    FileType= DF.type_desc,

    CurrentSizeMB= SUM(CAST(DF.size / 128.0 AS Dec(9,1))),

    SpacedUsedMB= SUM(CAST(FILEPROPERTY(DF.name, ''SpaceUsed'') / 128.0 AS Dec(9,1))),

    FreeSpaceMB= SUM(CAST((DF.size - FILEPROPERTY(name, ''SpaceUsed'')) / 128.0 AS Dec(9,1))),

    SpacedUsedGB= SUM(CAST(FILEPROPERTY(DF.name, ''SpaceUsed'') / 128.0 / 1024.0 AS Dec(9,3))),

    FreeSpaceGB= SUM(CAST((DF.size - FILEPROPERTY(name, ''SpaceUsed'')) / 128.0 / 1024.0 AS Dec(9,3))),

    SamppleDT= CAST(GETDATE() AS SmallDatetime)

    FROMsys.database_files DF

    WHEREDF.type_desc = ''ROWS''

    GROUPBY DF.type_desc'

    INSERT#DBSizes

    EXECsp_MSForEachDB @SQL

    SELECT*

    FROM#DBSizes

  • You could also modify the previous query to use sys.master_files. That will prevent you from having to loop through each database and you may not need a temp table either.

    Joie Andrew
    "Since 1982"

  • Joie Andrew (6/29/2016)


    You could also modify the previous query to use sys.master_files. That will prevent you from having to loop through each database and you may not need a temp table either.

    Tried that, doesn't work.

    From BOL:

    Returns the specified file name property value when a file name in the current database and a property name are specified. Returns NULL for files that are not in the current database

  • Thankyou for the replies.Its working

  • Hello, what's the significance of dividing by 128.0? is it safe to use this constant - if not, what's it logic behind?

    FreeSpaceGB = SUM(CAST((DF.size - FILEPROPERTY(name, ''SpaceUsed'')) / 128.0 / 1024.0 AS Dec(9,3)))

  • It's safe to use. The logic is that the FILEPROPERTY function when used with SpacedUsed returns the number of pages used. A page is 8K. So if you multiply by 8 you get the space used in KB. And then if you divide by 1024 you get the space used in MB. That is the same as dividing by 128.

    Sue

     

Viewing 7 posts - 1 through 6 (of 6 total)

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