Simple Database Recovery Model

Question

Simple Database Recovery Model

matt101

Default port

Points: 1456
More actions
June 4, 2003 at 9:16 am

#357793

I was reading through the docs because I was having trouble with a couple of my databases with huge transaction logs. I see most of my databases are set to simple and the ones that are set to full have problems with their transaction log. I read through the docs and don't quite understand what they mean by:
All data modifications made since last backup are expendable, or can be redone. Lowest logging overhead, but cannot recover past the end of the last backup.
So that means, I can only restore data from the tape from my last backup and not to worry about the transaction log?
Matt

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

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

Shas3 Hall of Fame Points: 3671 More actions · Answer 1

With simple recovery you can recover to the point of the last backup. You cannot restore the database to the point in time. Since most of your database set to Simple you don’t have to worry abt the Transaction log backups. But for the Full Recovery or Bulk-Logged Recovery model you must take frequent log backup to restore point in time and also to keep your Tlog file smaller

Shas3

adamcrv Hall of Fame Points: 3056 More actions · Answer 2

Hi,

Is there a specific way to set SQL server to use simple mode in SQL7 ?. I cant seem to find doco on this and have looked almost everywhere. Its easy to do on 2000 but i cant find it in SQL7. I would like to not have such a huge tlog for my non critical dbs that dont require it !

Thanks in advance

Adam

"If the war could be over tommorrow isnt that worth fighting for? isnt that worth dying for? " Morpheus - Matrix Reloaded

------------------------------
Life is far too important to be taken seriously

jaybmehta SSChasing Mays Points: 637 More actions · Answer 3

Check out the Truncatte the log on Chkpoint, havent worked then also give a try

RandyB SSCommitted Points: 1565 More actions · Answer 4

Yes, jaybmehta's post is correct. In SQL7 EM, click the database's properties and on the options tab, check the truncate log on checkpoint box.

I advise that you make sure you do full and possibly differential backups regularly though.

Shas3 Hall of Fame Points: 3671 More actions · Answer 5

If the log file still grows after the Truncate log on check point ON, you might want to schedule a hourly job with the following commands

use <Database_Name>

go

dbcc shrinkfile (Log_Filename, 300)

go

backup log Database_Name with no_log

go

As Randy suggested make sure you have full backup

Shas3

adamcrv Hall of Fame Points: 3056 More actions · Answer 6

Thanks for the info on this guys. 🙂

------------------------------
Life is far too important to be taken seriously