Backups doubling and halfing in size

22 Oct 2005 8:48 AM

hals_left

This is probably something really obvious, but can someone tell me
whats going on.

I do daily full backups of a database to zip and looking at the sizes,
they are growing at the normal rate but every other day halfing:

day 1 4Mb
day 2 7Mb
day 3 4Mb
day 4 9Mb
day 5 5Mb
day 6 11Mb
etc

Is this normal ?

The database is at 18Mb now & has approx 5% of the annual transaction
volume - 2000 of 100,000pa in invoices and 10,000 of 500,000 in invoice
lines.

I plan to keep 3ys of data in these tables, removing the oldest
transactions to an archive database of the same structure on a
different IP. Does this sound reasonable, can I predict performance of
the app with 3ys data ? Is this a small database for SQL2K ?

Thanks.

22 Oct 2005 12:49 PM

John Bell

Hi

You may have some additional task that shrinks/reorganizes the data/log
files on these days.

John

Show quote

"hals_left" wrote:

> This is probably something really obvious, but can someone tell me
> whats going on.
>
> I do daily full backups of a database to zip and looking at the sizes,
> they are growing at the normal rate but every other day halfing:
>
> day 1 4Mb
> day 2 7Mb
> day 3 4Mb
> day 4 9Mb
> day 5 5Mb
> day 6 11Mb
> etc
>
> Is this normal ?
>
> The database is at 18Mb now & has approx 5% of the annual transaction
> volume - 2000 of 100,000pa in invoices and 10,000 of 500,000 in invoice
> lines.
>
> I plan to keep 3ys of data in these tables, removing the oldest
> transactions to an archive database of the same structure on a
> different IP. Does this sound reasonable, can I predict performance of
> the app with 3ys data ? Is this a small database for SQL2K ?
>
> Thanks.
>
>

22 Oct 2005 2:40 PM

Erland Sommarskog

hals_left (cc900***@ntu.ac.uk) writes:

> This is probably something really obvious, but can someone tell me
> whats going on.
>
> I do daily full backups of a database to zip and looking at the sizes,
> they are growing at the normal rate but every other day halfing:

How do you perform these backups? The BACKUP command in SQL Server?
Something else?

> The database is at 18Mb now & has approx 5% of the annual transaction
> volume - 2000 of 100,000pa in invoices and 10,000 of 500,000 in invoice
> lines.
>
> I plan to keep 3ys of data in these tables, removing the oldest
> transactions to an archive database of the same structure on a
> different IP. Does this sound reasonable, can I predict performance of
> the app with 3ys data ? Is this a small database for SQL2K ?

18 MB is a very small database indeed.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp