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