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) = ''ASbegin
/* 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
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.