Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    229

    Unanswered: Displaying used space in Q.A.

    I would like to get information about percentage used space for data files and log files, like I can display it in Enterprise Mgr's TaskPad, through Query Analyzer. The 'sysfiles' system table only contains allocated space, not the % used.
    Does anyone know if this info is available this way?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    To my knowledge the % of space used is not stored in any tables. As I am sure you know DBCC SQLPERF(LOGSPACE) will give you the answer BUT for ALL logs. You could always roll your own custom answer:

    Code:
    --------------------------------------------------------------------------------------------------------------------------------
    create table #Tmp(DB varchar(255), LogSize varchar(25), SpaceUSed varchar(25), Status tinyint)
    declare @DBLen int, @LogSizeLen int, @SpaceUsedLen int, @StatusLen int, @TSQL varchar(255)
    insert into #Tmp exec('DBCC SQLPERF(LOGSPACE)')
    select @DBLen = max(datalength(DB)), @LogSizeLen = max(datalength(LogSize)), @SpaceUSedLen = max(datalength(SpaceUSed)), @StatusLen = max(datalength(Status)) From #Tmp where DB = db_name()
    set @TSQL = 'select ' +
    'cast(DB as varchar( ' + cast(@DBLen as varchar(12)) + ')) as ''Database Name'', ' +
    'cast(LogSize as varchar( ' + cast(@LogSizeLen as varchar(12)) + ')) as ''Log Size (MB)'', ' +
    'cast(SpaceUsed as varchar( ' + cast(@SpaceUsedLen as varchar(12)) + ')) as ''Log Space Used (%)'', ' +
    'cast(Status as varchar( ' + cast(@StatusLen as varchar(12)) + ')) as ''Status'' ' +
    'From #Tmp where DB = db_name()'
    raiserror('',0,1)
    exec(@TSQL)
    drop table #Tmp
    --------------------------------------------------------------------------------------------------------------------------------
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    Off topic, but you can use the 'code' tag when posting code i.e

    Code:
    This is my code
       with some indentation
          and other stuff ;)
       so the format
    still looks nice :D

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    Thank you very much. I was browsing through BOL yesterday but I missed DBCC SQLPERF for some reason.

    I guess I can get the same info for database files through sp_spaceused, which I now stumbled over in BOL.
    I tested running DBCC UPDATEUSAGE on some databases, which gave some corrections. Are these "problems" in sysindexes never corrected unless I explicitly run a DBCC UPDATEUSAGE manually or in a maintenance plan?

    Sigh... when the guys you've outsourced the operation & maintenance to can't supervise things, you've got to do it yourself ;-)
    Last edited by Coolberg; 09-13-02 at 06:22.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I run a maintaince plan every Saturday and Wednesday to re-calculate statistics, rebuild indexes, and force a recompile of all stored procedures. I am lucky to have a window twice a week where I can do these things and IMHO the 2 hours it takes to do this is time well spent.

    Here is another tip. If you find something in EM that you like but don't know how it works try running the profiler and watch the commands issued.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Jul 2002
    Posts
    229
    About sp_spaceusage, which is the most important detail to keep an eye on of Unallocated Space and Unused Space?

    Further, for one database I got a negative value for Unallocated Space. What does this mean?

Posting Permissions

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