master..sysaltfiles in 2000 vs 2005

27 Jul 2006 7:16 PM

Matthew

I have this little snippet of code that I am using to get some
information on database growth sizes, in SQL 2005 the code runs
correctly. But when I take the same code and run it on a SQL 2000
system the growths sizes are all messed up.

[Code]
SELECT
    FileGrowth          = CASE WHEN status <= 0x100000
                               THEN convert(decimal(9,2),growth)*8
                               ELSE convert(decimal(9,2),growth)*size*8/100 END,
    FileGrowthPercent   = CASE WHEN status <= 0x100000
                               THEN convert(decimal(9,2),growth)/size*100
                                  ELSE convert(decimal(9,2),growth) END,
FROM master..sysaltfiles
[/Code]

28 Jul 2006 3:30 AM

Uri Dimant

Matthew
Hmmm, it could be ,because of using sysaltfiles system table. Try using
sysfiles instead to see what is going on
Loop thru all user databases

exec master.dbo.sp_msforeachdb 'select @@servername as Servername,''?'' as
Databasename,
getdate() as TimeStamps, * from ?.dbo.sysfiles'

Show quote

"Matthew" <MKr***@gmail.com> wrote in message
news:1154027801.312725.99670@b28g2000cwb.googlegroups.com...
>I have this little snippet of code that I am using to get some
> information on database growth sizes, in SQL 2005 the code runs
> correctly. But when I take the same code and run it on a SQL 2000
> system the growths sizes are all messed up.
>
> [Code]
> SELECT
> FileGrowth          = CASE WHEN status <= 0x100000
>        THEN convert(decimal(9,2),growth)*8
>        ELSE convert(decimal(9,2),growth)*size*8/100 END,
> FileGrowthPercent   = CASE WHEN status <= 0x100000
>    THEN convert(decimal(9,2),growth)/size*100
>      ELSE convert(decimal(9,2),growth) END,
> FROM master..sysaltfiles
> [/Code]
>

31 Jul 2006 9:51 PM

Matthew

Thanks for the reply,

Actually I think I figured it out. Don't ask why but in 2005 the
Query needs to go "CASE WHEN status <= 0x100000" But in 2000 it
needs to be CASE WHEN status >= 0x100000. Then it will work. However I
think, (but I need this confirmed) I can use 32 because 0x100000 == 32.
This appears to be the case, but I need to verify it. "If status is
0x100000, growth is the percentage of file size; otherwise, it is the
number of pages." So I might need to change the query to CASE WHEN
status & 32.

Uri Dimant wrote:
Show quote

> Matthew
> Hmmm, it could be ,because of using sysaltfiles system table. Try using
> sysfiles instead to see what is going on
> Loop thru all user databases
>
> exec master.dbo.sp_msforeachdb 'select @@servername as Servername,''?'' as
> Databasename,
>   getdate() as TimeStamps, * from ?.dbo.sysfiles'
>
>
>
>
> "Matthew" <MKr***@gmail.com> wrote in message
> news:1154027801.312725.99670@b28g2000cwb.googlegroups.com...
> >I have this little snippet of code that I am using to get some
> > information on database growth sizes, in SQL 2005 the code runs
> > correctly. But when I take the same code and run it on a SQL 2000
> > system the growths sizes are all messed up.
> >
> > [Code]
> > SELECT
> > FileGrowth          = CASE WHEN status <= 0x100000
> >        THEN convert(decimal(9,2),growth)*8
> >        ELSE convert(decimal(9,2),growth)*size*8/100 END,
> > FileGrowthPercent   = CASE WHEN status <= 0x100000
> >    THEN convert(decimal(9,2),growth)/size*100
> >      ELSE convert(decimal(9,2),growth) END,
> > FROM master..sysaltfiles
> > [/Code]
> >