Re: Full v. simple recovery mode

Hello,

As you saif if you backup the transaction log in frequent interval then you
can keep the LDF file under control during Index analyzes / Index rebuild.

Regarding FULL VS Simple:- First of all if it is production I will
recommend you to go for FULL recovery model. Full recovery
model with the help of transaction Log backup will help you to :-

1.recover your database if there is any database/server crash
2. Will help you to do Point_In_Time recovery. This helps incase some does
an invalid update / delete

I suggest you to do ,below:-

1. Do DBCC CHECKDB daily when user access is less. Schedule this as a SQL
Server job. DB do not want to be in single user

2. Do a reindex every week. FOr SQL 2000 for this database should not have
any access. You could use ALTER DATABASE DBNAME SET SINGLE_USER command

Thanks
Hari

"TPetkus" <TPetkus@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8408153E-0C53-41A5-99AA-3DEDA5830655@xxxxxxxxxxxxxxxx
We use SQL 2000 as part of Small Business Server 2003 Premium (up to
date).
We are rank amateurs when it comes to SQL.

I am trying to run weekly database maintenance against a SQL db using the
SQL maintenance utility wizard. One of the routines I set up is to check
db
intergrity and make simple repairs, if needed. This always fails due to
the
db not being in single user recovery mode. The sp_dboption command fails
to
change the db to single user mode stating that while other users are using
the database it cannot be changed (yet there aren't any other users logged
in). The check table index linkage command does not execute.

I read that if I change the recovery mode from simple to full, I can avoid
the error altogether. Other than the risk of huge log files gobbling up
all
available disk space, is the any reason not to change a db to full
recovery
mode from simple?

I understand that if I have a regular routine to backup the transaction
log,
its size will not get out of control .

I would like to know what risks there are from a setting with full
recovery
vs. simple recovery mode. If someone can suggest a better alternative, I
would welcome any advice.

Thank you.

Tomas



.

Relevant Pages

  • Re: Restoring from non-truncated transaction log
    ... Hi - I suggest you read the Books Online topics on recovery models ... > NOT been backing up the transaction log, so I would assume the transaction ... > transaction log when you restore a database but not the transaction log? ...
    (microsoft.public.sqlserver.server)
  • Re: optimum log file size
    ... Fixing of Transaction log file based up on the amount of bulk transaction ... CUrrent log growth will be because of FULL recovery mode. ... If you do not require a point in time recovery or if your database ...
    (microsoft.public.sqlserver.clients)
  • Re: Log-size
    ... The user had an immediate problem: their transaction log filewere ... It is more valuable than the online database. ... periodic transaction log backups, I can recover that database from the FULL ... change the recovery model to support their ...
    (microsoft.public.sqlserver.server)
  • Re: Suspect / Emergency Mode
    ... if ur recovery is Simple then you can not even ... read the transaction log using Log reader tool. ... Since the database is recovered in emergency mode, ...
    (microsoft.public.sqlserver.server)
  • Re: NTDS ISAM DataBase Corruption
    ... Did you tried the recover the corrupted Active Directory process that a gave ... This is brand new IBM Server. ... using symantec livestate recovery. ... Active Directory database is unavailable because it is damaged, ...
    (microsoft.public.windows.server.active_directory)
Quantcast