Transaction Log

Question

Post reply

Transaction Log

control

SSCommitted

Points: 1630
More actions
April 22, 2009 at 1:02 pm

#208659

Guys,
Apart from setting the database to "simple recovery mode" what other options do I need to set to prevent the log getting too big? I have a database set to simple recovery mode and the log is still growing and getting bigger unless I perform a backup and shrink the log file.
can somebody tell me what I can do to prevent the log getting too big?

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

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

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 1

Gail has written a nice article : http://www.sqlservercentral.com/articles/64582/

Also keep in mind you transaction log will grow up to the size it needs to handle your largest transaction !

Also keep in mind any given sql statement runs at least in its own transaction scope.

Imagine update mytable set colx = 'abc' on a table that occupies 20GB (without a where clause) may need xGB to complete that statements transaction !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

control SSCommitted Points: 1630 More actions · Answer 2

Thank you for the info.

The database I am having issue with the recovry mode set to simple is been use by Sharepoint application. With the covery mode set to simple, the log continue to grow out of hands.

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 3

is dbcc opentran showing info (execute in the correct database !)