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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 4,046, Visits: 9,203
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.
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?

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: Thursday, December 18, 2014 1:23 PM
Points: 157, Visits: 981
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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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: Yesterday @ 3:11 PM
Points: 31,368, Visits: 15,837
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:08 AM
Points: 547, Visits: 261
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