Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1338

Re: SIMPLE Recovery Model Log file growth

Show all headers | View raw

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" <esquel@sommarskog.se> 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
> 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-27 22:46 +0100
  Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-28 00:07 +0200
    Re: SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-28 07:59 +0000
      Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-28 10:58 +0100
        Re: SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-28 13:23 +0000
          Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-28 15:49 +0100
            Re: SIMPLE Recovery Model Log file growth "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-10-28 12:51 -0400
              Re: SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-28 21:38 +0000
                Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-28 23:21 +0100
                Re: SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-30 21:43 +0000
                Re: SIMPLE Recovery Model Log file growth "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-10-30 18:22 -0400
                Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-30 23:37 +0100

csiph-web