Re: NEED Query to Find out all Databases sizes
From: Bill (someone_at_somewhere.com)
Date: 01/07/05
- Next message: Blandis: "SQL Server Port"
- Previous message: Abel Chan: "Re: Event log error message: Database log truncated: Database"
- In reply to: rb: "NEED Query to Find out all Databases sizes"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 7 Jan 2005 13:15:54 -0800
I thought CalcSpace was a cool proc, so just for kicks, I thought I would
try it on an existing table.
The table has 7million rows and currently consumes (with indexes) 4.5gb.
The script CalcSpace estimated it would need 510mb... I didn't
investigate why this returned such a low number, or why my table is
consuming such a high number (when compared to the estimate)
Here's another method that might work for you. It uses 2 existing system
procedures. It's a little backwards, converting the strings to int's, but
it works.
Create table #tblSize (name sysname,rows int,reserved varchar(10),data
varchar(10),idx varchar(10),used varchar(10))
insert into #tblSize EXEC sp_MSforeachtable @command1='sp_spaceused ''?'''
-- select * from #tblSize order by rows desc
select sum(cast(substring(reserved,1,charindex('KB',data,1)-1) as
int) ),sum(cast(substring(data,1,charindex('KB',data,1)-1) as int) +
cast(substring(idx,1,charindex('KB',idx,1)-1) as int) ) as used
from #tblSize
Bill
"rb" <srbssr@yahoo.com> wrote in message
news:eswxO7D9EHA.368@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I want to find out the SQL databases total size in SQL 2000 server, We
> have
> around 55 databases on the server. Can anobody have the query or Stored
> proc
> to find out the total space used in the server. Thanks
> RB
>
>
>
>
- Next message: Blandis: "SQL Server Port"
- Previous message: Abel Chan: "Re: Event log error message: Database log truncated: Database"
- In reply to: rb: "NEED Query to Find out all Databases sizes"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
