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 ]