April 6, 2012 at 3:01 am
I have following content repeating in textData column of SQL trace captured for blocked process report event through SQL Profiler.
<blocked-process-report><blocked-process><process id="process7e4f288" taskpriority="0" logused="0" waitresource="KEY: 151:72057594154844160 (d3ad03dea51a)" waittime="1116" ownerId="2287127722" transactionname="user_transaction" lasttranstarted="2012-04-05T06:13:54.270" XDES="0x16cd3f950" lockMode="U" schedulerid="12" kpid="8468" status="suspended" spid="340" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-04-05T06:13:54.270" lastbatchcompleted="2012-04-05T06:13:54.270" clientapp=".Net SqlClient Data Provider" hostname="USNDC2628" hostpid="4568" loginname="US\MossAppSvc" isolationlevel="read committed (2)" xactid="2287127722" currentdb="151" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"><executionStack><frame line="41" stmtstart="2510" stmtend="2756" sqlhandle="0x03009700befd8d4ab3591400029e00000100000000000000" /></executionStack><inputbuf>
Proc [Database Id = 151 Object Id = 1250819518] </inputbuf></process></blocked-process><blocking-process><process status="runnable" spid="209" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-04-05T06:13:54.270" lastbatchcompleted="2012-04-05T06:13:54.270" clientapp=".Net SqlClient Data Provider" hostname="USNDC2628" hostpid="4568" loginname="US\MossAppSvc" isolationlevel="read committed (2)" xactid="2287127692" currentdb="151" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"><executionStack><frame line="32" stmtstart="1982" stmtend="2014" sqlhandle="0x03009700a28e5e4ec8591400029e00000100000000000000" /><frame line="47" stmtstart="2636" stmtend="2754" sqlhandle="0x0300970085d99949ad591400029e00000100000000000000" /><frame line="79" stmtstart="5326" stmtend="5586" sqlhandle="0x03009700befd8d4ab3591400029e00000100000000000000" /></executionStack><inputbuf>
Proc [Database Id = 151 Object Id = 1250819518] </inputbuf></process></blocking-process></blocked-process-report>
In this content, if you see, inputbuf element for blocked process is
Proc [Database Id = 151 Object Id = 1250819518]
and same for blocking process also.
Here Database Id is correct but when try to fetch object name of object with Id 1250819518. I find nothing. Could somebody please tell, what kind of object is it and how to translate it to actual object name? I have several other object Ids like this only that are not getting translated to object names.
April 6, 2012 at 3:10 am
No way to tell from the object ID what object it is. Query sys.objects in the database that has an ID of 151.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2012 at 3:45 am
That's what querying object Id on destined DB does not show any object name. I have tried both of these options
select [name] from sys.objects where [object_id] = 1250819518
select object_name(1250819518)
April 6, 2012 at 5:33 am
Then the object has been dropped since that blocking report was created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply