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?
Showing posts with label space. Show all posts
Showing posts with label space. Show all posts
Wednesday, March 21, 2012
Displaying used space in Q.A.
Labels:
database,
display,
displaying,
enterprise,
files,
log,
mgr,
microsoft,
mysql,
oracle,
percentage,
server,
space,
sql,
taskpad
Friday, February 24, 2012
Display space on report server
Hi,
In my report summary I need to display space. The output looks like:
Total Restaurants: 20
Total seats : 315
I'm using
="Total Restaurants: " & Sum(Fields!Restaurant.Value)
="Total seats: " & Sum(Fields!Seats.Value)
It looks fine in the preview but after I deployed it on the report
server, it doesn't look right on the server:
Total Restaurants: 20
Total seats: 315
The spaces after "Total seats" got eaten up.
I tried to use Space(5) after "Total seats" but it still doesn't work.
Is there way to do this other than spliting them into two fields?
Thanks in advance.
DanniHi,
I'm sorry but I still don't know how to use "ALT+255".
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> If it would be urget, I would have used "ALT+255" instead of SPACE
> "Danni Liao" wrote:
> > Hi,
> > In my report summary I need to display space. The output looks like:
> >
> > Total Restaurants: 20
> > Total seats : 315
> >
> > I'm using
> > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > ="Total seats: " & Sum(Fields!Seats.Value)
> >
> > It looks fine in the preview but after I deployed it on the report
> > server, it doesn't look right on the server:
> >
> > Total Restaurants: 20
> > Total seats: 315
> >
> > The spaces after "Total seats" got eaten up.
> >
> > I tried to use Space(5) after "Total seats" but it still doesn't work.
> >
> > Is there way to do this other than spliting them into two fields?
> >
> >
> > Thanks in advance.
> > Danni
> >|||Hold ALT key and press 255 on num pad and then release ALT key. Hope this
help..
"Danni Liao" wrote:
> Hi,
> I'm sorry but I still don't know how to use "ALT+255".
> "Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> > If it would be urget, I would have used "ALT+255" instead of SPACE
> >
> > "Danni Liao" wrote:
> >
> > > Hi,
> > > In my report summary I need to display space. The output looks like:
> > >
> > > Total Restaurants: 20
> > > Total seats : 315
> > >
> > > I'm using
> > > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > > ="Total seats: " & Sum(Fields!Seats.Value)
> > >
> > > It looks fine in the preview but after I deployed it on the report
> > > server, it doesn't look right on the server:
> > >
> > > Total Restaurants: 20
> > > Total seats: 315
> > >
> > > The spaces after "Total seats" got eaten up.
> > >
> > > I tried to use Space(5) after "Total seats" but it still doesn't work.
> > >
> > > Is there way to do this other than spliting them into two fields?
> > >
> > >
> > > Thanks in advance.
> > > Danni
> > >
>|||Hi Ameet:
Thank you for your reply.
I need 5 white spaces so according to your suggestion I used:
="Total seats:" & "ALT+255 4" & Sum(Fields!Seats.Value) in the
expression of that textbox, I don't see the "ALT+255" shown and the
result is:
Total seats:4201
So it still doesn't work.
I think this is a bug in the reporting services.
Danni
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<3AB33890-3B65-4C6F-9A21-622AE7E9B3C4@.microsoft.com>...
> Hold ALT key and press 255 on num pad and then release ALT key. Hope this
> help..
> "Danni Liao" wrote:
> > Hi,
> > I'm sorry but I still don't know how to use "ALT+255".
> >
> > "Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> > > If it would be urget, I would have used "ALT+255" instead of SPACE
> > >
> > > "Danni Liao" wrote:
> > >
> > > > Hi,
> > > > In my report summary I need to display space. The output looks like:
> > > >
> > > > Total Restaurants: 20
> > > > Total seats : 315
> > > >
> > > > I'm using
> > > > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > > > ="Total seats: " & Sum(Fields!Seats.Value)
> > > >
> > > > It looks fine in the preview but after I deployed it on the report
> > > > server, it doesn't look right on the server:
> > > >
> > > > Total Restaurants: 20
> > > > Total seats: 315
> > > >
> > > > The spaces after "Total seats" got eaten up.
> > > >
> > > > I tried to use Space(5) after "Total seats" but it still doesn't work.
> > > >
> > > > Is there way to do this other than spliting them into two fields?
> > > >
> > > >
> > > > Thanks in advance.
> > > > Danni
> > > >
> >
In my report summary I need to display space. The output looks like:
Total Restaurants: 20
Total seats : 315
I'm using
="Total Restaurants: " & Sum(Fields!Restaurant.Value)
="Total seats: " & Sum(Fields!Seats.Value)
It looks fine in the preview but after I deployed it on the report
server, it doesn't look right on the server:
Total Restaurants: 20
Total seats: 315
The spaces after "Total seats" got eaten up.
I tried to use Space(5) after "Total seats" but it still doesn't work.
Is there way to do this other than spliting them into two fields?
Thanks in advance.
DanniHi,
I'm sorry but I still don't know how to use "ALT+255".
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> If it would be urget, I would have used "ALT+255" instead of SPACE
> "Danni Liao" wrote:
> > Hi,
> > In my report summary I need to display space. The output looks like:
> >
> > Total Restaurants: 20
> > Total seats : 315
> >
> > I'm using
> > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > ="Total seats: " & Sum(Fields!Seats.Value)
> >
> > It looks fine in the preview but after I deployed it on the report
> > server, it doesn't look right on the server:
> >
> > Total Restaurants: 20
> > Total seats: 315
> >
> > The spaces after "Total seats" got eaten up.
> >
> > I tried to use Space(5) after "Total seats" but it still doesn't work.
> >
> > Is there way to do this other than spliting them into two fields?
> >
> >
> > Thanks in advance.
> > Danni
> >|||Hold ALT key and press 255 on num pad and then release ALT key. Hope this
help..
"Danni Liao" wrote:
> Hi,
> I'm sorry but I still don't know how to use "ALT+255".
> "Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> > If it would be urget, I would have used "ALT+255" instead of SPACE
> >
> > "Danni Liao" wrote:
> >
> > > Hi,
> > > In my report summary I need to display space. The output looks like:
> > >
> > > Total Restaurants: 20
> > > Total seats : 315
> > >
> > > I'm using
> > > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > > ="Total seats: " & Sum(Fields!Seats.Value)
> > >
> > > It looks fine in the preview but after I deployed it on the report
> > > server, it doesn't look right on the server:
> > >
> > > Total Restaurants: 20
> > > Total seats: 315
> > >
> > > The spaces after "Total seats" got eaten up.
> > >
> > > I tried to use Space(5) after "Total seats" but it still doesn't work.
> > >
> > > Is there way to do this other than spliting them into two fields?
> > >
> > >
> > > Thanks in advance.
> > > Danni
> > >
>|||Hi Ameet:
Thank you for your reply.
I need 5 white spaces so according to your suggestion I used:
="Total seats:" & "ALT+255 4" & Sum(Fields!Seats.Value) in the
expression of that textbox, I don't see the "ALT+255" shown and the
result is:
Total seats:4201
So it still doesn't work.
I think this is a bug in the reporting services.
Danni
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<3AB33890-3B65-4C6F-9A21-622AE7E9B3C4@.microsoft.com>...
> Hold ALT key and press 255 on num pad and then release ALT key. Hope this
> help..
> "Danni Liao" wrote:
> > Hi,
> > I'm sorry but I still don't know how to use "ALT+255".
> >
> > "Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> > > If it would be urget, I would have used "ALT+255" instead of SPACE
> > >
> > > "Danni Liao" wrote:
> > >
> > > > Hi,
> > > > In my report summary I need to display space. The output looks like:
> > > >
> > > > Total Restaurants: 20
> > > > Total seats : 315
> > > >
> > > > I'm using
> > > > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > > > ="Total seats: " & Sum(Fields!Seats.Value)
> > > >
> > > > It looks fine in the preview but after I deployed it on the report
> > > > server, it doesn't look right on the server:
> > > >
> > > > Total Restaurants: 20
> > > > Total seats: 315
> > > >
> > > > The spaces after "Total seats" got eaten up.
> > > >
> > > > I tried to use Space(5) after "Total seats" but it still doesn't work.
> > > >
> > > > Is there way to do this other than spliting them into two fields?
> > > >
> > > >
> > > > Thanks in advance.
> > > > Danni
> > > >
> >
Subscribe to:
Posts (Atom)