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 % in value

Hi

I am calculating percentage using expression. I want to display % at the end of the result.

If I use P1 or P0 somehow it multiplies the result with 100 I guess.

Here is what my calculated value is

15.384515

and I want to display 15.38%

Using P1 or P0 I am getting 1,538.5%

How can I get the desired result?

Any help would be appreciated.

Regards

Amit

Yeah I always thought that was funny the way RS handles formating %.

You can try dividing your value by 100 and them formating it. I think the percent format requires a number between 0 and 1

e.d =format((field.value)/100,"P0")

that should give you "15%"

|||

You are great Nialhannon.

Thanks for your help. Your solution helped.

Regards

Amit

|||

I remember those issues, and I ended up doing the /100 in the dataset (or Stored Procs)

|||

Hi niallhannon,

I also have a similar problem in formatiing the data after rendering to excel.

The Field value is a percentage.

If there isn't any value zeros are getting displayed.

So if i use a format expression #,# (not to display anything in the cell),the percentage field is getting disturbed and is not getting displyed with two decimal places.

Kindly help me out in this issue.Its a bit urgent.

Thanks in advance

Nalini

displaying % in value

Hi

I am calculating percentage using expression. I want to display % at the end of the result.

If I use P1 or P0 somehow it multiplies the result with 100 I guess.

Here is what my calculated value is

15.384515

and I want to display 15.38%

Using P1 or P0 I am getting 1,538.5%

How can I get the desired result?

Any help would be appreciated.

Regards

Amit

Yeah I always thought that was funny the way RS handles formating %.

You can try dividing your value by 100 and them formating it. I think the percent format requires a number between 0 and 1

e.d =format((field.value)/100,"P0")

that should give you "15%"

|||

You are great Nialhannon.

Thanks for your help. Your solution helped.

Regards

Amit

|||

I remember those issues, and I ended up doing the /100 in the dataset (or Stored Procs)

Display percentage % field and format

Hi,
In the group footer I want to show:
Total store with ABC systems is: 33% of Total stores
33% is a calculated field.
The expression for this is:
"Total store with ABC systems is: " & =Fields!A1.Value/Fields!B1.Value
& " of Total stores"
I merged 3 cells and put this expression is in one text box, this
output would be:
summary 0.33333333333 of Total stores
So I split the cell into three:
in first cell: Total store with ABC systems is:
in second cell: =Fields!A1.Value/Fields!B1.Value (format set to p0)
in third cell: of Total stores
but the width of percentage field is limited by the table column, so
the 33% would be either too far from the first cell or from the third
cell.
What can I do to concatenate them into one line without too much
space?
Thanks in advance.
DanniTry this: ="Percentage is " &
Format((Fields!A1.Value/Fields!B1.Value)/100.0, "p") & " of Total stores"
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danni Liao" <danniliao@.yahoo.com> wrote in message
news:9d716b4a.0408231056.2d1b695e@.posting.google.com...
> Hi,
> In the group footer I want to show:
> Total store with ABC systems is: 33% of Total stores
> 33% is a calculated field.
> The expression for this is:
> "Total store with ABC systems is: " & =Fields!A1.Value/Fields!B1.Value
> & " of Total stores"
> I merged 3 cells and put this expression is in one text box, this
> output would be:
> summary 0.33333333333 of Total stores
> So I split the cell into three:
> in first cell: Total store with ABC systems is:
> in second cell: =Fields!A1.Value/Fields!B1.Value (format set to p0)
> in third cell: of Total stores
> but the width of percentage field is limited by the table column, so
> the 33% would be either too far from the first cell or from the third
> cell.
> What can I do to concatenate them into one line without too much
> space?
>
> Thanks in advance.
> Danni|||Or you could leave out the /100.0 and go to
Properties => Format => enter p2 for percent with a matissa of 2 places.
Bryan
"Ravi Mumulla (Microsoft)" wrote:
> Try this: ="Percentage is " &
> Format((Fields!A1.Value/Fields!B1.Value)/100.0, "p") & " of Total stores"
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Danni Liao" <danniliao@.yahoo.com> wrote in message
> news:9d716b4a.0408231056.2d1b695e@.posting.google.com...
> > Hi,
> >
> > In the group footer I want to show:
> >
> > Total store with ABC systems is: 33% of Total stores
> >
> > 33% is a calculated field.
> >
> > The expression for this is:
> > "Total store with ABC systems is: " & =Fields!A1.Value/Fields!B1.Value
> > & " of Total stores"
> > I merged 3 cells and put this expression is in one text box, this
> > output would be:
> > summary 0.33333333333 of Total stores
> >
> > So I split the cell into three:
> > in first cell: Total store with ABC systems is:
> >
> > in second cell: =Fields!A1.Value/Fields!B1.Value (format set to p0)
> >
> > in third cell: of Total stores
> >
> > but the width of percentage field is limited by the table column, so
> > the 33% would be either too far from the first cell or from the third
> > cell.
> >
> > What can I do to concatenate them into one line without too much
> > space?
> >
> >
> > Thanks in advance.
> > Danni
>
>