Transaction logs larger than actual data imported

Question

Post reply

Transaction logs larger than actual data imported

Viewing 9 posts - 16 through 23 (of 23 total)

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

eng_mgomaa Right there with Babe Points: 796 More actions · Answer 1

eng_mgomaa

Right there with Babe

Points: 796

August 5, 2010 at 11:40 pm

#1203259

Did you try to make Analysis SQL-traces?

montril2121 SSC Enthusiast Points: 186 More actions · Answer 2

Could you please explain me how to do this (Analysis SQl traces)?

eng_mgomaa Right there with Babe Points: 796 More actions · Answer 3

By using SQL Profiler. Try to recorde traces and then make anaylsis to them. The most important is to choose needed Events, for example this events which work transactions.

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 4

run select * from sysprocesses and check for the queries..

Dave Coats SSC Eights! Points: 885 More actions · Answer 5

Paul - thanks for clearing that up...I guess I need to do more research before posting 😀

I see now that the transaction log backup performs a truncation of any inactive portions of the log not being used anymore. Its only until you perform a shrink on the log that the physical size is decreased.

Sorry for any confusion.

Dave Coats

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 6

Are you sure that the activity is consistent between the two servers? Don't assume that only the app hits the DB. If someone is executing transactions, say from SSMS, that could impact the system. And there are certainly transactions that can be done that cause log growth larger than the data. At my last job, one poorly formed app did transactions in such a way that an 8 GB DB generated about 40 GB of log activity a day with a net change of maybe 50 MB growth a day tops. Just lots of temp activity not in tempdb.

montril2121 SSC Enthusiast Points: 186 More actions · Answer 7

Thanks.

Can you let me know what was done to troubleshoot the issue you faced in your last company?

We have now decided to find SQL traces thro profiler by knowing the fact it will eat up hard disk.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 8

Well, that was just an example. In that case the app design was screwed up from the start (i.e. creating app tables as temp tables, not using tempdb instead). But the principle is still the same: Profiler, OPENTRAN, the usual DBA stuff. You just have to dig hard but the info is there to be found.

eng_mgomaa Right there with Babe Points: 796 More actions · Answer 9

Yes i am agree with jeff.mason. Youn should begin to analyse Traces, this is will be very good start.