40gig templog - What to do

Question

Post reply

40gig templog - What to do

Viewing 6 posts - 16 through 20 (of 20 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

ouch...so you either have:

something that creates an outlandishly large transaction and doesn't commit it
something smaller that failed while in a transaction, and didn't roll it back (so the transaction stays open)
millions of small uncommitted transaction (usually caused by a SQL developer/DBA forgetting to call the COMMIT transaction)

What do you get with either DBCC OPENTRAN(tempdb) or select @@trancount?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jon-343748 SSChasing Mays Points: 626 More actions · Answer 2

0 for both.

The coworker contacted me, and has shut down his app. Hopefully that's the problem.

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

- keep your profiler running ! so you can see growth is stopped.

- contact your coworker again to monitor his application in detail (profiler) and ask her/him if something has changed lately (new settings getting active, ...)

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

Jon-343748 SSChasing Mays Points: 626 More actions · Answer 4

So is it common practice to have a handful of traces running all the time to track performance/maint issues? It seems like a growth trace would be something you would want to run all the time. Or I guess there's a way I can run a report once a month?

Antares686 SSC Guru Points: 125444 More actions · Answer 5

No because profiler adds a bit of overhead to you system and bandwidth utilization. You ca get software such as MS MOM which can monitor and notify you of some changes but profiler is more for one the spot troubleshooting and to use for performance considerations.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

Like Antares686 said, profiler is mostly used for troubleshooting, so you only add the overhead for a controled period of time. In this case, actualy finding the cause of the exceptional growth.

Profiler should always be used with a profound reason.

e.g. audit trace, baseline trace, sox-trace, ...

Depending on the kind of trace you run and the level of detail you select, your trace will be stressing your server.