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.
This was first published in October 2003