May 1, 2008 at 10:33 am
I am trying to figure out how to get space used for a database in SQL 2005.
In SQL 2000, i used:
select sum(used)*8/1024 from [master]..sysindexes WHERE indid in (0, 1, 255)
go
but this does not work for some databases SQL 2005. then i tried the following command:
SELECT sum(page_count)*8/1024 FROM sys.dm_db_index_physical_stats (DB_ID('master'), NULL, NULL, NULL, 'DETAILED');
go
which return 0 for small databases.
Can anyone tell me how to get accurate DB space used information from SQL 2005?
Many thanks!
Li
May 1, 2008 at 11:43 am
You can use couple of things:
1. sp_spaceused -- run it in context of your db
2. Here is a script which I use for myself. If you want on kb level, remove the math that calculates it to MB.
select
num = row_number() over (order by sum(f.size)*8/1024 desc),
database_name = left(d.name,30),
size_MB = sum(f.size)*8/1024
from
sys.databases d join sys.master_files f on
d.database_id = f.database_id
group by
d.name
order by
num
compute sum(sum(f.size)*8/1024)
May 1, 2008 at 1:01 pm
Thanks Old Hand!
This give the total DB size(MB). Is there a way to find out the used space in a DB?
May 1, 2008 at 1:11 pm
The reason you're getting 0 on small databases is because this, "sum(page_count)*8/1024" is integer math, and any number smaller than 128 for sum(page_count) will give 0.
Cast the page count as Float, instead of Int, and it should give you better results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply