How to Compute Actual Space Used In Datafiles

Has anyone discovered the algorithm and the appropriate system
tables/columns to use to compute the actual amount of space used/remaining
within a database datafile?
I'm trying to hit out to the values displayed in the "Taskpad" view in
Enterprise Manager and want to write a routine around those values.
Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
You can build some really good stuff around that command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv .net...
> Has anyone discovered the algorithm and the appropriate system
> tables/columns to use to compute the actual amount of space used/remaining
> within a database datafile?
> I'm trying to hit out to the values displayed in the "Taskpad" view in
> Enterprise Manager and want to write a routine around those values.
>
|||Have you looked at sp_spaceused ?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
> You can build some really good stuff around that command.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv .net...
|||Yes. It has one huge glaring weakness. It does not look at space within a
filegroup. Knowing that a database has 70% free space is useless if I have
a filegroup that is full. With DBCCSHOWFILESTATS, I was able to aggregate
data usage within files and filegroups to get a meaningful space used/free
report.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0405060059.20b88b1e@.posting.google.c om...
> Have you looked at sp_spaceused ?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
display.[vbcol=seagreen]
used/remaining[vbcol=seagreen]

How to Compute Actual Space Used In Datafiles

Has anyone discovered the algorithm and the appropriate system
tables/columns to use to compute the actual amount of space used/remaining
within a database datafile?
I'm trying to hit out to the values displayed in the "Taskpad" view in
Enterprise Manager and want to write a routine around those values.Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
You can build some really good stuff around that command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> Has anyone discovered the algorithm and the appropriate system
> tables/columns to use to compute the actual amount of space used/remaining
> within a database datafile?
> I'm trying to hit out to the values displayed in the "Taskpad" view in
> Enterprise Manager and want to write a routine around those values.
>|||Have you looked at sp_spaceused ?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<u#PQovwMEHA.3944@.tk2msftng
p13.phx.gbl>...[vbcol=seagreen]
> Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display
.
> You can build some really good stuff around that command.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...|||Yes. It has one huge glaring weakness. It does not look at space within a
filegroup. Knowing that a database has 70% free space is useless if I have
a filegroup that is full. With DBCCSHOWFILESTATS, I was able to aggregate
data usage within files and filegroups to get a meaningful space used/free
report.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0405060059.20b88b1e@.posting.google.com...
> Have you looked at sp_spaceused ?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
display.[vbcol=seagreen]
used/remaining[vbcol=seagreen]

How to Compute Actual Space Used In Datafiles

Has anyone discovered the algorithm and the appropriate system
tables/columns to use to compute the actual amount of space used/remaining
within a database datafile?
I'm trying to hit out to the values displayed in the "Taskpad" view in
Enterprise Manager and want to write a routine around those values.Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
You can build some really good stuff around that command.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> Has anyone discovered the algorithm and the appropriate system
> tables/columns to use to compute the actual amount of space used/remaining
> within a database datafile?
> I'm trying to hit out to the values displayed in the "Taskpad" view in
> Enterprise Manager and want to write a routine around those values.
>|||Have you looked at sp_spaceused ?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...
> Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
> You can build some really good stuff around that command.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> > Has anyone discovered the algorithm and the appropriate system
> > tables/columns to use to compute the actual amount of space used/remaining
> > within a database datafile?
> > I'm trying to hit out to the values displayed in the "Taskpad" view in
> > Enterprise Manager and want to write a routine around those values.
> >
> >|||Yes. It has one huge glaring weakness. It does not look at space within a
filegroup. Knowing that a database has 70% free space is useless if I have
a filegroup that is full. With DBCCSHOWFILESTATS, I was able to aggregate
data usage within files and filegroups to get a meaningful space used/free
report.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0405060059.20b88b1e@.posting.google.com...
> Have you looked at sp_spaceused ?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...
> > Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that
display.
> > You can build some really good stuff around that command.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> > "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> > news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> > > Has anyone discovered the algorithm and the appropriate system
> > > tables/columns to use to compute the actual amount of space
used/remaining
> > > within a database datafile?
> > > I'm trying to hit out to the values displayed in the "Taskpad" view in
> > > Enterprise Manager and want to write a routine around those values.
> > >
> > >

How to compute a half year to full year comparison?

Hello everyone,

Here is the formula I am trying to:

(the actual cost from Jan to Jun) / (the budget from Jan to Dec)

If I use → Head ( Descedants ( [Date].CurrentMember, [Date].[Month] ) , 6 ) , I can get the first 6 months of that year. However, if the time data is not complete, e.g., there's only July to Dec on 2004, the months I get will be July to Dec and that won't be the output I expect.

Is there a better way to the question? Any idea would be appreciated.

Thanks,
Hank

Hi Hank,

you could create a new attribute for the month number (vales 1-12) with the sql-function Month()

then select (in mdx) the range from monthnumber 1 to monthnumber 6 an join it with the current year and sum that set

so your half-year value would be calculated as follows:

sum({[Time].[MonthNumber].[1]:[Time].[MonthNumber].[12]}*[Time].[Year].[2004])

should work but no warranty

greets

gerhard

|||

Thanks a lot, Gerhard.

I didn't think about using a join with the current year. This solves my problem.

Regards,

Hank