C # The path of the sage note--sql by creating a stored procedure to view the current process (whether it caused a table lock death)

Source: Internet
Author: User

Stored procedures:

1 Create procedureSp_who_lock2 3  as4 5 begin6 7 Declare @spid int,@bl int,8 9 @intTransactionCountOnEntry int,Ten  One          @intRowcount int, A  -          @intCountProperties int, -  the          @intCounter int -  -  -  + Create Table#tmp_lock_who ( -  +Idint Identity(1,1), A  atspidsmallint, -  -Blsmallint) -  -  -  in IF @ @ERROR<>0 RETURN @ @ERROR -  to  +  - Insert  into#tmp_lock_who (SPID,BL)Select   0, blocked the  *     from(Select *  fromsysprocesseswhereBlocked>0) A $ Panax Notoginseng    where  not exists(Select *  from(Select *  fromsysprocesseswhereBlocked>0) b -  the    whereA.blocked=spid) +  A    Union SelectSpid,blocked fromsysprocesseswhereBlocked>0 the  +  -  $ IF @ @ERROR<>0 RETURN @ @ERROR $  -  -  the --number of records found for temporary table - Wuyi Select @intCountProperties = Count(*),@intCounter = 1 the  -  from#tmp_lock_who Wu  -  About  $ IF @ @ERROR<>0 RETURN @ @ERROR -  -  -  A if @intCountProperties=0 +  the Select 'No blocking and deadlock information now'  asmessage -  $  the  the --Cycle Start the  the  while @intCounter <= @intCountProperties -  in begin the  the --take the first record About  the Select @spid =spid@bl =BL the  the  from#tmp_lock_whowhereId= @intCounter +  - begin the Bayi if @spid =0 the  the              Select 'causing the database deadlock is:'+ CAST(@bl  as VARCHAR(Ten))+ 'process number, which executes the following SQL syntax' -  - Else the  the              Select 'process number spid:'+ CAST(@spid  as VARCHAR(Ten))+ 'was' + 'process number spid:'+ CAST(@bl  as VARCHAR(Ten))+'The SQL syntax that the current process executes is blocked as follows' the  the DBCCInputBuffer (@bl ) -  the End the  the 94  the --The loop pointer moves down the  the Set @intCounter = @intCounter + 198  About End - 101 102 103 104  the Drop Table#tmp_lock_who106 107 108 109 return 0 the 111 End



1 //// viewing processes, processing processes 2 3 4 /// Close Process 5 KILL 126 6 7 8 9 /// View process Status Ten DBCC InputBuffer (a)

C # The path of the sage note--sql by creating a stored procedure to view the current process (whether it caused a table lock death)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.