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

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

Re: SIMPLE Recovery Model Log file growth

Show all headers | View raw

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:XnsA0FA6F96DC629Yazorman@127.0.0.1...
> 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?

NOTHING for this database. There are three other in LOG_BACKUP

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

The database is in SIMPLE recovery mode

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

Well something seems to be wrong

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

I have several Log files, The largest appears to be 5GB and is the one I am 
looking at

I seem to have now discovered another database (a Search crawl Database) 
that is at a checkpoint, and at this point in time that DATABASE seems to 
take up most of the space on the disk with the database file sitting at 
around 7GB. Disk drive is 14Gb (After we recently, just last week I think 
added an extra 5GB to the drive)

regards

C 

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