While continuing to look at what is causing blocks, you come across another format of information in the waitresource column in sysprocesses. This time you see:
PAG: 7:1:11223344
Again - thanks for the help SQL - object_ID too much trouble for you I guess?? Anyway, gives a DBA something to do I suppose.
This time it's not a hobt_id, but a page id, and is read as "DB_id, file_id, page_id"
To get this one into text, we need to do a couple of things.
First Run:
DBCC TraceOn (3604)
- this just tells DBCC commands to output to the screen rather than errorlog, and just lasts for your current connection, otherwise the next command just tells you "DBCC execution completed" which is even less helpful than "PAG 7:1:11223344"!
Then run
DBCC PAGE (7, 1, 11223344)
(Note you could run this adding WITH TABLERESULTS for an even tidier output)
From the result set returned, you should be able to spot a few useful Id's, such as
Metadata: IndexId
Metadata: ObjectId
From here, with OBJECT_NAME() or just scanning sys.indexes, you should be able to ascertain what table and index you are being blocked by.
