I can see the database free size in properties in Enterprise manager for a database. How can I query this from system tables? I have database size in sysfiles but what about the 'size left' or 'size free' or 'size used' and 'size allocated'?

Any help is appreciated.

Thanks

This is what profiler captures:

use [master]
go
select name, DATABASEPROPERTY(name, N'IsDetached'), (case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'), (case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name, N'IsInRecovery') end), (case when DATABASEPROPERTY(name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name, N'IsNotRecovered') end), DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status, category, status2 from master.dbo.sysdatabases
go
use [TEST]
go
exec sp_MSdbuserpriv N'role'
go
use [master]
go
exec sp_MSdbuseraccess N'perm' , N'msdb'
go
exec sp_MSdbuseraccess N'perm' , N'msdb'
go
use [msdb]
go
select 1
go
set noexec off set parseonly off
go
use [TEST]
go
select sum(convert(float,size)) * (8192.0/1024.0) from dbo.sysfiles
go
exec sp_dbcmptlevel N'TEST'
go
select DISTINCT o.name, loginname = (case when (o.sid = 0x00) then NULL else l.loginname end), user_name(o.gid), o.uid, o.hasdbaccess from dbo.sysusers o left join master.dbo.syslogins l on l.sid = o.sid where ((o.issqlrole != 1 and o.isapprole != 1 and o.status != 0) or (o.sid = 0x00) and o.hasdbaccess = 1)and o.isaliased != 1 order by o.name
go
exec sp_spaceused
go
DBCC SQLPERF(LOGSPACE)
go
use [master]
go
set rowcount 1
go
set noexec off set parseonly off
go
use [msdb]
go
select backup_finish_date from backupset where type = 'D' and database_name = N'TEST' order by backup_finish_date desc
go
set noexec off set parseonly off
go
select backup_finish_date from backupset where type = 'I' and database_name = N'TEST' order by backup_finish_date desc
go
set noexec off set parseonly off
go
select backup_finish_date from backupset where type = 'L' and database_name = N'TEST' order by backup_finish_date desc
go
set noexec off set parseonly off
go
use [master]
go
set rowcount 0
go
set noexec off set parseonly off
go
use [msdb]
go
select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'TEST') and (p.plan_id = d.plan_id)
go
set noexec off set parseonly off
go
use [TEST]
go
SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0
go
DBCC sqlperf(logspace)
go
set noexec off set parseonly off
go
DBCC showfilestats
go

Sometimes you may have to run dbcc updateusage so that EM and the script that rdjabarov contributed is accurate.

The combination of sp_spaceused and dbcc sqlperf('logspace') may be sufficient depending on your needs.

HTH

Hello there,

Thanks for your suggestion but what I see in Enterprise manager for database properties (unused space) does not match with the command display sp_spaceused. There is hell a lot of difference. What is the correct data between both these displays?

Thanks in advance.

ONly way to be be sure is to run the spaceused command with the true switch or run dbcc updateusage (which would make them both the same and accurate)

HTH