Hi there

Ummm, I picked up on one statement and created a sproc for the master database. It returns all the basic information for locked and blocked processes in the sysprocesses table OR buffer information for a specific SPID.

CREATE PROCEDURE [dbo].[spchecklocks]

-- ==================================================================
-- Author......: John Ness / Bühler AG / Uzwil / Switzerland
-- Date........: 01-Aug-2005
-- Version.....: 1.1
-- Server......: UZN487
-- Database....: master
-- Name........: spchecklocks
-- Owner.......: dbo
-- Table.......: 
-- Type........: Stored Procedure
-- Description.: Grabs all the possible information for locked, 
--   blocked and waiting processes or for a specific
--   SPID
--   The @loginname and @srvname parameters are unused
--
-- History.....: 01-Aug-2005 1.0 JN First created
--    07-Dec-2005 1.1 JN Modified seperators 
--
-- Editor......: UltraEdit 11.10a (using Syntax Highlighting)
--   Tabstop Values = 4     
-- ==================================================================

 --input variables
 @spid  int  = null,
 @loginname varchar(50)  = '',
 @srvname varchar(20)  = ''

AS

begin

 /* Turn off double quotes for text strings */

 set quoted_identifier off

 

  /* Dont return the count for any statment */

 set nocount on

 

 /* Declare variables used only in sproc */

 declare @SQLH binary(20)

 declare @sqlstmt nvarchar(1000)  

 

 /* Add a point to the server name */

 if @srvname <> ''

  begin

   set @srvname = @srvname + '.'

  end

  

 /* Display all currently locked processes */

 print '==================================================='

 print ' Currently Locked Processes'

 print '==================================================='

 set @sqlstmt =  'select * from ' + @srvname +

   'master..sysprocesses where spid in (select req_spid from ' + @srvname +

   'master..syslockinfo where req_spid in (select spid from ' + @srvname +

   'master..syslocks )) and (open_tran = 1 or blocked != 0 or waittype != 0x0000)'

 exec sp_executesql @sqlstmt

 

 /* If @spid was supplied list various input and output buffers */

 if @spid <> '' and @spid is not null

  begin

    

   print '==================================================='

   print ' Summary for the following SPID : ' + cast(@spid as varchar(10))

   print '==================================================='

   print ' '

   print ' '

    

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   print ' Input Buffer'

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   set @sqlstmt = 'dbcc inputbuffer (' + cast(@spid as varchar(10)) + ')'

   exec sp_executesql @sqlstmt

   print ' '

   print ' '

     

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   print ' Handles for Input Buffer'

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   create table #temp_jtprochandle (

    sql_handle binary(20))

   -- The following statement is on one line

   set @sqlstmt = 'insert into #temp_jtprochandle (sql_handle) select sql_handle from master..sysprocesses where spid = '  + cast(@spid as varchar(10)) + ''

   -- The one-liner ends here

   exec sp_executesql @sqlstmt

   select @SQLH = sql_handle from #temp_jtprochandle

   select text from ::fn_get_sql(@sqlh)

   drop table #temp_jtprochandle

   print ' '

   print ' '

   

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   print ' Output Buffer'

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   set @sqlstmt = 'dbcc outputbuffer (' + cast(@spid as varchar(10)) + ')'

   exec sp_executesql @sqlstmt

   print ' '

   print ' '

   

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   print ' SP_Who'

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   set @sqlstmt = 'sp_who ' + cast(@spid as varchar(10)) + ''

   exec sp_executesql @sqlstmt

   print ' '

   print ' '

      

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   print ' SP_Who2'

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   set @sqlstmt = 'sp_who2 ' + cast(@spid as varchar(10)) + ''

   exec sp_executesql @sqlstmt

   print ' '

   print ' '

      

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   print ' Lock Info'

   print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

   set @sqlstmt = 'sp_lock ' + cast(@spid as varchar(10)) + ''

   exec sp_executesql @sqlstmt

   print ' '

   print ' '

   

  end

 

 print '==================================================='

 print ' End of Output'

 print '==================================================='

 

 set quoted_identifier on

 set nocount off

 

end

GO

I'm still working on it and hope to be able to add server and login specific information. I created it because of a problem with an application that kept locking up on me. It helped the software developer pinpoint their issue(s).

hot2use