Changing Recovery Model from FULL to SIMPLE

Question

Changing Recovery Model from FULL to SIMPLE

Denesh Naidoo

SSCrazy

Points: 2348
More actions
June 10, 2010 at 4:59 am

#387122

Hi All,
I have a database that currently is in FULL recovery model. There are no transaction log backups being done. This database does not need to be recovered (if need be) to a point in time. I therefore want to change the recovery model to SIMPLE. I've tested the following 2 scenarios in my dev enviroment which both work:
1. Truncate log file using DBCC BACKUP LOG [DBNAME] WITH NO_LOG
2. Shrink the log file using DBCC SHRINKFILE('DBNAME_Log', 5000,truncateonly). I want to set the log to 5GB
3. Change the recovery model to SIMPLE
1. Change the recovery model to SIMPLE
2. Truncate log file using DBCC BACKUP LOG [DBNAME] WITH NO_LOG
3. Shrink the log file using DBCC SHRINKFILE('DBNAME_Log', 5000,truncateonly). I want to set the log to 5GB
My question is...do I need to change the recovery model first before truncating then shrinking or should I truncate, shrink and then change the recovery model
Like I mentioned they both work, but I would just like to confirm which is the better/correct way of doing it
Thanks
Denesh Naidoo

Viewing 4 posts - 1 through 3 (of 3 total)

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

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 1

Change it to the SIMPLE recovery Model.

Also, you might want to check for the active trancation before you do anything. run this to check the active transaction:

DBCC OPENTRAN

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

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

Denesh Naidoo (6/10/2010)
My question is...do I need to change the recovery model first before truncating then shrinking or should I truncate, shrink and then change the recovery model

Neither. Change the recovery model and shrink. There's no need to truncate, once the DB is in simple, a checkpoint will automatically truncate the log and checkpoints run quite often.

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

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 3

Thank you Vishal and Gail. Your replies are appreciated.