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?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
---------------------------------------|||Off topic, but you can use the 'code' tag when posting code i.e

This is my code
with some indentation
and other stuff ;)
so the format
still looks nice :D|||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 ;-)|||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.|||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?

displaying the list of databases faster in enterprise manager console

Hello,
We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
On this server we have 59 databases.
Different user's pc connect to it using enterprise manager. Client PC are
mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
Each time one of these clients connect to the server to display the list of
databases, it takes about 45sec to 1.15 min. I beleive this behaviour comes
from the increasing number of DB (it was much faster when we had about 5 to
10 db). Is there a way to "cache" this catalog (or any other way) in order
to displays this list faster?
thanks
Have you considered using Query Analyzer to look at the list?
http://www.aspfaq.com/2455
Also, make sure you don't have auto-close set for any of the databases.
This forces EM to take a lot longer than it should to show the list...
http://www.aspfaq.com/2469
http://www.aspfaq.com/
(Reverse address to reply.)
<grille11@.yahoo.com> wrote in message
news:cjjpqo$aeh$1@.reader1.imaginet.fr...
> Hello,
> We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
> On this server we have 59 databases.
> Different user's pc connect to it using enterprise manager. Client PC are
> mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
> Each time one of these clients connect to the server to display the list
of
> databases, it takes about 45sec to 1.15 min. I beleive this behaviour
comes
> from the increasing number of DB (it was much faster when we had about 5
to
> 10 db). Is there a way to "cache" this catalog (or any other way) in
order
> to displays this list faster?
> thanks
>

displaying the list of databases faster in enterprise manager console

Hello,
We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
On this server we have 59 databases.
Different user's pc connect to it using enterprise manager. Client PC are
mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
Each time one of these clients connect to the server to display the list of
databases, it takes about 45sec to 1.15 min. I beleive this behaviour comes
from the increasing number of DB (it was much faster when we had about 5 to
10 db). Is there a way to "cache" this catalog (or any other way) in order
to displays this list faster?
thanksHave you considered using Query Analyzer to look at the list?
http://www.aspfaq.com/2455
Also, make sure you don't have auto-close set for any of the databases.
This forces EM to take a lot longer than it should to show the list...
http://www.aspfaq.com/2469
--
http://www.aspfaq.com/
(Reverse address to reply.)
<grille11@.yahoo.com> wrote in message
news:cjjpqo$aeh$1@.reader1.imaginet.fr...
> Hello,
> We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
> On this server we have 59 databases.
> Different user's pc connect to it using enterprise manager. Client PC are
> mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
> Each time one of these clients connect to the server to display the list
of
> databases, it takes about 45sec to 1.15 min. I beleive this behaviour
comes
> from the increasing number of DB (it was much faster when we had about 5
to
> 10 db). Is there a way to "cache" this catalog (or any other way) in
order
> to displays this list faster?
> thanks
>