advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
MikeSet the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:
>I have a database that i make a copy of everynight. I delete all the tables
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:
> I have a database that i make a copy of everynight. I delete all the tables
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore my
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreate
> the whole database by running the dts package (this only takes 10 minutes).
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>