SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Decyphering waitresource


Decyphering waitresource

Author
Message
larryd
larryd
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 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
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 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
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 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
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3292 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
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 144
Yes absolutely - so helpful. Thank you:-)
E P-402913
E P-402913
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

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