Tlog growth analysis

Question

Post reply

Tlog growth analysis

Oracle_91

SSChampion

Points: 14548
More actions
July 10, 2011 at 10:38 pm

#254388

Hi All,

I was in little dilama on this concept and how to exactly react to such situations.

Very often clients will be coming and asking us the txn log is growing very rapidly. Mostly when sharepoint content database are concerned.

Sometimes the tlogs will grow up to 33 Gigs and may also next day it is growing 67 gigs.

I came to know a crawling job is being run and it is taking 7 hours and sometimes it keeps on running.

normally full crawling should take 2-3 hours and incremental crawling would take 10-15 mins.

But we have observed that incremental itself it is taking 7 hours and never ended.

finally we had look into log_reuse_wait_desc column in sysdatabases and had to take a log backup

and managed to shrink tlog to 1 gigs.

But i wanted to know how to react in such situations and what are preemptive actions can be taken

go forward.

Basically i was not able to know why the tlog has grown such drastically.

In such situations how should we troubelshoot the issue and figure out the reason for the tlog growth.

How to monitor the activities on a particular database and casuing the growth of tlog and finally we should be able to figure out which one is the actual culprit.

I know this is something which is environment specific but am looking for some generosu answers who can help me out in handling such situations.

What are all the possible solutions we can look for and how to capture all information and put forward the management and answer bcz of this, the TLOg has been growing rapidly ??

Thanks in advance.

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

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

There is number of things a transaction log growth.As you told, is it a content-sharepoint database only growing daily? or anyother DB's like custom..

1.Have you running t-log backup? How often is it?

2.DO you have any rebuild job in place your own or default one?

3.what it shows 'log_reuse_wait_desc'

Do monitor your server if you want set up the baseline like the t-log grows 10 GB then you'll get an email.

For more you want run a server side trace and capture the data and find where the modification/transaction running huge.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/