View those tables in the database are locked, those blocked and how to kill the process

Original address: http://topic.csdn.net/u/20100520/14/0570ec45-a1da-4067-8940-8f5eed42f4ab.html?32933

--Detection of deadlocks

--If a deadlock occurs, how do we detect which SQL statement or stored procedure has a deadlock?

--we can detect the process that caused the deadlock and the SQL statement by using the following stored procedure. SQL Server comes with system stored procedures sp_who and sp_lock can also be used to find blocking and deadlocks, but not the methods described here are useful.




Use master
Go
CREATE PROCEDURE Sp_who_lock
As
Begin
declare @spid int, @bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int

CREATE TABLE #tmp_lock_who (
ID int identity (+),
SPID smallint,
BL smallint)

IF @ @ERROR <>0 RETURN @ @ERROR

Insert into #tmp_lock_who (SPID,BL) Select 0, blocked
From (SELECT * from sysprocesses where blocked>0) a
Where NOT EXISTS (SELECT * from sysprocesses where blocked>0) b
where A.blocked=spid)
Union select spid,blocked from sysprocesses where blocked>0

IF @ @ERROR <>0 RETURN @ @ERROR

--Find the number of records for the staging table
Select @intCountProperties = Count (*), @intCounter = 1
From #tmp_lock_who

IF @ @ERROR <>0 RETURN @ @ERROR

If @intCountProperties =0
Select ' now has no blocking and deadlock information ' as message

--Cycle start
While @intCounter <= @intCountProperties
Begin
--Take the first record
Select @spid = spid, @bl = bl
From #tmp_lock_who where Id = @intCounter
Begin
If @spid =0
Select ' Causes the database deadlock: ' + CAST (@bl as VARCHAR (10) + ' process number, which executes the SQL syntax as follows '
Else
Select ' Process number spid: ' + cast (@spid as varchar (10)) + ' by ' + ' process number spid: ' + cast (@bl as varchar (10) + ' block, whose current process executes SQL syntax as follows ')
DBCC InputBuffer (@bl)
End

--The loop pointer moves down
Set @intCounter = @intCounter + 1
End

drop table #tmp_lock_who

return 0
End




--Kill locks and processes

-How to kill the process and lock manually? The simplest way to restart the service. But here we introduce a stored procedure that can kill processes and locks by explicit invocation.

Use master
Go

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_killspid] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_killspid]
GO

Create proc P_killspid
@dbname varchar (200)--the database name of the process to be closed
As
declare @sql nvarchar (500)
declare @spid nvarchar (20)

DECLARE #tb cursor FOR
Select Spid=cast (spid as varchar) from master: sysprocesses where dbid=db_id (@dbname)
Open #tb
FETCH NEXT from #tb into @spid
While @ @fetch_status =0
Begin
EXEC (' kill ' [email protected])
FETCH NEXT from #tb into @spid
End
Close #tb
Deallocate #tb
Go

--Usage
exec p_killspid ' newdbpy '




--View lock information

--How do I see the details of all the locks in my system? In Enterprise Management Manager, we can see some process and lock information, here is another method.

--View lock information
CREATE TABLE #t (req_spid int,obj_name sysname)

declare @s nvarchar (4000)
, @rid int, @dbname sysname, @id int, @objname sysname

DECLARE TB cursor FOR
SELECT DISTINCT Req_spid,dbname=db_name (rsc_dbid), Rsc_objid
From Master. syslockinfo where Rsc_type in (4,5)
Open TB
FETCH NEXT from TB to @rid, @dbname, @id
While @ @fetch_status =0
Begin
Set @s= ' select @objname =name from [' [Email protected]+ ']. sysobjects where [email protected] '
exec sp_executesql @s,n ' @objname sysname out, @id int ', @objname out, @id
INSERT into #t values (@rid, @objname)
FETCH NEXT from TB to @rid, @dbname, @id
End
Close TB
Deallocate TB

Select Process Id=a.req_spid
, Database =db_name (rsc_dbid)
, type =case rsc_type when 1 Then ' NULL resource (not used) '
When 2 Then ' database '
When 3 Then ' file '
When 4 Then ' index '
When 5 then ' table '
When 6 then ' page '
When 7 Then ' key '
When 8 then ' extended extents '
When 9 Then ' RID (row ID) '
When ten then ' application '
End
, Object Id=rsc_objid
, Object name =b.obj_name
, Rsc_indid
From Master. Syslockinfo a LEFT join #t B on a.req_spid=b.req_spid

Go
drop table #t

View those tables in the database are locked, those blocked and how to kill the process