Strange Values in SYS.DM_TRAN_LOCKS table RESOURCE_ASSOCIATED_ENTITY_ID column

Question

I’ve been trying to understand some strange values in the RESOURCE_ASSOCIATED_ENTITY_ID column of SYS.DM_TRAN_LOCKS when RESOURCE_TYPE is “OBJECT”. Although these should be object Ids, I cannot determine what object they actually refer to.

I’ve tried everything I can think of, including querying all system tables with columns of type INT and BIGINT to see if I can find the value. No luck. The funny values actually appear in SYS.DM_TRAN_LOCKS, SYS.SYSLOCKINFO and SP_LOCK.

(Microsoft SQL Server Standard Edition (64-bit), version 9.00.4053.00 (SP3 + a security patch))

Answer 1

It can return Object ID, Hobt ID, or Allocation Unit ID.

The Objects can be Data Table, View, Stored Proc, Extended Stored Proc, or any other Object that has an Object ID.

Might try OBJECTPROPERTYEX ( ID, PROPERTY ). See 2008 Books Online for examples.

Answer 2

AJM has stated that he is seeing these unknown object IDs when RESOURCE_TYPE is OBJECT. According to the Books Online the content of the RESOURCE_ASSOCIATED_ENTITY_ID should be an object id (not a Hobt ID or Allocation Unit ID) in this circumstance. The actual wording is "Represents a database object. This can be a data table, view, stored procedure, extended stored procedure, or any object with an object ID".

However, I am able to recreate the problem in my environment (SQL Server 2005 Developer Edition version 9.00.3282.00, SP1). I've tried the OBJECTPROPERTYEX suggestion, but it also returns NULL. Again, this suggests that the mystery objects are not schema-scoped, but unfortunately I also can't work out what they are.