Estimating the DUMP's size

[Mikhail T.]

Hello!

In order to schedule the dumps efficiently I'm trying to estimate their
sizes. I assume, they will be about as large as the amount of space the
databases _use_.

How can obtain this information with reasonable accuracy (+/- 10%)? The
following query seems to work sometimes, but is woefully off at other times
(producing NEGATIVE results on occasion):

select name, sum(size - unreservedpgs) from sysdatabases,\
sysusages where sysusages.dbid = sysdatabases.dbid group by name

What's the right way to do it? Thanks!

-mi

[m***@peppler.org]

Post by Mikhail T.
Hello!
In order to schedule the dumps efficiently I'm trying to
estimate their sizes. I assume, they will be about as
large as the amount of space the databases _use_.
How can obtain this information with reasonable accuracy
(+/- 10%)? The following query seems to work sometimes,
but is woefully off at other times (producing NEGATIVE

I use this:

use dbname
go
declare @pgsiz int
declare @logsiz float, @datasiz float

select @pgsiz = low from master..spt_values where number = 1
and type = 'E'
select @logsiz = convert(float, reserved_pgs(s.id,
s.doampg))
from sysindexes s
where id = 8

select @datasiz = sum(convert(float, reserved_pgs(s.id,
s.doampg) + reserved_pgs(s.id, s.ioampg)))
from sysindexes s
where s.id != 0

select ((@datasiz + @logsiz) * @pgsiz) / (1024.0 * 1024.0)
go

Michael