007_ process Avira

007_ process Avira

--Create the stored procedure "P_lockinfo"

----Process Deadlock Avira

/*

View: EXEC p_lock_info 0,1

Kill: Exec P_lock_info

*/

CREATE PROCEDURE P_lockinfo

@kill_lock_spid Bit=1,--whether to kill the deadlock process. 1, kill 0, show only

@show_spid_if_nolock Bit=1-If there is no deadlock process, the normal process is displayed. 1, display 0 does not display

As

DECLARE @count int,

@s nvarchar (1000),

@i int

Select

Id=identity (int,1,1),

Sign

, 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

, Logon time =login_time

, open the number of things =open_tran

, process status =status

, workstation name =hostname

, Application name =program_name

, workstation process Id=hostprocess

, Domain name =nt_domain

, Nic address =net_address

Into #test_liwz

From

(

Select

Flag = ' Deadlock process '

, the SPID

, Kpid

, blocked

, dbid

Uid

, loginame

Cpu

, Login_time

, Open_tran

, status

, hostname

, program_name

, hostprocess

, Nt_domain

, net_address

, S1=spid

, s2=0

From

Master.. sysprocesses

where blocked=0

UNION ALL

Select

Logo =n ' L_ victims '

, the SPID

, blocked

, dbid

Uid

, Kpid

, loginame

Cpu

, Login_time

, Open_tran

, status

, hostname

, program_name

, hostprocess

, Nt_domain

, net_address

, s1=blocked

, s2=1

From

Master.. sysprocesses

where blocked<>0

) A

ORDER BY S1,s2

Select @[email protected] @ROWCOUNT, @i=1

If @count =0 and @show_spid_if_nolock =1

Begin

Insert #test_liwz

Select

Flag =n ' normal process '

, the SPID

, blocked

, dbid

Uid

, Kpid

, loginame

Cpu

, Login_time

, Open_tran

, status

, hostname

, program_name

, hostprocess

, Nt_domain

, net_address

, s1=blocked

, s2=1

From

Master.. sysprocesses

Set @[email protected] @ROWCOUNT

End

If @count >0

Begin

CREATE TABLE #test_liwz_1

(

ID int identity (+)

, a nvarchar (30)

, b int

, EventInfo nvarchar (255)

)

If @kill_lock_spid =1

Begin

DECLARE @spid varchar (10)

, @ flag varchar (10)

While @i<[email protected]

Begin

Select @spid = Process id,@ flag = flag from #test_liwz where [email protected]

Insert #test_liwz_1 EXEC (' DBCC INPUTBUFFER (' [email protected]+ ') ')

If @ @ROWCOUNT =0

Insert #test_liwz_1 (a) VALUES (NULL)

If @ sign = ' deadlock process ' exec (' kill ' [email protected])

Set @[email protected]+1

End

End

Else

While @i<[email protected]

Begin

Select @s= ' DBCC INPUTBUFFER (' +cast (process ID as varchar) + ') ' from #test_liwz where [email protected]

Insert #test_liwz_1 EXEC (@s)

If @ @ROWCOUNT =0

Set @[email protected]+1

End

Select a.*, the SQL statement for the process =b.eventinfo

From #test_liwz a join #test_liwz_1 B on a.id=b.id

ORDER by Process ID

End

SET NOCOUNT OFF

Go

007_ process Avira