Use masters - must create Go in the Master database
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_lockinfo]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_lockinfo] Go
/ * - Handling block
Check the current process, or the block process, and automatically kill the death process.
Because it is for Block, if you have a Block process, you can only view the block process. Of course, you can control it through the parameter, no matter whether there is a block, you only view the block process.
Thank: Caiyunxia, JIANGOPEN two provided reference information
- Zou Jian 2004.4 (Please keep this information) - * /
/ * - Call example
EXEC P_LOCKINFO - * / CREATE PROC P_LOCKINFO @ Kill_Lock_SPID Bit = 1, - Do you kill the block process, 1 kill, 0 only display @show_spid_if_nolock bit = 1 - If there is no Block process, do you have a normal process information, 1 Display, 0 does not display Asset NoCount OndeClare @count int, @ s nvarchar (1000), @ i Intselect ID = Identity (int, 1, 1), logo, process ID = SPID, thread id = kpid, block process id = Blocked, database ID = DBID, database name = db_name (dbid), user ID = uid, user name = loginame, cumulative CPU time = CPU, login time = login_time, open transaction number = Open_TRAN, process status = status, work station name = Hostname, App Program Name = Program_Name, Workstation Process ID = HostProcess, Domain Name = NT_DOMAIN, NIC Address = Net_Addressinto #t from (SELECT Sign = 'Block process', SPID, KPID, A. Blocked, DBID, UID, Loginame, CPU , login_time, open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address, s1 = a.spid, s2 = 0 from master..sysprocesses a join (select blocked from master..sysprocesses group by blocked) b on a.spid = b.blocked where a.blocked = 0 union all select '| _ victim (the BLOCK) _>', spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran, status, hostname, program_name, hostprocess , nt_domain, net_address, s1 = blocked, s2 = 1 from master..xysprocesses a Where blocked <> 0) a Order By S1, S2
SELECT @count = @@ rowcount, @ i = 1
if @ count = 0 and @ show_spid_if_nolock = 1begin insert #t select flag = 'normal process', spid, kpid, blocked, dbid, db_name (dbid), uid, loginame, cpu, login_time, open_tran, status, hostname, program_name , hostprocess, nt_domain, net_address from master..sysprocesses set @count = @@ rowcountendif @count> 0begin create Table # T1 (ID Int Id IDENTITY (1, 1), A NVARCHAR (30), B Int, EventInfo NVARCHAR (255) ) If @ Kill_Lock_SPID = 1 Begin Declare @SPID VARCHAR (10), @ @ 标 v VARCHAR (10) While @i <= @ count begin select @ spid = process id, @ 标 = flag from #t where id = @ i insert # T1 EXEC ('DBCC InputBuffer (' @ spid ')') IF @@ rowcount = 0 Insert # T1 (a) Values (null) if @ 标 = 'block process' EXEC ('Kill' @ spid) SET @ i = @ i 1 end end @ = @ count begin select @ s = 'DBCC INPUTBUFFER (' CAST (process ID as varchar) ')' from #t where id = @ i INSERT # T1 EXEC (@s) if @@ rowcount = 0 insert # T1 (a) values (null) set @ i = @ i 1 end select a. *, process SQL statement = B.Eventinfo from #ta Join # T1 B ON A.ID = B.ID ORDER BY process idendset nocount offgo
