Getting space info for all DBs from the master

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting space info for all DBs from the master

Author

Topic

valiky
Starting Member

3 Posts

Posted - 2009-07-27 : 09:21:31

Hi team,

I would like to determine how much space is being used for real by a database, how much is allocated but unused, etc.
Something like sp_spaceused, perhaps with additional details.

Here's the tricky part:
I cannot connect to each database.
I can only connect to the master and the msdb databases.

Can it be done or am I wasting my time?

Files and sizes are reported for all databases in master.sysaltfiles... but can I deduce somehow how much is actually used, allocated but not used, etc?

Just to make sure we're on the same page as it related to terminology - "allocated but unused" - I'm referring to the situation where space is already consumed by the database, but it's not being used yet to store data/log.

Any help will be highly appreciated!

escasey
Starting Member

1 Post

Posted - 2009-07-27 : 09:53:21

/* casey, 31-jan-2008
- report on space allocated to & occupied by a database, its component files, & its objects
*/

use master
go
--db total size allocated
--db logfile' total size
select
--
sum(((sf.size*8192.0)/1048576)/1024)/1024 'Total TBytes Allocated - Including Log',
sum(((sf.size*8192.0)/1048576)/1024) 'Total GBytes Allocated - Including Log'
--sum ((sf.size*8192.0)/1048576) 'Total MBytes Allocated'
from sys.sysfiles sf

-- db logfile' total size
select
sum(((sf.size*8192.0)/1048576)/1024) 'Logfile GBytes Allocated'
from sys.sysfiles sf
where
groupid = 0
go

--db datafiles' total size
select
sum((sf.size*8192.0)/1073741824)/1024 'Data-only TBytes Allocated',
sum((sf.size*8192.0)/1073741824) 'Data-only GBytes Allocated'
from sys.sysfiles sf
where
groupid > 0
go

-- total size of table & index objects
select
sum ((ii.dpages*8192.0)/1073741824)/1024 'Data-only TBytes Used',
sum ((ii.dpages*8192.0)/1073741824) 'Data-only GBytes Used'
from sysobjects o
left outer join sys.indexes i on o.id = i.object_id
join sys.data_spaces d on i.data_space_id = d.data_space_id
join sysindexes ii on i.object_id = ii.id and i.index_id = ii.indid
where
o.type <> 's' -- eliminate system tables
go

-- listing of all files comprising the database; groupid 0 is logfiles
select sf.groupid,
sf.name,
sf.filename,
sf.size 'data pages',
--((sf.size*8192.0)/1048576) 'MBytes',
((sf.size*8192.0)/1073741824) 'GBytes'
from sys.sysfiles sf
order by sf.groupid,sf.filename
--
--order by sf.filename
go

-- listing of rowcount, MBytes & GBytes for all tables & index objects
select
o.name 'table',
--i.index_id 'index object id',
i.type_desc 'index object type',
i.name 'index object name', -- name = NULL indicates the size of a base table with no primary key
--i.is_unique,
--i.is_primary_key,
d.name 'dataspace name',
d.type_desc 'dataspace type',
ii.rowcnt 'row count',
ii.dpages 'data pages',
--((ii.dpages*8192.0)/1048576) 'MBytes',
((ii.dpages*8192.0)/1073741824) 'GBytes'
from sysobjects o
left outer join sys.indexes i on o.id = i.object_id
join sys.data_spaces d on i.data_space_id = d.data_space_id
join sysindexes ii on i.object_id = ii.id and i.index_id = ii.indid
where
o.type <> 's' -- eliminate system tables
and o.name not like 'queue_message%'
--and i.index_id < 2 -- counts from base tables only
order by o.name,i.index_id
go

valiky
Starting Member

3 Posts

Posted - 2009-07-27 : 10:29:38

Thank you escasey for your quick reply!
However sysfiles only returns information on the database you're currently connected to, whereas I need to connect to the master and/or msdb abd get a report for *all* the databases of the SQL server...
As I previously mentioned I cannot connect to each database, only to the master and/or msdb.

Appreciate any additional directions.

saran_d28
Starting Member

36 Posts

Posted - 2009-07-28 : 03:42:09

Hi,

You can achive this using cursor,

declare @name char(100)
declare @str char(200)
declare c1 cursor for
select name from sys.databases
open c1
fetch next from c1 into @name
while @@fetch_status =0
begin
@str = 'sp_spaceused ' + @name
sp_executesql @str
fetch next from c1 into @name
end
close c1
deallocate c1

valiky
Starting Member

3 Posts

Posted - 2009-07-29 : 03:58:15

saran_d28,

Thank you, but this does not work since you cannot execute "sp_spaceused db1" while being connected to the master.
You will get a "The object 'db1' does not exist in database 'master' or is invalid for this operation." error message.
I guess sp_spaceused can only be used to report on the database the user is currently connected to.

Any other suggestions or leads? Perhaps it's simply impossible.

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources