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.

Download this free guide

Download: The Developer's Guide to IoT

The IoT world may be exciting, but there are serious technical challenges that need to be addressed, especially by developers. In this handbook, learn how to meet the security, analytics, and testing requirements for IoT applications.

Start Download

• Corporate E-mail Address:

  You forgot to provide an Email Address.

  This email address doesn’t appear to be valid.

  This email address is already registered. Please login.

  You have exceeded the maximum character limit.

  Please provide a Corporate E-mail Address.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

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.