Changing The Recovery Model

Question

Changing The Recovery Model

Chris Kitchen

Hall of Fame

Points: 3090
More actions
August 7, 2007 at 7:51 am

#125263

Hi,
My company is running Win2K3 with SQL Server 2005 Enterprise Edition and the databases are currently set to have a recovery model of 'Simple'. I am wanting to change this to a 'Full' recovery model, however, before I can do this, I need to assess the impact this will have on the performance and disk capacity of the server. Whats the best way to find out the impact this will have on the server?

Thanks in advance.
www.sqlAssociates.co.uk

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

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

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 1

In simple recovery model you can take only full backups and log backups are not possible as it will truncate the log and issues checkpoint, if you use simple recovery model the database consistency will not be there in case of any catastrophe.....so you have only the option of restoring the dsata from your last previous backup and there will be data loss.............inorder to prevent it you can go for full recovery model as you said, where all those mentioned are possible.......one of the advantage of having simple recovery model is that the log file will not grow as it will be checkpointed........but in full recovery model the log file will grow enormously, so you need to take T-log backups frequently to minimize the same.......so depending on your needs you can choose it.

[font="Verdana"]- Deepak[/font]

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 2

Hi,

Thanks for that, I do understand the different recovery model options and what each option entails, however, I am unsure as to how I can forecast what the level of impact might be if I do change the recovery model from 'Simple' to 'Full'

Thanks.

www.sqlAssociates.co.uk

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 3

do u have a test environment???? sample some large imports using that to see how much TLog space u need.....

may be that bulk-logged may b a better option......

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 4

Hi Chris,

Depending on how your hardware if configured I don't think changing the recovery model will have too much impact on the performacne of your DB. You will need to monitor the growth of the log file and take regular backups of the log to ensure it doesn't grow out of control.

Do you split your log and data file onto different disks?

Gethyn Elliswww.gethynellis.com

steve smith-401573 SSChampion Points: 10889 More actions · Answer 5

You've said NOTHING about transaction volume. That seems to be the key to making this change and measuring the results. Nothing is as good as testing the changes you want to make, but even before testing the changes, there are some basic questions you want to answer:

1. What volume of inserts? Updates 'in place'? (note that a time-stamped update, which creates a new version of the data record is NOT an 'update in place', but an insert of a new

record!)

2. How critical is the database? Must it be available for insert/update/delete 24/7 or is the availability window narrower than that?

3. What's the current schedule for maintenance on the database? how frequently are you having the statistics updated? reindexing? etc etc etc

4. How long can the database be tolerated as 'down' when normal processing times aren't met?

Enjoy!