table/index and page mapping

[New MSSQL DBA]

I've just found a blocking lock occuring in a SQL Server.

From the waitresource, I found that the blocker has the following

information:
wait_info: PAGEIOLATCH_EX
wait_resource: 14:1:564312

And the blockee has the following information:
wait_info: LCK_M_S
wait_resource: KEY: 14:405576483:2 (7501a5aa8355)

The problem is that I do not know which object is the blocker holding,
for I understand that for a PAGE lock, it is in the format of
database_id:file_id:page_id

I know the db_id, the file_id but don't know how to map the page_id
(564312) to a table/index in the database.

Can somebody shed some lights on this? thks a lot.

[Tzvika Barenholz]

have you tried using sp_lock ?

[New MSSQL DBA]

These information are gathered from scheduled jobs so when I discover
that, it's already gone.

But one thing I would like to know, is there a clear way on how to map
a page_id to a particular object, i.e. how can I know which object is
involved when given a particular page id?

[Erland Sommarskog]

New MSSQL DBA (bo*******@gmail.com) writes:

I've just found a blocking lock occuring in a SQL Server.

From the waitresource, I found that the blocker has the following

information:
wait_info: PAGEIOLATCH_EX
wait_resource: 14:1:564312

And the blockee has the following information:
wait_info: LCK_M_S
wait_resource: KEY: 14:405576483:2 (7501a5aa8355)

The problem is that I do not know which object is the blocker holding,
for I understand that for a PAGE lock, it is in the format of
database_id:file_id:page_id

I know the db_id, the file_id but don't know how to map the page_id
(564312) to a table/index in the database.

Can somebody shed some lights on this? thks a lot.

DBCC PAGE is good for this. This command is however not documented.
I did find a KB article about, but it's very ancient:
http://support.microsoft.com/default...kb;en-us;83065.
You may still be able to find the information, though. Somewhere in
the output, there is an object id.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Bell]

Hi

You may want to look at getting "Inside SQL Server 2000" by Kalen Delany
ISBN
0-7356-0998-5.

John

"New MSSQL DBA" <bo*******@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

These information are gathered from scheduled jobs so when I discover
that, it's already gone.

But one thing I would like to know, is there a clear way on how to map
a page_id to a particular object, i.e. how can I know which object is
involved when given a particular page id?