Calculate the total DB size - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

Calculate the total DB size

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2002-06-05 : 17:25:26

Hello! ALL,

I have a SQL server(Let's say A server) that has 10 different Database, and would like to know the total DB size in A server. Is there any System function or procedure can calculate the total DB size?

Thanks
John

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-06 : 03:34:37

Yeah, find the size of your mssql/data directory, you could use a little VB for this....

Or look at the sp_databases proc....

Peace

Rick

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-06 : 08:49:19

use master
go

create proc sp_alldbsize
as
set nocount on
create table #temp([name] sysname,
db_size varchar(15),
owner varchar(50),
dbid int,
created datetime,
status varchar(1000),
compat int)
insert #temp exec sp_helpdb

select SUM(cast(LTRIM(RTRIM(LEFT(db_size,(LEN(db_size)-3)))) as numeric(18,2)))
as 'Total DB Size' from #temp

drop table #temp

return
go

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-06 : 09:41:28

Jay99 offered up this beautiful proc that will tell you what you want, plus some.

I'm gonna go pour out a 40oz for Jay99(RIP) right now . . .

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources