Sometimes one might like to identify a particular SQL Server resource using the Page ID this resource is under, you might notice a Page ID sometimes on the Activity Monitor page under the Wait Resource column, if a certain query is waiting on a particular resource, some DMVs will reference Page IDs as well as the SQL Server log files, I find it handy to be able to see exactly what SQL Server Object falls under this Page ID, in order to diagnose any issues with access to that particular resource.

A Page ID looks something like this:
Database-ID:File-ID:Page-ID
For example:
6:2:1358463

This already gives us some information about the physical and logical locations of the object within SQL Server, so in order to retrieve the database name the object falls under, all we have to do is run the following function:

Replacing the Database-ID with the first name in the Resource Page ID.

And in order to get the logical name of the file this Page ID falls under, we can call the following SQL Server function:

Replacing the File-ID with the second name in the Resource Page ID.

Now this is close enough, but we still need to retrieve Object ID, which will actually tell us something a bit more meaningful, and alot less broad than the database or the file this resource is sitting under, for that we can use the following SQL Server function:

The DBCC PAGE function will return a bunch of information about the page itself, its allocation status, and other interesting information that relates this page to SQL’s objects and system, what we are interested in is the “Metadata: ObjectId” entry, which can be seen below in the image output of the DBCC PAGE command.

After getting the ObjectID, all we need to do is plug it into the following SQL Server function and we have our object name:

And we are cooking!

It is a very handy way of exploring which objects are being troublesome, and digging deeper into SQL’s internals.

Identify SQL Server Object Using Resource Page ID rated 4 out of 5 by 1 readers