VB and VBA Users Source Code: Determine which users are blocking a SQL Server database
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Determine which users are blocking a SQL Server database
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Sunday, April 29, 2001
Hits:
558
Category:
Database/SQL/ADO
Article:
The following script creates a stored procedure which can be used to determine which users are blocking a database: if exists (select * from sysobjects where id = object_id('dbo.GetBlockers') and sysstat & 0xf = 4) drop procedure dbo.GetBlockers' GO create proc GetBlockers as /* Returns the 1. processes being blocked 2. processes doing the blocking 3. a set of dbcc inputbuffer commands which can be run to see what the blockers are doing Written on SQL Server 7 */ set nocount on print 'Blocked' select spid, status, hostname, blocked blk, cmd from master..sysprocesses SP1 where SP1.blocked>0 if @@rowcount >0 begin print '' print 'Blockers' select SP1.spid, SP1.status, SP1.hostname, SP1.blocked blk, SP1.cmd from master..sysprocesses SP1 where SP1.blocked=0 and exists (select SP2.spid from master..sysprocesses SP2 where SP2.blocked = SP1.spid) print '' print 'Run the following to see what the blockers are doing' select 'dbcc inputbuffer(', SP1.spid '', ')' from master..sysprocesses SP1 where SP1.blocked=0 and exists (select SP2.spid from master..sysprocesses SP2 where SP2.blocked = SP1.spid) end else begin print '' print 'Nobody is blocking' end GO
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
