Recovery Mode

Question

Recovery Mode

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
August 4, 2010 at 5:39 am

#227492

Dear all,
We have a database with full recovery model, so the logs are growing very faster, so if i shrink the database base its not shrinking especially LDF
So, i have planned to change the Database Full recovery to Simple recovery
am i correct or not?
Note: We don't have any log shipping and all..
but only full text search using...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sudeepta SSCommitted Points: 1666 More actions · Answer 1

Shrinking of database is not recomended.

If you don't have log backup in place, it is a good idea to change the recovery model of the database to Simple. However, you should be aware that you will not be able to do a point-in-time restoration of your database, in case of a disaster.

If a database is in Simple recovery model, you will be able to recover the data till the last full backup in case of a disaster (consider the fact that your backup is valid and working properly).

Hope, I didn't confused you... 🙂

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Changing recovery model has little to do with what features you are using.

Do you need point-in-time recovery with this database? If the DB failed middle of the working day, would it be acceptable to the users of the system for you to restore to the last full backup and lose any data entered since then?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sivaprasad SSCertifiable Points: 6183 More actions · Answer 3

On the simple recovery model, the transaction log is automatically truncated. If you must remove the log backup chain from a database, switch to the simple recovery model.

Database Log truncation, which is automatic under the simple recovery model, whereas full and bul-log recovery model, log backup has to occure to clear the in-active entries from the database log file.

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

VastSQL SSC-Dedicated Points: 36173 More actions · Answer 4

Remember once to change back to full..dont forget to take a full backup otherwise database will be in simple recovery mode only

Steve T SSCommitted Points: 1994 More actions · Answer 5

Saravanan_tvr (8/4/2010)

Dear all,

We have a database with full recovery model, so the logs are growing very faster, so if i shrink the database base its not shrinking especially LDF

Like a lot of the other posters have mentioned, you shouldn't change it just because you can. You need to determine if you can get away with using simple recovery vs full recovery. Based on your first line, it sounds like you logs (ldf file) are growing really fast. It could be you just need to to backup your transaction logs more often so that the log space get reused instead of just growing and growing.

Either way, the best bet is to determine what you need first.