Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!newsfeed.datemas.de!feeder.erje.net!eu.feeder.erje.net!border3.nntp.ams.giganews.com!Xl.tags.giganews.com!border1.nntp.ams.giganews.com!nntp.giganews.com!local2.nntp.ams.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail NNTP-Posting-Date: Sun, 28 Oct 2012 02:59:58 -0500 From: "Cathy" Newsgroups: comp.databases.ms-sqlserver References: <0tudnXZbkNzRyxHNnZ2dnUVZ7qednZ2d@bt.com> In-Reply-To: Subject: Re: SIMPLE Recovery Model Log file growth Date: Sun, 28 Oct 2012 07:59:56 -0000 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Windows Mail 6.0.6002.18197 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463 Message-ID: Lines: 59 X-Usenet-Provider: http://www.giganews.com X-AuthenticatedUsername: NoAuthUser X-Trace: sv3-UsoVg/eANUpA9muwaXV7r+Yk2YODCZCdlx90IhQJqLJKdEOasP+JKNhTP7BUTQd48QJSz3bFVlJmP6f!p/QRs6qTj5/GwmHYX9XXbeKAXpMROPnkbCFkU9I/RCYbuFpjVAtjQoh8Ku5k46G+H+jVjMap1bg2!Lg== X-Complaints-To: abuse@btinternet.com X-DMCA-Complaints-To: abuse@btinternet.com X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.40 X-Original-Bytes: 3566 Xref: csiph.com comp.databases.ms-sqlserver:1338 Thanks for the response. I have done DBCC OPENTRAN and there appears to be no open transactions yest the log file continues to grow. That said, what I am looking for a a bulletproof way (preferably script) that could be used to 1. Issue a Checkpoint command 2. Stop all processing in the database (probably out of hours) 2. Ensure no transactions are waiting to be processed, (identify any that are and stop them) 4. Backup the database (if needed, as I actually backup the entire system through veeam by shutting down) 3. Truncate the Logfile (if neeeded) 4. Shrink the database log file (down to a minimum) I have done quite a bit of googling on this, find lots of sites saying what the problem is (which suggests to me that many others are having this problem), a few suggestions on a solution, but not found one so far that has a comprehensive solution that will resolve this no matter what is keeping the logfile so large. I thought therefore it might be usefull posting here so that a comprehensive solution could become available. C "Erland Sommarskog" wrote in message news:XnsA0FA14761DAYazorman@127.0.0.1... > Cathy (Cathy@Nospam.com) writes: >> The database is used by Sharepoint but as we have no requirement to >> restore to a point in time and will be happy to restore to the last >> backup, (normally run in the middle of the night) >> >> I have noted that the log files are getting bigger and bigger and causing >> disks to run out of space. >> >> My question is. what is the simplest way in which to close any open >> transactions in the log file and truncate and shrink the file. > > You can use DBCC OPENTRAN to see if there are any open transactions in the > database. > > Has the database ever been involved in replication? Replication also uses > the log, and non-replicated transactions will cause the transaction to > grow. > > And doublecheck that you really are in simple. > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >