{"id":520,"date":"2011-07-01T12:23:19","date_gmt":"2011-07-01T04:23:19","guid":{"rendered":"http:\/\/nick.workao.org\/?p=520"},"modified":"2011-07-01T12:23:19","modified_gmt":"2011-07-01T04:23:19","slug":"sql-server-2005%e7%bb%93%e6%9d%9fsleeping%e7%9a%84%e8%bf%9b%e7%a8%8b%e8%af%ad%e5%8f%a5","status":"publish","type":"post","link":"http:\/\/nick.txtcc.com\/index.php\/win\/520","title":{"rendered":"sql server 2005\u7ed3\u675fsleeping\u7684\u8fdb\u7a0b\u8bed\u53e5"},"content":{"rendered":"<p><code>SET NOCOUNT ON;\nDECLARE @host VARCHAR(50), @login NVARCHAR(128);\nSET @host = 'dawoo'; --\u5ba2\u6237\u7aef\u540d\u79f0\uff0cNULL to kill sessions from all hosts.\nSET @login = 'tscn';    --\u767b\u5f55\u540d\nDECLARE @cmd NVARCHAR(255);\nDECLARE @possition INT, @total INT, @selSpid SMALLINT;\nDECLARE @spidInfo TABLE\n(\n[id] INT IDENTITY(1,1),\nspid SMALLINT,\nloginame NVARCHAR(128)\n);\nINSERT @spidInfo(spid, loginame)\nSELECT session_id, login_name&lt;br \/&gt;\nFROM sys.dm_exec_sessions\nWHERE is_user_process = 1 AND [status] = 'sleeping' AND&lt;br \/&gt;\nlogin_name = @login AND [host_name] = COALESCE(@host, [host_name]);\nSELECT @total = @@IDENTITY, @selSpid = 0, @possition = 0;\nWHILE @possition &lt; @total\nBEGIN\nSELECT TOP 1 @selSpid = spid, @possition = [id]\nFROM @spidInfo\nWHERE [ID] &gt; @possition\nSET @cmd = N'KILL ' + CAST(@selSpid AS NVARCHAR(10));\nEXEC sp_executesql @cmd;\nPRINT 'SessionId = ' + CAST(@selSpid AS NVARCHAR(10)) + '[' + @login +&lt;br \/&gt;\n'] killed by ' + system_user + ' at ' + CAST(GETDATE() AS VARCHAR(50));\nEND;\nIF (@total = 0)\nPRINT 'No sessions owned by user ' + '[' + @login + ']'; <\/code><\/p>","protected":false},"excerpt":{"rendered":"<p>SET NOCOUNT ON; DECLARE @host VARCHAR(50), @login NVARCHAR(128); SET @host = &#8216;dawoo&#8217;; &#8211;\u5ba2\u6237\u7aef\u540d\u79f0\uff0cNULL t&#46;&#46;&#46;<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[14,302,256],"class_list":["post-520","post","type-post","status-publish","format-standard","hentry","category-win","tag-server","tag-sleep","tag-sql"],"_links":{"self":[{"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/posts\/520","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/comments?post=520"}],"version-history":[{"count":1,"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/posts\/520\/revisions"}],"predecessor-version":[{"id":521,"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/posts\/520\/revisions\/521"}],"wp:attachment":[{"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/media?parent=520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/categories?post=520"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/tags?post=520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}