SQL Server Full to Simple to Full Recovery Model

Question

SQL Server Full to Simple to Full Recovery Model

mtz676

SSCrazy Eights

Points: 9176
More actions
September 3, 2013 at 12:26 pm

#277642

Whenever I have a huge log file for any database with Full as its recovery model
This is what I do to recover disk space...
I change the recovery to simple..then shrink the file and change it back to full.
What exactly is happening here ?
Is this a recommended process?

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

That is not a recommended process, you will have performance issues and you'll be losing all the advantages given by the full recovery model.

Once I found a company where they did that from time to time but never did any log backups (the recommended practice), so my suggestion was to change definitively to the simple recovery model.

Depending on your situation, you should be taking log backups and defining a correct log size or change the recovery model to simple.

Maybe someone will explain with more detail.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Bill Talada SSChampion Points: 11954 More actions · Answer 2

You're not gaining anything because you need to do a full backup immediately after doing your changes. There used to be a command to "dump tran with no_log" but I'm not sure that works in newer versions. You might be able to dump tran to a nul device but I haven't tried it in a decade.

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

Please take a read through this article on how to manage transaction logs - http://www.sqlservercentral.com/articles/Administration/64582/

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

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 4

Read Gail's article and then set up log backups to manage your recovery. you can shrink the log file if you need to recover the space, but if you haven't set up management of your files, it will just grow again.

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 5

Take a look at the Autogrowth settings. Default is 10% increase. I change this to a MB increase.