How to find databases allocated and unallocated space per instance

Question

Post reply

How to find databases allocated and unallocated space per instance

Narine M

SSCrazy

Points: 2672
More actions
June 8, 2016 at 9:59 am

#311055

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

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

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

DennisPost SSCrazy Points: 2691 More actions · Answer 1

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

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

Narine M

SSCrazy

Points: 2672

July 6, 2016 at 10:38 pm

#1888840

Thankyou for the replies.Its working

takeiteasyn20 Grasshopper Points: 12 More actions · Answer 5

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: 90860 More actions · Answer 6

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