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

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

Re: SIMPLE Recovery Model Log file growth

Show all headers | View raw

Cathy (Cathy@Nospam.com) writes:
> I have done DBCC OPENTRAN and there appears to be no open transactions
> yest the log file continues to grow. 

What does the column log_reuse_wait_desc in sys.databases say about this
database?

> That said, what I am looking for a a bulletproof way (preferably script) 
> that could be used to

I don't think you will ever find such a script, because the action 
that needs to be taken depends on why the log does not truncate. If
there is an open transaction - kill that process. If the database is
in full recovery contrary to your belief - change the recovery model
etc.

Nor do I see any points with stop all processing in the database as a
general action. It may be needed to fix what is preventing the log
from being truncated, but then again it may not. Backup the database
is something you should do regularly.
 
> 3. Truncate the Logfile (if neeeded)

That is what simple recovery is all about. There were commands for this 
in the past, but they have been dropped from the product. Instead
you need to set the database to simple recovery.

> 4. Shrink the database log file (down to a minimum)

Depends on what you define as "minimum". The log should still be big
enough to hold your transactions.

By the way, what size is the log today?



-- 
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