a big transaction log

Question

a big transaction log

oren koler

Right there with Babe

Points: 776
More actions
January 4, 2006 at 3:42 am

#188828

Hi All
ive got a problem.....
i have a small DB 200 MB on the sql2000.
the thing is - only 20 MB are the Datafiles
and 180 MB is the transaction log
now i know that the DB should be no more than 30 MB.
does someone know y my transaction log isnt shrinking ???

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

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Are you backing up the transaction log? If you're not then you either need to back it up regularly or set the recovery mode to simple.

The transaction log does not get truncated until you back it up (unless you're in simple recovery mode) or you manually truncate it. Just be aware that if you put the database into simple recovery mode that you will not be able to recover to a point in time if you have a failure.

Hope that helps,

Karl
source control for SQL Server

oren koler Right there with Babe Points: 776 More actions · Answer 2

yes ure write - there was no backup

but the thing is - that even after backup - the log was still big ( no truncate )

only after i used the shrinkfile command - did the physical log get smaller.

but y does the log not clean itself - every checkpoint ( if im not mistaking )???

SQLZ SSChampion Points: 12872 More actions · Answer 3

The log will only get truncated on every checkpoint if the recovery mode is set to simple. Otherwise, you need to manage the size of the log by taking regular log backups.

Also, as you have noticed, the truncate operation does not affect the physical size of the log, it only frees up space within the log.

By keeping regular log backups you should find that the size is better managed and you will be less likely to need to use shrinkfile in the future.

Karl

Karl
source control for SQL Server