How to find databases allocated and unallocated space per instance

  • Narine M

    SSCrazy

    Points: 2672

    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

  • DennisPost

    SSCrazy

    Points: 2691

    Feels like homework......

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

    DROP TABLE #DBSizes

    CREATE TABLE #DBSizes

    (

    DBName Varchar(128),

    FileType Varchar(5),

    CurrentSizeMB Dec(9,1),

    SpacedUsedMB Dec(9,1),

    FreeSpaceMB Dec(9,1),

    SpacedUsedGB Dec(9,3),

    FreeSpaceGB Dec(9,3),

    SamppleDT SmallDatetime

    )

    DECLARE @SQL Varchar(1000)

    SET @SQL = 'USE [?]

    SELECT DBName = 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)

    FROM sys.database_files DF

    WHERE DF.type_desc = ''ROWS''

    GROUP BY DF.type_desc'

    INSERT #DBSizes

    EXEC sp_MSForEachDB @SQL

    SELECT *

    FROM #DBSizes

  • Joie Andrew

    One Orange Chip

    Points: 27295

    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"

  • DennisPost

    SSCrazy

    Points: 2691

    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

  • Narine M

    SSCrazy

    Points: 2672

    Thankyou for the replies.Its working

  • takeiteasyn20

    Newbie

    Points: 1

    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)))

  • Sue_H

    SSC Guru

    Points: 90590

    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 7 (of 7 total)

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