September 27, 2011 at 8:26 pm
I have a database of <9G. For he longest time my hourly transaction backups were <100M. Now they are consistently around 2G. This just seems freakish to me.
I'm not asking how to maintain the logs. I'm trying to figure out what could have changed in my application that so many transactions are happening.
I am a novice with SQL Profiler. What should I be looking for?
September 27, 2011 at 8:45 pm
With Profiler/trace you'll see all the activity, but that doesn't show you what changed. Without a trace from before, it's hard to know.
What I'd do is grab the statement completed, and scan through it. Look for any SQL that looks odd, or might be causing lots of data changes. Maybe reindexing? Maybe some job that's just adding transactions? New imports? Something is adding/changing/deleting data or moving things around.
September 28, 2011 at 6:49 am
Profiler might actually help.
You could search for long running transaction.
You can also filter based on the rowcounts column. Look for something freakishly out of the normal range. Say 10 000 VS 2-3.
September 28, 2011 at 5:45 pm
Thanks for the tip!
I started with the 'Duration' template, filtered for just my company database, and included text, rowcounts and applicationname.
I was getting massive rowcounts for a log table, PRX_Transaction_Queue, that is supposed to be cleaned out periodically but that had fallen by the wayside. The text included update queries to this table. The table had no keys or indexes.
We cleaned up the table and the next transaction log backup size dropped by 99%. Literally. And yes, I know what literally means. The change was that drastic.
September 28, 2011 at 6:42 pm
Thanks for the feedback.
As always SOMETHING changed :-D.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply