SQLServerCentral / SQL Server 7,2000 / Administration / Decyphering waitresource / Latest Posts

RE: Decyphering waitresource

Ion Freeman — Wed, 01 Nov 2006 10:00:00 GMT

-- 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

RE: Decyphering waitresource

larryd — Thu, 18 Sep 2003 14:07:00 GMT

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 ONdeclare @dbid int, @fileid int, @pageid int, @spid int, @sql varchar(128)--set your spid of interest here:set @spid = 74select @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..sysprocesseswhere 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 #pageinfocreate table #pageinfo (ParentObject varchar(128),Object varchar(128),Field varchar(128),Value varchar(128) )dbcc traceon (3604) with no_infomsgsinsert into #pageinfo (ParentObject, Object, Field, Value)exec (@sql)select object_name(Value) as 'waitresource object name'from #pageinfowhere Field = 'm_objId'dbcc traceoff (3604) with no_infomsgs

RE: Decyphering waitresource

sxg6023 — Thu, 18 Sep 2003 10:58:00 GMT

Yes, you are correct the third one is the page number.Say you have a wait on resource 5:1:9337310In my case DB 5 is Items and File 1 is Items_Data. Run the following to get the object info:dbcc traceon (3604)godbcc 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 = 0x9bspin = 0 bnext = 0x00000000 PAGE HEADER:------------Page @0x7B764000----------------m_pageId = (1:9337310) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000m_objId = 978102525 m_indexId = 0 m_prevPage = (1:9337311)m_nextPage = (1:9337309) pminlen = 68 m_slotCnt = 109m_freeCnt = 30 m_freeData = 7944 m_reservedCnt = 0m_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) = CHANGEDML (1:9202183) = NOT MIN_LOGGED

Decyphering waitresource

larryd — Thu, 18 Sep 2003 10:41:00 GMT

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!