tempdb monitoring

[drStealth]

We have a recurring issue with the tempdb growing rapidly in a short time. We have set up alerts in the system to forewarn us of such an event, and we've successful in restoring the size of the tempdb to a manageable level before the tempdb fills up completely. Attemping to figure out the user(s) and process(es) is the real question. Does anyone have any tips that may be of help...can anyone recommend a monitoring tool that may be helpful. Thanks to all. drstealth

 

[Catadmin]

SQL Profiler. Using Information_SChema, you can find the process id of the tempdb, then filter your profiler trace based only on that DB. Or you can filter by name.

This should give you a good overview of what and who are accessing it. Look up Profiler in Books Online for more info. It is a fantastic tool.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[SQLBill]

There are some actions that really can fill up TEMPDB quickly. Are you doing any type of rebuilding? For instance, rebuilding your indexes or even defragging them will cause TEMPDB to grow very quickly.

-SQLBill

Posting advice: FAQ481-4875

 

[drStealth]

Catadmin and SQLBill, thanks for your responses. We will look into the SQL profiler, we are not using it at the moment. As for rebuuilding indexes the answer is yes we are doing that with our production databases and you are right SQLBill this does fill up the tempdb. However, the processes in question are not related to this type of activity, which is scheduled. We use a Sybase product (Infomaker) which may be the culprit...I believe certain Infomaker reports cause the tempdb to balloon. The problem is that it does not occur everytime the reports are run. It would be nice to pinpoint the process and user to figure this out.

Thanks again for your input, it is appreciated. drStealth