none
ObjectID from PageID

    Question

  • Is there any way to obtain the objectID from a DatabaseID & PageID WITHOUT using DBCC Page.

    I'm returning Data from the Blocked Process Report and want to show which resource is being blocked.

    For certain locks means i need to find the ObjectID from the PageID.

    Ideally I would like to do this using a function or view in order to access it within the query.

    And of course DBCC Page cannot be used in a view or Function.

    edited to add: SQL Server 2008 R2 :-)

    Thanks in advance

    Paul


    • Edited by SignOut Thursday, August 07, 2014 5:32 PM
    Thursday, August 07, 2014 5:32 PM

Answers

  • Hi you can catch the output of a DBCC-Statement in a temp table. why not build an SP that do this? Then you can use SP in a view using openquery like this

    create view ViewTest
    as
     select * from openquery([Server_name\Instance_name], 'sp_who')
    go
    * This is just a workaround idea which I did not try it using your case :-)


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachModerator Sunday, August 10, 2014 11:31 AM
    • Marked as answer by SignOut Monday, August 11, 2014 8:40 PM
    Sunday, August 10, 2014 11:30 AM
    Moderator

All replies

  • Can you refer the below link

    http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/172/storage-question.aspx

    With CTE_AllocationTable(TableName,FileId,PageId,SlotId,FirstPage) AS ( SELECT t.name, cast(convert (binary (2), reverse (substring (au.first_page, 5, 2))) as int) as FileId, cast(convert(binary (4), reverse (substring (au.first_page, 1, 4))) as int) as PageId, cast(convert (binary (2), reverse (substring (au.first_page, 7, 2))) as int) as SlotId, sys.fn_PhysLocFormatter(au.first_page) AS FirstPage FROM sys.system_internals_allocation_units au INNER JOIN sys.partitions p ON au.container_id = p.partition_id INNER JOIN sys.tables t ON p.object_id = t.object_id ) SELECT TableName,ct.FileId,PageId,SlotId,FirstPage,Name,[FileName] FROM CTE_AllocationTable ct INNER JOIN sys.sysfiles sf on ct.FileId=sf.FileId

    Where pageID=55778

    ORDER BY TableName

    --Prashanth


    Thursday, August 07, 2014 6:52 PM
  • Unfortunately (unless i am missing something) this will only match the first PageID of a table.

    Paul.

    Saturday, August 09, 2014 12:14 PM
  • Is there any way to obtain the objectID from a DatabaseID & PageID WITHOUT using DBCC Page.

    You'll also need the file id if there is more than one data file.  Below is an example using the undocumented sys.dm_db_database_page_allocations TVF to determine the object_id of a table, given the file and page.  However, I believe this TVP may have been introduced in SQL Server 2012 so it might not be an option for you, and the query will be expensive against a large database. 

    SELECT t.object_id
    FROM sys.tables AS t
    CROSS APPLY sys.dm_db_database_page_allocations (db_id(), object_id, NULL, NULL, 'LIMITED') AS pa
    WHERE
    	pa.allocated_page_file_id = @FileID
    	AND pa.allocated_page_page_id = @PageID;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, August 09, 2014 2:12 PM
  • As you say it is new to 2012, otherwise it would have been ideal.

    Paul

    Saturday, August 09, 2014 3:03 PM
  • Hi you can catch the output of a DBCC-Statement in a temp table. why not build an SP that do this? Then you can use SP in a view using openquery like this

    create view ViewTest
    as
     select * from openquery([Server_name\Instance_name], 'sp_who')
    go
    * This is just a workaround idea which I did not try it using your case :-)


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachModerator Sunday, August 10, 2014 11:31 AM
    • Marked as answer by SignOut Monday, August 11, 2014 8:40 PM
    Sunday, August 10, 2014 11:30 AM
    Moderator
  • Many thanks, that seems to do it.

    It's a trick i wasn't aware of (using OpenQuery to use sproc in a query), something to keep in mind.

    Paul

    Monday, August 11, 2014 8:40 PM
  • you are most welcome,
    I am glad to hear that I could help :-)

    [Personal Site] [Blog] [Facebook]signature

    Monday, August 11, 2014 9:18 PM
    Moderator