MS-SQL(MSDE) How can i see with a select the available database space

[vanhulst]

Is it possible to select the available space of the database in a microsoft sql MSDE edition?

With sysfiles i can see the size of the database but where can i find the available space or the used space of the database.

[MAK]

--For datafile
declare @query varchar(200)
set @query ='DBCC showfilestats'
create table #mytable(Fileid int, FileGroup int, Totalextents bigint,UsedExtents bigint,Name varchar(256),Filename varchar(600))
insert #mytable exec (@query)
select Name,Filename,(TotalExtents*64)/1024 as TotalMB,(Usedextents*64)/1024 as UsedMB,
Free= ((TotalExtents*64) - (Usedextents*64))/1024,
FreePercent=(((TotalExtents*64*1.0) - (Usedextents*64*1.0))/(TotalExtents*64*1.0))*100
from #mytable
drop table #mytable

--For Logfile

dbcc sqlperf(logspace)

[skhanal]

You can run sp_spaceused to get an estimate. But run dbcc updateusage to get upto date data.

[vanhulst]

Thanks it works perfectly