Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Full to Simple to Full Recovery Model Expand / Collapse
Author
Message
Posted Tuesday, September 3, 2013 12:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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?
Post #1491061
Posted Tuesday, September 3, 2013 12:32 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:48 PM
Points: 3,252, Visits: 6,997
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1491062
Posted Tuesday, September 3, 2013 12:58 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:04 AM
Points: 128, Visits: 810
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.
Post #1491068
Posted Tuesday, September 3, 2013 1:11 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 42,308, Visits: 35,365
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 2008, MVP
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

Post #1491070
Posted Tuesday, September 3, 2013 1:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, June 11, 2014 5:42 PM
Points: 33,007, Visits: 15,132
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1491077
Posted Thursday, September 5, 2013 7:33 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, January 31, 2014 2:56 AM
Points: 483, Visits: 256
Take a look at the Autogrowth settings. Default is 10% increase. I change this to a MB increase.
Post #1491788
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse