Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Unanswered: SQLServer 2000 database free size

    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

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    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
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    SQLSERver database free size

    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.

  5. #5
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    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
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •