March 10, 2009 at 12:26 pm
In a highly transactional database, does that recovery model affect query running time...?
March 10, 2009 at 12:34 pm
No. Doesn't really have anything to do with that.
Simple recovery is basically for databases where you don't care if you lose some of the data, or where there just plain isn't anything to lose.
For example, if you have a database that gets loaded up once a day by importing some text files, and otherwise is just being selected from, with no other data changes, you could put it in simple recovery mode, run a backup right after the import process, and there'd be no reason to do log backups during the day, since they wouldn't have anything in them.
If it's a highly transactional database, and the data changes in it matter to you, then do full recovery and do periodic log backups, because then you can do a "point-in-time" recovery. That means, if the hard drive crashes at 3:17 PM, you should be able to recover up to that point. In simple, you'd be stuck with going back to your last full backup, whenever that was, and you'd lose any data since then. In full, you can usually get it all back, up the point of the crash.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2009 at 12:52 pm
Thank You..we have changed the model to SIMPLE to allow few hardware upgrades and we will be changing it soon.
I got this question because, Insert was taking long time and high CPU today. I never observed such behavior before..
Is check point concept the same in Simple and Full recovery model? Is there any chance of Buffer cache over load in Simple causing performance issues?
March 10, 2009 at 12:59 pm
I can't see any way that Simple could cause that.
Books Online has the full data on how it works internally. It's worth reading and understanding if you're going to be working with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2009 at 4:16 pm
GSquared (3/10/2009)
stuck with going back to your last full backup,
or differential if you have them.
Simple recovery logs just about the same as Full recovery although some transactions are minimally logged in both bulk logged and simple mode. Could it be another op that was running around the same time, what other processes run on the sql server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply