Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcolli00
Starting Member
19 Posts |
 Posted - 2011-01-28 : 15:36:32
|
| Hi I would like to see if there is a DMV or systems table that has data that I can tie to a temporary table. My goal is to track an object_id back to a process in one of my databases on my server. There are many objects listed. For example, the tempdb tables has the name #3FD53C33 and object_id is 1013423434233. I have tried everything that I could think of so far including simply writing a query to select * from #3FD53C33. I have also run sql profiler all day and then filtered with the goal of finding objectid but it was not showing up in there. What would you do to trace a tempdb table to the actual process (stored procedure, view, whatever is causing the object) that created it? Thanks in advance!MC |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-28 : 16:01:16
|
| Take a look here: http://sqlblog.com/blogs/paul_white/archive/2010/08/14/viewing-another-session-s-temporary-table.aspx |
 |
|
|
mcolli00
Starting Member
19 Posts |
Posted - 2011-01-28 : 17:57:58
|
| The link didn't help me locate the process causing permanent temp tables in my tempdb. However, I played with the sql examples from the link provided and it will be very handy in the future! Thanks!MC |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-28 : 18:13:06
|
| Ugh, I misread the question, sorry about that. If you're running profiler be sure to include the HostName and ApplicationName columns, in addition to any others you're capturing, and also include the Object:Created event. You can set a filter on DatabaseID=2 or DatabaseName=tempdb to catch only tempdb activity. Don't try to filter on object_id as that is randomly generated for non-system objects. |
|
|
|
|
|
|
temporary table - how to trace back to process