|
|
|
Mr or Mrs. 500
 
Group: General Forum Members
Last Login: Friday, May 22, 2009 8:10 AM
Points: 536,
Visits: 27
|
|
I have a spid that shows a wait resource in the form n:n:nnnnnnn . The first number is dbid and the second (I believe) is fileid, and I think the third might be page number? This is different than the waitresource type TAB: n:nnnnnn which seems better documented. What would be great is a script that converts this waitresource 'address' to the object name.
Thanks!
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Friday, January 02, 2004 12:00 AM
Points: 216,
Visits: 1
|
|
Yes, you are correct the third one is the page number.
Say you have a wait on resource 5:1:9337310
In my case DB 5 is Items and File 1 is Items_Data.
Run the following to get the object info:
dbcc traceon (3604)
go
dbcc page (5, 1, 9337310)
The output shows the objectId : m_objId = 978102525
Then object_name (id) gives you the object.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:9337310)
-----------------
BUFFER:
-------
BUF @0x19A3E680
---------------
bpage = 0x7B764000 bhash = 0x00000000 bpageno = (1:9337310)
bdbid = 5 breferences = 1 bstat = 0x9
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x7B764000
----------------
m_pageId = (1:9337310) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId = 978102525 m_indexId = 0 m_prevPage = (1:9337311)
m_nextPage = (1:9337309) pminlen = 68 m_slotCnt = 109
m_freeCnt = 30 m_freeData = 7944 m_reservedCnt = 0
m_lsn = (9238:816300:27) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 37750929
Allocation Status
-----------------
GAM (1:9202176) = ALLOCATED
SGAM (1:9202177) = NOT ALLOCATED
PFS (1:9333552) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:9202182) = CHANGED
ML (1:9202183) = NOT MIN_LOGGED
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Friday, May 22, 2009 8:10 AM
Points: 536,
Visits: 27
|
|
Thanks much! I had to try automating this, and I think this is it. (Sorry, I don't know how to keep the code indented in this window to make it more readable).
/* The following query automates the process of translating the page in a waitresource in sysprocesses
(of the form dbid:fileid:page) into the object name containing that page.
*/
SET NOCOUNT ON
declare @dbid int,
@fileid int,
@pageid int,
@spid int,
@sql varchar(128)
--set your spid of interest here:
set @spid = 74
select
@dbid = substring(waitresource, 1, charindex (':', waitresource) - 1),
@fileid = substring(waitresource,
charindex( ':', waitresource) + 1,
charindex(':', waitresource, charindex(':', waitresource) + 1) - charindex(':',waitresource) - 1
),
@pageid = substring(waitresource,
charindex(':', waitresource, charindex(':', waitresource, charindex(':', waitresource) + 1)) + 1,
len(waitresource) - (charindex(':', waitresource, charindex(':', waitresource, charindex(':', waitresource) + 1)) + 1)
)
from master..sysprocesses
where spid = @spid
and waitresource like '%:%:%'
set @sql = 'dbcc page (' + convert(varchar,@dbid) + ',' + convert(varchar,@fileid) + ',' + convert(varchar,@pageid) + ') with no_infomsgs, tableresults'
if exists (select 1 from tempdb..sysobjects where xtype = 'U' and name like '#pageinfo%')
drop table #pageinfo
create table #pageinfo (
ParentObject varchar(128),
Object varchar(128),
Field varchar(128),
Value varchar(128) )
dbcc traceon (3604) with no_infomsgs
insert into #pageinfo (ParentObject, Object, Field, Value)
exec (@sql)
select object_name(Value) as 'waitresource object name'
from #pageinfo
where Field = 'm_objId'
dbcc traceoff (3604) with no_infomsgs
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, June 24, 2009 4:46 PM
Points: 26,
Visits: 36
|
|
-- the formatting seems to be working for me, and this is so useful! /* The following query automates the process of translating the page in a waitresource in sysprocesses (of the form dbid:fileid:page) into the object name containing that page. */
SET NOCOUNT ON declare @dbid int, @fileid int, @pageid int, @spid int, @sql varchar(128)
--set your spid of interest here:
set @spid = 75
select @dbid = substring(waitresource, 1, charindex (':', waitresource) - 1),
@fileid = substring(waitresource, charindex( ':', waitresource) + 1,
charindex(':', waitresource, charindex(':', waitresource) + 1) -
charindex(':',waitresource) - 1 ),
@pageid = substring(waitresource, charindex(':', waitresource,
charindex(':', waitresource, charindex(':', waitresource) + 1)) + 1,
len(waitresource) - (charindex(':', waitresource,
charindex(':', waitresource, charindex(':', waitresource) + 1)) + 1) )
from master..sysprocesses where spid = @spid and waitresource like '%:%:%'
set @sql = 'dbcc page (' + convert(varchar,@dbid) + ',' +
convert(varchar,@fileid) + ',' + convert(varchar,@pageid) + ') with
no_infomsgs, tableresults'
if exists (select 1 from tempdb..sysobjects where xtype = 'U' and name like
'#pageinfo%')
drop table #pageinfo
create table #pageinfo ( ParentObject varchar(128), Object varchar(128),
Field varchar(128), Value varchar(128) )
dbcc traceon (3604) with no_infomsgs
insert into #pageinfo (ParentObject, Object, Field, Value)
exec (@sql) select object_name(Value) as 'waitresource object name' from
#pageinfo where Field = 'm_objId' dbcc traceoff (3604) with no_infomsgs
|
|
|
|
