Transaction log growing
-
Hi
what is the reason of transaction log(.ldf) growing unexpectedly ??How to handle such situations ??
And what to do when it is completely full as in that condition DBCC Shrinkfile doesn't work
Is there any relation betn Recovery model & transaction log ,Is changing to simple recovery model the only solution ??
-
A transactionlog records all insert/update/delete activity on a transaction basis to be able to keep the database consistent e.g. in case of servercrach,...
If the recovery model is set to "Simple", a logged transaction will be marked "overwritable" after every (implicit) commit or rollback. This way the log can be kept small, but you cannot perform a point in time recovery because it overwrites logged transactions.
With bulk-logged and full logged recovery model, you'll need to do a log-backup, to tell sqlserver it is alowed to overwrite the logged transactions.
You can perform point in time recovery. With bulk logged, you can do PIT recovery if there are no bulk-operations logged.
BOL has more info regarding recovery models and their recovery options.
So a growing log-file with a db in simple recovery points to :
- heavy I/U/D use
- long running transaction(s)
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
- Please review the following links for more info.Regarding recovery model BOL is the best.Minaz Amin
"We make a living by what we get, we make a life by what we give" - Sir Winston Churchill"More Green More Oxygen !! Plant a tree today"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply