September 18, 2003 at 10:41 am
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!
September 18, 2003 at 10:58 am
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
September 18, 2003 at 2:07 pm
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,
@pageidint,
@spidint,
@sqlvarchar(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
November 1, 2006 at 10:00 am
-- 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
July 29, 2010 at 2:10 pm
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 @0x00000004E9FEC180bpage0x00000004E9B06000
BUFFER:BUF @0x00000004E9FEC180bhash0x0000000000000000
BUFFER:BUF @0x00000004E9FEC180bpageno(1:6340633)
BUFFER:BUF @0x00000004E9FEC180bdbid9
BUFFER:BUF @0x00000004E9FEC180breferences0
BUFFER:BUF @0x00000004E9FEC180bUse130199
BUFFER:BUF @0x00000004E9FEC180bstat0x5c00009
BUFFER:BUF @0x00000004E9FEC180blog0x979a2159
BUFFER:BUF @0x00000004E9FEC180bnext0x0000000000000000
PAGE HEADER:Page @0x00000004E9B06000m_pageId(1:6340633)
PAGE HEADER:Page @0x00000004E9B06000m_headerVersion1
PAGE HEADER:Page @0x00000004E9B06000m_type1
PAGE HEADER:Page @0x00000004E9B06000m_typeFlagBits0x4
PAGE HEADER:Page @0x00000004E9B06000m_level0
PAGE HEADER:Page @0x00000004E9B06000m_flagBits0x200
PAGE HEADER:Page @0x00000004E9B06000m_objId (AllocUnitId.idObj)412
PAGE HEADER:Page @0x00000004E9B06000m_indexId (AllocUnitId.idInd)256
PAGE HEADER:Page @0x00000004E9B06000Metadata: AllocUnitId72057594064928768
PAGE HEADER:Page @0x00000004E9B06000Metadata: PartitionId72057594048544768
PAGE HEADER:Page @0x00000004E9B06000Metadata: IndexId1
PAGE HEADER:Page @0x00000004E9B06000Metadata: ObjectId167671645
PAGE HEADER:Page @0x00000004E9B06000m_prevPage(1:6340634)
PAGE HEADER:Page @0x00000004E9B06000m_nextPage(1:6340632)
PAGE HEADER:Page @0x00000004E9B06000pminlen64
PAGE HEADER:Page @0x00000004E9B06000m_slotCnt3
PAGE HEADER:Page @0x00000004E9B06000m_freeCnt841
PAGE HEADER:Page @0x00000004E9B06000m_freeData7345
PAGE HEADER:Page @0x00000004E9B06000m_reservedCnt0
PAGE HEADER:Page @0x00000004E9B06000m_lsn(554547:11623:7)
PAGE HEADER:Page @0x00000004E9B06000m_xactReserved0
PAGE HEADER:Page @0x00000004E9B06000m_xdesId(0:0)
PAGE HEADER:Page @0x00000004E9B06000m_ghostRecCnt0
PAGE HEADER:Page @0x00000004E9B06000m_tornBits-1807182252
PAGE HEADER:Allocation StatusGAM (1:6134784)ALLOCATED
PAGE HEADER:Allocation StatusSGAM (1:6134785)NOT ALLOCATED
PAGE HEADER:Allocation StatusPFS (1:6332904)0x40 ALLOCATED 0_PCT_FULL
PAGE HEADER:Allocation StatusDIFF (1:6134790)NOT CHANGED
PAGE HEADER:Allocation StatusML (1:6134791)NOT MIN_LOGGED
December 15, 2010 at 6:54 am
Yes absolutely - so helpful. Thank you:-)
September 11, 2012 at 11:24 am
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 ]
May 22, 2013 at 6:42 am
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)
October 1, 2015 at 2:05 am
Thanks !!! It helped !!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply