database log file keeps growing

2 Jul 2009 5:27 PM

Pingx

Hi sql server experts:
I thought if I set the recovery mode to SIMPLE, the log will not grow.
But it is not true.
How do I set up the database so that the log file does not grow?

Thanks,

Pingx

2 Jul 2009 6:28 PM

Jeffrey Williams

Every transaction needs to be recorded in the transaction log, regardless of
the recovery model. The transaction log in simple recovery model will grow
as large as is needed to handle the largest transaction you are processing.

Show quoteHide quote

"Pingx" <Pi***@discussions.microsoft.com> wrote in message
news:EBFDD290-2472-41E9-AC72-54FD447CBDD1@microsoft.com...
> Hi sql server experts:
> I thought if I set the recovery mode to SIMPLE, the log will not grow.
> But it is not true.
> How do I set up the database so that the log file does not grow?
>
> Thanks,
>
> Pingx

2 Jul 2009 6:34 PM

Pingx

Thanks Jeffery. When the transaction ends, will the log file shrink itself?

Pingx
Show quoteHide quote

"Jeffrey Williams" wrote:

> Every transaction needs to be recorded in the transaction log, regardless of
> the recovery model. The transaction log in simple recovery model will grow
> as large as is needed to handle the largest transaction you are processing.
>
> "Pingx" <Pi***@discussions.microsoft.com> wrote in message
> news:EBFDD290-2472-41E9-AC72-54FD447CBDD1@microsoft.com...
> > Hi sql server experts:
> > I thought if I set the recovery mode to SIMPLE, the log will not grow.
> > But it is not true.
> > How do I set up the database so that the log file does not grow?
> >
> > Thanks,
> >
> > Pingx
>
>

2 Jul 2009 7:15 PM

TheSQLGuru

the log file itself (i.e. on disk) will not shrink by itself, nor should you
do so unless it was an exceptional situation that caused it to grow in the
first place. In SIMPLE recovery mode the committed transactions WILL be
flushed out of the tlog, allowing the space to be reused over and over.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

Show quoteHide quote

"Pingx" <Pi***@discussions.microsoft.com> wrote in message
news:BE2241CB-9206-4183-BC44-FC346FD97508@microsoft.com...
>
> Thanks Jeffery. When the transaction ends, will the log file shrink
> itself?
>
> Pingx
> "Jeffrey Williams" wrote:
>
>> Every transaction needs to be recorded in the transaction log, regardless
>> of
>> the recovery model. The transaction log in simple recovery model will
>> grow
>> as large as is needed to handle the largest transaction you are
>> processing.
>>
>> "Pingx" <Pi***@discussions.microsoft.com> wrote in message
>> news:EBFDD290-2472-41E9-AC72-54FD447CBDD1@microsoft.com...
>> > Hi sql server experts:
>> > I thought if I set the recovery mode to SIMPLE, the log will not grow.
>> > But it is not true.
>> > How do I set up the database so that the log file does not grow?
>> >
>> > Thanks,
>> >
>> > Pingx
>>
>>

2 Jul 2009 8:56 PM

Aaron Bertrand [SQL Server MVP]

> Thanks Jeffery. When the transaction ends, will the log file shrink itself?

No. And unless you had some really bizarre event happen, why would you want
it to? If the log is going to grow again because of normal transactional
activity on your system, then what do you gain by making it smaller
temporarily? Please read Tibor's article:

http://tr.im/StopShrinking