Thanks Paul, This turned out quite different, it was open transaction which happened during nightly backup which locked the database. So checkpoint was not happening and auto truncate of log not happening as well. Interesting thing that within 15 hours of locked/open transaction log grew to 27 GB.
Got very good advise from the SqlDownUnder mailing list ppl., I appreciate your active help Cheers A On Thu, Jul 21, 2011 at 10:54 PM, Paul Turner <ptur...@smsmt.com> wrote: > 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* <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 > >
_______________________________________________ ozmoss mailing list ozmoss@ozmoss.com http://prdlxvm0001.codify.net/mailman/listinfo/ozmoss
