how to stop log from growing?

Question

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

skrilla99 SSCertifiable Points: 5795 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

Simple answer, because a checkpoint has not occured yet.

-Isaiah

gts31 Ten Centuries Points: 1294 More actions · Answer 5

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