sql server - Is it possible to read data in another session's temporary table?

Question

We're maintaining (and occasionally debugging) a large in-house system. The system has 20+ databases, and a number of servers interfacing to other systems, processing data, etc. Not all is in-house developed - i.e. we don't always have access to source code.

At one place, we can see the system creating a #temp table - and then, in the next step, failing due to a data-error. We can see the existence of the #temp table in Management Studio - it exists in tempdb --> Temporary Tables as something like

#MyStuff________________________________________________________________________________________________________000000A65029

Obviously, the context menu here doesn't offer the full functionality (with Create table, select top 1000, etc.) - but only Reportsand Refresh.

I can find the table in sys.objects, sys.tables and even its column definition in sys.columns.

The question is: Is it in any way possible to access the data in the table? We can break execution so as to ensure that the table stays in scope, so the table vanishing shouldn't be an issue. This is not something that is to be done regularly or in code - it's more or less a one-shot deal. (I hope).

Answer 1

Unwieldy but you can examine the tables pages from an admin logon.

Get object id;

select object_id from tempdb.sys.tables where name like '#mystuff%'

Get a list of allocated pages;

dbcc ind('tempdb', <object id>, -1)

for each of the PageFID / PagePID (file/page IDs)

dbcc traceon(3604);
dbcc page(tempdb, <PageFID>, <PagePID>, 3) with tableresults

If I create #mystuff from another session I can see in a dbcc page view from my own session:

Slot 0 Offset 0x60 Length 18    Slot 0 Column 1 Offset 0xb Length 7 Length (physical) 7 myFieldName MyValue