Problem-solving blocking situations

This procedure is intended to help with problem-solving blocking situations on the server. I developed it to contact users when their client software holds locks that are holding up other users. This works faster than viewing the blocking status through Enterprise Manager. It has been tested on SQL Server 2000.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


/*Authored by Bob Hauck
@SendMessage - If set to true message will be sent via net send to client machine
@KillBlock   - If set to true the first offending process will be killed.
@Extension   - A phone number to have the user call.  Sent in net send message
*/

Create         PROCEDURE Blocking_Analysis  @SendMessage bit = 0 ,
      @KillBlock bit = 0,
      @Extension VarChar(12) = '0'
AS

Declare @WorkStation as varchar(8000)
Declare @SPID as smallint
Declare @NetSend as varchar(8000)
Declare @NT_UserName as varchar(8000)

--@SendMessage -- T = send message to user
   -- F = don't send message to user
--@KillBlock  -- T = Kill blocking process
   -- F = dont kill blocking process
--@Extension -- phone extension to have user call
Create table #temp ( spid smallint, 
   TreadID smallint,
   Blocked_by smallint,
   Wait_Type binary(2),
   Wait_Time int,
   Last_Wait_Type nchar(32),
   Wait_Resource nchar(256),
   Database_ID smallint,
   UserID smallint,
   CPU int,
   Physical_IO bigint,
   Memory_Usage int,
   Log_in_Time datetime,
   Last_Batch datetime,
   Execution_Context smallint,
   Open_Transactions smallint,
   Status nchar(30),
   GUID_or_sid binary(86),
   WorkStation nchar(128),
   Application nchar(128),
   WorkStation_PID nchar(8),
   Current_Command nchar(16),
   NT_Domain nchar(128),
   NT_UserName nchar(128),
   net_address nchar(12),
   net_library nchar(12),
   Login_Name nchar(128),
   [context_info] binary(128),
   SQL_Handle binary(20),
   stmt_start int,
   stmt_end int
      )
--Populate table with blocking data from system tables
insert #temp Select * from master.dbo.sysprocesses where blocked <> 0 or spid in (
 Select blocked from master.dbo.sysprocesses where blocked <> 0)
--Retrieve information about the blocking process
Select  @Workstation = Workstation, 
 @SPID = spid ,
 @NT_UserName = nt_username
from #temp where Blocked_by = 0
--List the involved processes and label the blocking status
select Case when Blocked_by = 0 
 then 'Blocking others' 
 else 'Blocked by ' + ltrim(rtrim(@NT_UserName))
 end'Status',
 ltrim(rtrim(NT_UserName))'User', 
 ltrim(rtrim(WorkStation))'WorkStation' 
from #temp order by blocked_by

--Send net send message to client PC to call the supplied extension 
If @SendMessage = 1
Begin

 Set @NetSend = 'Net Send ' + ltrim(rtrim(convert(varchar,@Workstation))) + 
  ' "Please call extension ' + @Extension + ' ASAP"'
 print @NetSend
 EXEC master..xp_cmdshell @NetSend
End
-- Kill the offending process if requested  default is not to kill
If @KillBlock = 1 and isnull(@SPID,-1) != -1
Begin
 EXEC ('KILL ' + @SPID)
 If @@Error = 0
 Begin
  Print 'Process ID ' + convert(varchar,@SPID) + ' has been terminated on machine ' + @Workstation
 End
 Else
 Begin
  Print 'An Error was encountered while trying to stop process ' 
   + convert(varchar,@SPID) + ' Error code ' + convert(varchar,@@Error)
 End
End

drop table #temp

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free SQL Server tips and scripts.
• Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
• Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.