how to stop log from growing?

• boris_shvartsman

  Mr or Mrs. 500

  Points: 582

  More actions

  November 28, 2003 at 3:30 pm

  #63366

  heard a lot and read a lot, but still the question is: if transaction is completed, WHY SQL SERVER doesn't shrink the empty inactive protion. (all on SP3).

  lalafafa

  if one wants it.. one will justify it.

• skrilla99

  SSCertifiable

  Points: 5795

  More actions

  November 28, 2003 at 4:38 pm

  #484038

  Are you using the simple recovery model? If so, then sql server will automatically remove the inactive log entries.

  Perhaps you are referring to the physical file size? You could set use AutoShrink to keep the physical file as small as possible, but as soon as sql server needed to write anything there would be resources wasted while file space was allocated.

  -Dan B

• ray_higdon

  SSCarpal Tunnel

  Points: 4589

  More actions

  November 30, 2003 at 10:23 am

  #484052

  Also, keeping the commited transaction in the log allows you the chance to backup the log and have transactional recovery. Once you backup the log or truncate it, those commited transactions are no longer in the tran log.

  HTH

  Ray Higdon MCSE, MCDBA, CCNA

  ------------
  
  Ray Higdon MCSE, MCDBA, CCNA

• philcart

  SSC-Forever

  Points: 47794

  More actions

  November 30, 2003 at 4:08 pm

  #484064

  Have a read through "Transaction Log Architecture" in Books Online. You should find the answer to your question there.

  Hope this helps

  Phill Carter

  --------------------

  Colt 45 - the original point and click interface

  --------------------
  Colt 45 - the original point and click interface

• iadams

  SSCommitted

  Points: 1970

  More actions

  December 3, 2003 at 10:13 am

  #484478

  Simple answer, because a checkpoint has not occured yet.

  
  -Isaiah

• gts31

  Ten Centuries

  Points: 1294

  More actions

  December 3, 2003 at 4:49 pm

  #484550

  I am running into a similar problem and have been wrangling with it all day. I have inherited numerous databases that look like they were created to have a 1GB data file and a 500MB log file. Both are way too large for current usage. I have been able to get the datafile down to proper size (about 600MB) using DBCC SHRINKFILE. And I have whittle down the log files using the SQL Server 7 script found here. BUT...the smallest I seem to be able to get them is about 187MB with 124MB free (as reported by the sp_force_shrink_log script). And this seems to be pretty typical of my results on all of the databases. I have tried backing up the entire DB, backing up the transaction log, backup up the transaction log with truncate_only, issued a manual CHECKPOINT. Anybody have any idea why I am getting these results??? Oh yes, btw, it looks like the open transactions are at the beginning of the virtual log based on DBCC LOGINFO:

  FileId FileSize StartOffset FSeqNo Status Parity CreateTime

  2 65470464 8192 1016 2 64 2003-12-03 17:14:18.437

  2 65470464 65478656 0 0 0 2001-02-04 02:06:55.670

  2 65470464 130949120 0 0 0 2001-02-04 02:01:34.360

  I know this has been covered ad-nauseum but I am missing something here 🙁

  Thanks for any help you may be able to provide.

  George