T-SQL Script to render Server DB Sizes (Alloc & Used)

Question

Post reply

T-SQL Script to render Server DB Sizes (Alloc & Used)

Express12

SSCoach

Points: 18514
More actions
February 23, 2006 at 10:47 am

#111701

I'm in the market for a T-SQL Script to render the following for a given SQL Server 2000 Server:
All DB Names along with their allocated space & used space. I know sp_helpfile will do this for a SINGLE DB. I'm looking for a script to render this for all DB's on a single server in a single invocation.
SAMPLE:
DBName      Allocated Space (MB)   Utilized Space (MB)
Customer                   15000                 13000
Sales                         40                    10
Products                     210                    24
etc...
etc ....
thx in advance!
BT

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

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Here is what I run every Sunday evening versus over 100 servers that is at the Database and File group level:

set nocount on

create table #DBSpace

(DbNamesysname

,FileGroupNamesysname

,AllocatedMbinteger

,UsedMbinteger

,FreeMbinteger

,FreePercentinteger

,AutoGrowMbdecimal(12,2)

,AutoGrowMaxSizeinteger

)

exec master.dbo.sp_MSForEachDb @command1 =

'dbcc updateusage([?]) with no_infomsgs

insert into #DBSpace

select ''?''

,sysFileGroups.groupname

, F.AllocatedPages / 128 as AllocatedMb

,U.UsedPages / 128as UsedMb

,(F.AllocatedPages - U.UsedPages) / 128 as FreeMb

,(F.AllocatedPages - U.UsedPages) * 100

/ F.AllocatedPages as FreePercent

,CAST(F.GrowthPages / 128.0 as decimal(12,2) )as AutoGrowMb

, F.MaxSize as AutoGrowMaxSize

FROM(select GroupId

,sum( cast( used as bigint) )

from [?].dbo.sysindexes

whereindid in (0,1,255)

group by GroupId

)asU ( GroupId , UsedPages)

join(select GroupId

, sum(cast( size as bigint) )

, Max(growth)

, MAX(case status & 0x100000

WHEN 0x100000 then Growth * Size / 100 -- Increase in Percentage

elseGrowth-- Increase in Pages

end)

, MAX( case maxsize when -1 then 2147483647 else maxsize end)

from [?].dbo.sysfiles

where (status & 64 = 0)

group by GroupId

)as F (GroupId , AllocatedPages, Growth, GrowthPages, maxsize)

on F.GroupId = U.groupid

joinsysFileGroups

on sysFileGroups.GroupId= U.groupId

'

selectCAST( SERVERPROPERTY ('MachineName') as nvarchar(128) )AS MachineName

,COALESCE ( CAST( SERVERPROPERTY ('InstanceName')as nvarchar(128) ) , 'Default')AS InstanceName

,DbName

,FileGroupName

,AllocatedMb

,UsedMb

,FreeMb

,FreePercent

,AutoGrowMb

,AutoGrowMaxSize

from #DBSpace

SQL = Scarcely Qualifies as a Language

Cory E. SSCertifiable Points: 7076 More actions · Answer 2

What about

sp_msforeachdb "?..sp_helpfile"

That would give you the results for every db on that server.

-- Cory

Jimmy Jen SSCrazy Points: 2915 More actions · Answer 3

I just tested Carl's script. The result is correct if the data file consists of a single MDF file. For multi-datafiles database it will only report the first file.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Why does it not work for multi-datafiles ?

Please note that it reports by FileGroup, not by File.

Also, everything is rounded to MBs not Kbs.

Here is a test case:

create database SizeTest

go

use SizeTest

go

select *

into Foo1

from syscolumns

go

exec sp_spaceused @updateusage = 'true'

go

exec sp_helpfile

go

exec sp_helpfilegroup

go

alter database SizeTest

ADD FILE ( NAME = SizeTest_2,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\SizeTest_2.ndf' ,

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB)

TO FILEGROUP [PRIMARY]

go

SQL = Scarcely Qualifies as a Language

Cory E. SSCertifiable Points: 7076 More actions · Answer 5

I ran this as well on one of my servers, nearly all of my DB's have more than one datafile, and I got one datafile to come back as well. ?

-- Cory

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

There is no way to report space used by datafile as this information is not in the system tables, only in the space management pages.

One may get object space usage by file by running DBCC CHECKALLOC but the output would need to be reformated and then translated (object ids into object names and file ids into file names).

That is why the SQL summarizes by FileGroup.

Here is the format of the output of CHECKALLOC:

File 1. Number of extents = 52, used pages = 375, reserved pages = 405.

File 1 (number of mixed extents = 36, mixed pages = 277).

Object ID 1, Index ID 0, data extents 0, pages 6, mixed extent pages 6.

Object ID 1, Index ID 2, index extents 0, pages 4, mixed extent pages 4.

Object ID 1, Index ID 3, index extents 0, pages 2, mixed extent pages 2.

Object ID 2, Index ID 0, data extents 0, pages 6, mixed extent pages 6.

Object ID 2, Index ID 255, index extents 3, pages 23, mixed extent pages 9.

Object ID 3, Index ID 0, data extents 1, pages 15, mixed extent pages 9.

SQL = Scarcely Qualifies as a Language