How You Can Crack Another Session's Temp Table, And Why You Might Want To

One of the understood behaviors of a SQL Server temporary table is that (aside from global temp tables) you can't peek into them from another session. Your temp table is your own and nobody else can access it (although sysadmins can at least find row counts in tempdb, which can be handy). But every now and then you run into an odd situation where it would be incredibly handy to find out what's inside of a temporary table that you didn't create.

For instance, say you have a batch process that has been running for hours, and seems to be doing nothing of interest. You look into the code that is running, and it has a while loop based on a count of the rows in a temp table. The exact methods can and do vary considerably, this is a completely contrived example.

SET NOCOUNT ON;CREATE TABLE #TempTable ( Col1 INT, Col2 CHAR(2000));INSERT INTO #TempTable VALUES (-42691,'Odd Record'),(0,'A'),(1,'A'),(2,'A'),(3,'A'),(4,'A'),(5,'A'),(6,'A'),(7,'A'),(8,'A'),(9,'A'),(10,'A');
DECLARE @i INT = 0;WHILE EXISTS (SELECT * FROM #TempTable)BEGIN
   WAITFOR DELAY '00:00:01';

   --Do something with the record

   DELETE FROM #TempTable WHERE Col1 = @i;
   SET @i = @i+1;END

Now you may look at that and see the obvious logical flaw here, but if the table population wasn't visible to you, it might not be so clear. So you see that it is spinning forever in this while loop, and you can tell from various DMVs that the table exists with one row. But you can't see inside the table to find out why. OR CAN YOU? First let's grab the object ID...note the percent wildcard, important because SQL Server appends a bunch of silly stuff on the name:

SELECT name, [object_id], type_desc, create_date FROM tempdb.sys.tables WHERE name LIKE '#TempTable%'

name	object_id	type_desc	create_date
#TempTable____________________000000011F66	-1590458413	USER_TABLE	2016-08-16 11:55:02

Make sure the create_date matches up to what you would expect (in case you grab a wrong but similarly named table) and take the object_id value and plug it into either DBCC IND, or my current favorite, sys.dm_db_database_page_allocations:

SELECT index_id, allocation_unit_type_desc, allocated_page_page_id, page_type, page_type_desc, page_level, extent_file_id FROM sys.dm_db_database_page_allocations (DB_ID(), -1590458413, NULL, NULL, 'DETAILED') WHERE is_allocated = 1 ORDER BY index_id, page_type;

index_id	allocation_unit_type_desc	allocated_page_page_id	page_type	page_type_desc	page_level	extent_file_id
0	IN_ROW_DATA	681	1	DATA_PAGE	0	1
0	IN_ROW_DATA	939	10	IAM_PAGE	0	1

In this example, we can ignore the IAM page and go straight to the one (currently) allocated data page. Note both the file ID and page ID...often very important for tempdb where multiple files is more the norm.

DBCC TRACEON (3604);DBCC PAGE (tempdb, 1, 681, 3) WITH TABLERESULTS;

If you're new to DBCC PAGE, just Google Paul Randal and DBCC PAGE and you'll be off in no time. Just don't confuse it with DBCC WRITEPAGE or it will be a bad, bad day for you. But once you scan on down through, you can see your record. Col1 with a negative value, and our oddly written increment logic (starting from zero) will never hit that. So that's where our endless loop came from!

ParentObject	Object	Field	VALUE
PAGE HEADER:	Slot 0 Offset 0x60 Length 2011	Record Type	PRIMARY_RECORD
PAGE HEADER:	Slot 0 Offset 0x60 Length 2011	Record Attributes	NULL_BITMAP
PAGE HEADER:	Slot 0 Offset 0x60 Length 2011	Record Size	2011
Slot 0 Offset 0x60 Length 2011	Memory Dump @0x000000003078A060		0000000000000000:   1000d807 3d59ffff 4f646420 5265636f 72642020  ..Ø.=YÿÿOdd Record
Slot 0 Offset 0x60 Length 2011	Memory Dump @0x000000003078A060		0000000000000014:   20202020 20202020 20202020 20202020 20202020
extra rows…
Slot 0 Offset 0x60 Length 2011	Memory Dump @0x000000003078A060		00000000000007D0:   20202020 20202020 020000                              …
Slot 0 Offset 0x60 Length 2011	Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4	Col1	-42691
Slot 0 Offset 0x60 Length 2011	Slot 0 Column 2 Offset 0x8 Length 2000 Length (physical) 2000	Col2	Odd Record

As seemingly obscure as this may appear, I have had to use it to find root cause of a production issue. Kudos to the Pauls (both White and Randal)...the former's article helped me last year when I needed to try this out the first time, and the latter's various blog posts and SQLskills training has helped me a lot in sorting out how and when to use these useful undocumented commands like DBCC PAGE.

Happy data hacking!