Simple recovery model will keep the Tx log small as it will essentially truncate when a checkpoint occurs, see http://msdn.microsoft.com/en-us/library/aa173531(SQL.80).aspx . But if it is already large i.e. your 27GB you need to also run DBCC ShrinkDatabase after you switch it. That will reduce the file size to only the size needed for the active part of the Tx log i.e. small. See http://msdn.microsoft.com/en-us/library/ms190488.aspx
The other alternative rather than changing the recovery model (depending on you backup process) is to implement a transaction log backup on a nightly/regular basis in addition to a full backup. This has the same effect of cleaning out the inactive part of the log, then you can run DBCC ShrinkDatabase and keep the file small. Just remember that keeping it too small might cause it to autogrow (depending on your settings) and consume the disk space again. See http://support.microsoft.com/kb/315512 The advantage of implementing a transaction log backup is you also get the ability to have point in time restore operations. I.e. if the server dies you can restore the transactions from the Full backup then apply the transaction logs and basically get 99.999% of the data back (assuming you back up the tail of the log before you start the recovery process). Regards Paul Turner Practice Lead - SharePoint SMS Management & Technology M 0412 748 168 paul.tur...@smsmt.com<mailto:paul.tur...@smsmt.com> www.smsmt.com<http://www.smsmt.com/> About SMS: Innovation House, Technology Park, Mawson Lakes Boulevard, Mawson Lakes SA 5095 SMS Management & Technology (SMS) [ASX:SMX] is Australia's largest publicly listed Management Services company. We solve complex problems and transform business through Consulting, Technology and Systems Integration P please consider the environment before printing this email From: ozmoss-boun...@ozmoss.com [mailto:ozmoss-boun...@ozmoss.com] On Behalf Of Ajay Sent: Thursday, 21 July 2011 12:01 PM To: ozMOSS Subject: Sharepoint - Sql Server recommendations Hi Guys, Is there any recommended settings for backup for the configuration database of the SP2007 shared services database. One of our customer sites, the config database as been set up in Simple recovery mode, and the transaction log has massively grown. What I am reading on google, in simple mode transaction log is minimal (gets truncated whenevr a checkpoint occurs) , but in this case it's grown to 27GB. So for Simple Mode, how do we control the transaction log not to grow this big? Also is it okay to keep the Shared Services Db in Simple mode or switch it to full... any recommendations, pros or cons Thanks Ajay ________________________________ NOTICE - This communication is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking any action in reliance on, this communication by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this communication please delete and destroy all copies and telephone SMS Management & Technology on 1300 842 767 immediately. Any views expressed in this Communication are those of the individual sender, except where the sender specifically states them to be the views of SMS Management & Technology. Except as required by law, SMS Management & Technology does not represent, warrant and/or guarantee that the integrity of this communication has been maintained nor that the communication is free from errors, virus, interception or interference.
_______________________________________________ ozmoss mailing list ozmoss@ozmoss.com http://prdlxvm0001.codify.net/mailman/listinfo/ozmoss
