{"id":655,"date":"2010-10-01T08:39:32","date_gmt":"2010-10-01T08:39:32","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=655"},"modified":"2013-05-07T04:00:41","modified_gmt":"2013-05-07T04:00:41","slug":"%e6%ad%bb%e9%94%81%e4%b8%8esys-sysprocesses","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2010\/10\/01\/%e6%ad%bb%e9%94%81%e4%b8%8esys-sysprocesses\/","title":{"rendered":"\u6b7b\u9501\u4e0esys.sysprocesses"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\n\u5f15\u8d77\u6b7b\u9501\u7684\u8fdb\u7a0b\u67e5\u770b\uff1a\u5b83\u6ca1\u6709\u88ab\u963b\u585e\uff0c\u4f46\u522b\u7684\u8fdb\u7a0b\u88ab\u5b83\u963b\u585e\u7740\r\n   select * from sysprocesses a where  a.blocked=0 \r\n   AND  EXISTS( select * from sysprocesses b where  b.blocked&gt;0  and b.blocked = a.spid   )\r\n\r\n*--\u5904\u7406\u6b7b\u9501\r\n\r\n\u67e5\u770b\u5f53\u524d\u8fdb\u7a0b,\u6216\u6b7b\u9501\u8fdb\u7a0b,\u5e76\u80fd\u81ea\u52a8\u6740\u6389\u6b7b\u8fdb\u7a0b\r\n\r\n\u56e0\u4e3a\u662f\u9488\u5bf9\u6b7b\u7684,\u6240\u4ee5\u5982\u679c\u6709\u6b7b\u9501\u8fdb\u7a0b,\u53ea\u80fd\u67e5\u770b\u6b7b\u9501\u8fdb\u7a0b\r\n\u5f53\u7136,\u4f60\u53ef\u4ee5\u901a\u8fc7\u53c2\u6570\u63a7\u5236,\u4e0d\u7ba1\u6709\u6ca1\u6709\u6b7b\u9501,\u90fd\u53ea\u67e5\u770b\u6b7b\u9501\u8fdb\u7a0b\r\n\r\n--*\/\r\nSET QUOTED_IDENTIFIER ON\r\nSET ANSI_NULLS ON\r\nGO\r\nALTER PROC [dbo].[p_lockinfo]\r\n    @kill_lock_spid BIT = 1 , --\u662f\u5426\u6740\u6389\u6b7b\u9501\u7684\u8fdb\u7a0b,1 \u6740\u6389, 0 \u4ec5\u663e\u793a\r\n    @show_spid_if_nolock BIT = 1 --\u5982\u679c\u6ca1\u6709\u6b7b\u9501\u7684\u8fdb\u7a0b,\u662f\u5426\u663e\u793a\u6b63\u5e38\u8fdb\u7a0b\u4fe1\u606f,1 \u663e\u793a,0 \u4e0d\u663e\u793a\r\nAS \r\n    DECLARE @count INT ,\r\n        @s VARCHAR(MAX) ,\r\n        @i INT\r\n    SELECT  id = IDENTITY( INT,1,1 ),\r\n            \u6807\u5fd7 ,\r\n            \u8fdb\u7a0bID = spid ,\r\n            \u7ebf\u7a0bID = kpid ,\r\n            \u5757\u8fdb\u7a0bID = blocked ,\r\n            \u6570\u636e\u5e93ID = dbid ,\r\n            \u6570\u636e\u5e93\u540d = DB_NAME(dbid) ,\r\n            \u7528\u6237ID = uid ,\r\n            \u7528\u6237\u540d = loginame ,\r\n            \u7d2f\u8ba1CPU\u65f6\u95f4 = cpu ,\r\n            \u767b\u9646\u65f6\u95f4 = login_time ,\r\n            \u6253\u5f00\u4e8b\u52a1\u6570 = open_tran ,\r\n            \u8fdb\u7a0b\u72b6\u6001 = status ,\r\n            \u5de5\u4f5c\u7ad9\u540d = hostname ,\r\n            \u5e94\u7528\u7a0b\u5e8f\u540d = program_name ,\r\n            \u5de5\u4f5c\u7ad9\u8fdb\u7a0bID = hostprocess ,\r\n            \u57df\u540d = nt_domain ,\r\n            \u7f51\u5361\u5730\u5740 = net_address,\r\nsql_handle,\r\nstmt_start,\r\nstmt_end\r\n\r\n    INTO    #t\r\n    FROM    ( SELECT    \u6807\u5fd7 = &#39;\u6b7b\u9501\u7684\u8fdb\u7a0b&#39; ,\r\n                        spid ,\r\n                        kpid ,\r\n                        a.blocked ,\r\n                        dbid ,\r\n                        uid ,\r\n                        loginame ,\r\n                        cpu ,\r\n                        login_time ,\r\n                        open_tran ,\r\n                        status ,\r\n                        hostname ,\r\n                        program_name ,\r\n                        hostprocess ,\r\n                        nt_domain ,\r\n                        net_address ,\r\n                        s1 = a.spid ,\r\n                        s2 = 0,\r\nsql_handle,\r\nstmt_start,\r\nstmt_end\r\n              FROM      master..sysprocesses a\r\n                        JOIN ( SELECT   blocked\r\n                               FROM     master..sysprocesses\r\n                               GROUP BY blocked\r\n                             ) b ON a.spid = b.blocked\r\n              WHERE     a.blocked = 0\r\n              UNION ALL\r\n              SELECT    &#39;|_\u727a\u7272\u54c1_&gt;&#39; ,\r\n                        spid ,\r\n                        kpid ,\r\n                        blocked ,\r\n                        dbid ,\r\n                        uid ,\r\n                        loginame ,\r\n                        cpu ,\r\n                        login_time ,\r\n                        open_tran ,\r\n                        status ,\r\n                        hostname ,\r\n                        program_name ,\r\n                        hostprocess ,\r\n                        nt_domain ,\r\n                        net_address ,\r\n                        s1 = blocked ,\r\n                        s2 = 1,\r\nsql_handle,\r\nstmt_start,\r\nstmt_end\r\n              FROM      master..sysprocesses a\r\n              WHERE     blocked &lt;&gt; 0\r\n            ) a\r\n    ORDER BY s1 ,\r\n            s2\r\n\r\n    SELECT  @count = @@rowcount ,\r\n            @i = 1\r\n\r\n    IF @count = 0\r\n        AND @show_spid_if_nolock = 1 \r\n        BEGIN\r\n            INSERT  #t\r\n                    SELECT  \u6807\u5fd7 = &#39;\u6b63\u5e38\u7684\u8fdb\u7a0b&#39; ,\r\n                            spid ,\r\n                            kpid ,\r\n                            blocked ,\r\n                            dbid ,\r\n                            DB_NAME(dbid) ,\r\n                            uid ,\r\n                            loginame ,\r\n                            cpu ,\r\n                            login_time ,\r\n                            open_tran ,\r\n                            status ,\r\n                            hostname ,\r\n                            program_name ,\r\n                            hostprocess ,\r\n                            nt_domain ,\r\n                            net_address,\r\nsql_handle,\r\nstmt_start,\r\nstmt_end\r\n                    FROM    master..sysprocesses\r\n            SET @count = @@rowcount\r\n        END\r\n\r\n    IF @count &gt; 0 \r\n        BEGIN\r\n            CREATE TABLE #t1\r\n                (\r\n                  id INT IDENTITY(1, 1) ,\r\n                  a NVARCHAR(30) ,\r\n                  b INT ,\r\n                  EventInfo NVARCHAR(255)\r\n                )\r\n            IF @kill_lock_spid = 1 \r\n                BEGIN\r\n                    DECLARE @spid VARCHAR(10) ,\r\n                        @\u6807\u5fd7 VARCHAR(10)\r\n                    WHILE @i &lt;= @count \r\n                        BEGIN\r\n                            SELECT  @spid = \u8fdb\u7a0bID ,\r\n                                    @\u6807\u5fd7 = \u6807\u5fd7\r\n                            FROM    #t\r\n                            WHERE   id = @i\r\n                            INSERT  #t1\r\n                                    EXEC ( &#39;dbcc inputbuffer(&#39; + @spid + &#39;)&#39;\r\n                                        )\r\n                            IF @\u6807\u5fd7 = &#39;\u6b7b\u9501\u7684\u8fdb\u7a0b&#39; \r\n                                EXEC(&#39;kill &#39;+@spid)\r\n                            SET @i = @i + 1\r\n                        END\r\n                END\r\n            ELSE \r\n                WHILE @i &lt;= @count \r\n                    BEGIN\r\n                        SELECT  @s = &#39;dbcc inputbuffer(&#39;\r\n                                + CAST(\u8fdb\u7a0bID AS VARCHAR) + &#39;)&#39;\r\n                        FROM    #t\r\n                        WHERE   id = @i\r\n                        INSERT  #t1\r\n                                EXEC ( @s\r\n                                    )\r\n                        SET @i = @i + 1\r\n                    END\r\n            SELECT  \u6807\u5fd7 ,\r\n\u6570\u636e\u5e93\u540d ,\r\n \u8fdb\u7a0b\u7684SQL\u8bed\u53e5 = b.EventInfo,\r\nsqlText=substring(qt.text,a.stmt_start\/2, \r\n  (case when a.stmt_end = -1 \r\n then len(convert(nvarchar(max), qt.text)) * 2 \r\n else a.stmt_end end -a.stmt_start)\/2\r\n) ,\r\n\u8fdb\u7a0b\u72b6\u6001,\r\n            \u7528\u6237\u540d,\r\n            \u7d2f\u8ba1CPU\u65f6\u95f4,\r\n            \u767b\u9646\u65f6\u95f4  ,\r\n            \u5de5\u4f5c\u7ad9\u540d ,\r\n            \u5e94\u7528\u7a0b\u5e8f\u540d,\r\n\u8fdb\u7a0bID ,\r\n            \u7ebf\u7a0bID  \r\n            FROM    #t a\r\n                    JOIN #t1 b ON a.id = b.id\r\nOUTER apply sys.dm_exec_sql_text(a.sql_handle) as qt\r\n        END\r\nGO\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5f15\u8d77\u6b7b\u9501\u7684\u8fdb\u7a0b\u67e5\u770b\uff1a\u5b83\u6ca1\u6709\u88ab\u963b\u585e\uff0c\u4f46\u522b\u7684\u8fdb\u7a0b\u88ab\u5b83\u963b\u585e\u7740 select * from sysprocesses  [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-655","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/655","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=655"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/655\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=655"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=655"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=655"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}