Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Decyphering waitresource


Decyphering waitresource

Author
Message
larryd
larryd
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 30
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!



sxg6023
sxg6023
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
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



larryd
larryd
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 30
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



Ion Freeman
Ion Freeman
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 123

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


Jerry Hung
Jerry Hung
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 1208
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
Random Visitor
Random Visitor
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 144
Yes absolutely - so helpful. Thank you:-)
E P-402913
E P-402913
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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 ]


ciborek
ciborek
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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)

SUBRAHMANYA HEDGE
SUBRAHMANYA HEDGE
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 406
Thanks !!! It helped !!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search