Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Decyphering waitresource Expand / Collapse
Author
Message
Posted Thursday, September 18, 2003 10:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:43 PM
Points: 537, Visits: 28
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!




Post #16430
Posted Thursday, September 18, 2003 10:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, January 2, 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






Post #80075
Posted Thursday, September 18, 2003 2:07 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:43 PM
Points: 537, Visits: 28
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





Post #80076
Posted Wednesday, November 1, 2006 10:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 8:38 AM
Points: 55, Visits: 122

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

Post #319645
Posted Thursday, July 29, 2010 2:10 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:55 AM
Points: 774, Visits: 1,195
Just to add, this is what shows in SQL 2005
DBCC traceon (3604) WITH no_infomsgs         
dbcc page (9,1,6340633) with no_infomsgs, tableresults

SELECT OBJECT_NAME(167671645) -- need to run this in the target database


BUFFER: BUF @0x00000004E9FEC180 bpage 0x00000004E9B06000
BUFFER: BUF @0x00000004E9FEC180 bhash 0x0000000000000000
BUFFER: BUF @0x00000004E9FEC180 bpageno (1:6340633)
BUFFER: BUF @0x00000004E9FEC180 bdbid 9
BUFFER: BUF @0x00000004E9FEC180 breferences 0
BUFFER: BUF @0x00000004E9FEC180 bUse1 30199
BUFFER: BUF @0x00000004E9FEC180 bstat 0x5c00009
BUFFER: BUF @0x00000004E9FEC180 blog 0x979a2159
BUFFER: BUF @0x00000004E9FEC180 bnext 0x0000000000000000
PAGE HEADER: Page @0x00000004E9B06000 m_pageId (1:6340633)
PAGE HEADER: Page @0x00000004E9B06000 m_headerVersion 1
PAGE HEADER: Page @0x00000004E9B06000 m_type 1
PAGE HEADER: Page @0x00000004E9B06000 m_typeFlagBits 0x4
PAGE HEADER: Page @0x00000004E9B06000 m_level 0
PAGE HEADER: Page @0x00000004E9B06000 m_flagBits 0x200
PAGE HEADER: Page @0x00000004E9B06000 m_objId (AllocUnitId.idObj) 412
PAGE HEADER: Page @0x00000004E9B06000 m_indexId (AllocUnitId.idInd) 256
PAGE HEADER: Page @0x00000004E9B06000 Metadata: AllocUnitId 72057594064928768
PAGE HEADER: Page @0x00000004E9B06000 Metadata: PartitionId 72057594048544768
PAGE HEADER: Page @0x00000004E9B06000 Metadata: IndexId 1
PAGE HEADER: Page @0x00000004E9B06000 Metadata: ObjectId 167671645
PAGE HEADER: Page @0x00000004E9B06000 m_prevPage (1:6340634)
PAGE HEADER: Page @0x00000004E9B06000 m_nextPage (1:6340632)
PAGE HEADER: Page @0x00000004E9B06000 pminlen 64
PAGE HEADER: Page @0x00000004E9B06000 m_slotCnt 3
PAGE HEADER: Page @0x00000004E9B06000 m_freeCnt 841
PAGE HEADER: Page @0x00000004E9B06000 m_freeData 7345
PAGE HEADER: Page @0x00000004E9B06000 m_reservedCnt 0
PAGE HEADER: Page @0x00000004E9B06000 m_lsn (554547:11623:7)
PAGE HEADER: Page @0x00000004E9B06000 m_xactReserved 0
PAGE HEADER: Page @0x00000004E9B06000 m_xdesId (0:0)
PAGE HEADER: Page @0x00000004E9B06000 m_ghostRecCnt 0
PAGE HEADER: Page @0x00000004E9B06000 m_tornBits -1807182252
PAGE HEADER: Allocation Status GAM (1:6134784) ALLOCATED
PAGE HEADER: Allocation Status SGAM (1:6134785) NOT ALLOCATED
PAGE HEADER: Allocation Status PFS (1:6332904) 0x40 ALLOCATED 0_PCT_FULL
PAGE HEADER: Allocation Status DIFF (1:6134790) NOT CHANGED
PAGE HEADER: Allocation Status ML (1:6134791) NOT MIN_LOGGED


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #961006
Posted Wednesday, December 15, 2010 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 4:07 AM
Points: 16, Visits: 110
Yes absolutely - so helpful. Thank you
Post #1035094
Posted Tuesday, September 11, 2012 11:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 9:15 AM
Points: 1, Visits: 95
Just to clarify the above posts (in case you are directed here while looking in regards to SQL 2005)
Use this code for 2005:

/*FIND WAIT RESOURCE BY SPID*/
/*harvested and adjusted from :
http://www.sqlservercentral.com/Forums/Topic16430-5-1.aspx
*/
SET NOCOUNT ON declare @dbid int, @fileid int, @pageid int, @spid int, @sql varchar(128)
--set your spid of interest here:
set @spid = 95
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 '%:%:%'
select @dbid,@fileId,@pageid
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)
print @sql
select object_name([Value]) as 'waitresource object name' from
--/*adjusted for 2005*/ #pageinfo where Field = 'm_objId'
#pageinfo where Field = 'Metadata: ObjectID'
dbcc traceoff (3604) with no_infomsgs ]

Post #1357601
Posted Wednesday, May 22, 2013 6:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:57 AM
Points: 1, Visits: 10
Hello,
there is trick in your code - last char from waitresource is cutted of. I work for a while with it, and it seems that waitresource from sysprocesses always have extra char at the end.

For more clarity I do this:
select @colon1index = charindex (':', @waitresource)
if (@colon1index < 0)
return
select @colon2index = charindex (':', @waitresource, @colon1index+1)
if (@colon2index <= 0)
return

select
@dbid = substring(@waitresource, 1, @colon1index - 1),
@fileid = substring(@waitresource, @colon1index + 1, @colon2index - @colon1index - 1),
@pageid = substring(@waitresource, @colon2index + 1, len(@waitresource) - @colon2index - 1)
Post #1455461
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse