Viewing Temp Tables

I am using SQL Server 2008 and my stored proceduce does inserts into #temptable. My procedure has been running for 32 hours (which isn't unheard of considering the amount of data that it has to aggregate) and I was wondering if outside of the stored procedure itself, I can query the temp table and view what is in the table?

The only solutions I can come up with at this time are stopping the procedure, which I do not want to do :(

1) Stop the procedure and use global temp tables ##temptableglobal

2) Stop the procedure and use actual tables temptable

EDIT

Following the tutorial linked in below, My first page returns the below


0XE601000000100

but if I try to run

DBCC TRACEON (3404)
DBCC PAGE (tempdb, 1, 173, 3) With table results

the data doesnt appear to be in plain text, for example the data in the column VALUE is showing 0 and nothing with a 0 value should be in the #temptable at all.

Let's start with the query in Paul White's blog post, Viewing Another Session’s Temporary Table (linked provided by @PJMahoney in a comment on the Question), as that gives us a few key pieces of information. Please note that I have added two fields to Paul's original query to handle the translation of the hex/binary value in the first_page field.

SELECT T.name,
T.[object_id],
AU.type_desc,
AU.first_page,
AU.data_pages,
P.[rows],
CONVERT(INT,
CONVERT(BINARY(2), REVERSE(SUBSTRING(AU.first_page, 5, 2)))) AS [FileIdINT],
CONVERT(INT,
CONVERT(BINARY(4), REVERSE(SUBSTRING(AU.first_page, 1, 4)))) AS [FirstPageINT]
FROM tempdb.sys.tables T
JOIN tempdb.sys.partitions P
ON P.[object_id] = T.[object_id]
JOIN tempdb.sys.system_internals_allocation_units AU
ON (AU.type_desc = N'IN_ROW_DATA' AND AU.container_id = P.partition_id)
OR (AU.type_desc = N'ROW_OVERFLOW_DATA' AND AU.container_id = P.partition_id)
OR (AU.type_desc = N'LOB_DATA' AND AU.container_id = P.hobt_id)
WHERE T.name LIKE N'#temptable%'; -- replace with your temp table name!!


You should get something similar to the following:

name object_id type_desc first_page data rows File 1st
pages Id Page
----------------------- ----------- ----------- -------------- ----- ---- ---- ---
#bob__...__000000000054 -1221199275 IN_ROW_DATA 0xF00100000100 1 4 1 496
#bob__...__000000000054 -1221199275 LOB_DATA 0xF20100000100 0 4 1 498


Before even bothering with looking at the datapages directly using DBCC PAGE, take a look at the "rows" field. Is it 0? Is it much lower than expected? Does your query, if not inserting the results into a temporary table, start returning rows well before it is finished? Or does it wait until somewhere towards the end of the query processing? Depending on what the query is doing, and the execution plan, it could be that sorting and/or parallelism is preventing any results from being released until later in the processing. If this is the case (and for a query that normally takes more than a day to run, it quite likely is the case), then there might not be anything to see here, in which case the "rows" will be 0 but that won't imply that anything is being blocked, etc.

But, if there are rows there, then you can view them by using:

DBCC PAGE(tempdb, {file_id}, {page_id}, 3) WITH TABLERESULTS;


Just replace {file_id} and {page_id} with the values returned in the FileIdINT and FirstPageINT columns respectively.

The rows in a data page are in "slots". So scroll down until you see something along the lines of Slot 0 Offset 0x60 Length 37 in the ParentObject field. There should be several rows for each "slot", depending on how many columns are in the table and if any are LOB types with enough data that won't fit in the row, etc. But in the Field field you should see the column names of the table, though the LOB types sometimes don't display the field name here and instead only show it on the far left of the Object field as "field name = [BLOB ...". In the VALUE field for each "field" row, you should see the value. Just keep in mind that:

-
The first several rows for each "slot" (i.e. the actual row of data), are the actual bytes of the entire row. The Object field is set to "Memory Dump @0x00..." and the Field field is empty. The VALUE field for each of these rows is displayed as "first_byte_offset: hex_values ASCII_characters". Each "Memory Dump" row shows 20 bytes, with the hex values arranged in 5 sets of 4 bytes each (1 byte = 00 - FF), and the 20 ASCII characters corresponding to each of those 20 bytes.

This representation is not translated so don't expect to see any actual data here outside of in-row string data. CHAR / VARCHAR data shows up as it would anywhere else: "test" shows up as "test". NCHAR / NVARCHAR is not displayed as Unicode. The bytes are parsed (not translated!) as if they are VARCHAR (i.e. 8=bit ASCII Extended) characters. So characters that fit in the ASCII Extended range (Code Points 0 - 255) display "normally", just with a trailing "." (the "00" in the hex). Any Code Points above 255 will be incorrectly represented since a Code Point of 0x03F4 will show up as two characters -- 0x03 and 0xF4 -- instead of ϴ.

• The next set of rows are the individual columns:

• NULLs show up as: [NULL]

• String fields display differently depending on whether they are a LOB type (VARCHAR(MAX), NVARCHAR(MAX), XML, etc) or not. For example, the string "BoB" shows up as:

• non-MAX VARCHAR or NVARCHAR: BoB

• VARCHAR(MAX): 0x426f42

• NVARCHAR(MAX): 0x42006f004200

• Unicode characters, including Supplementary Characters, do display correctly for non-LOB string types.

• LOB data that

StackExchange Database Administrators Q#121063, answer score: 4