Home > Query, SQL > BISQL # 68 – How to Kill Process Very Fast , With Analysis and Efficiently to improve performance

BISQL # 68 – How to Kill Process Very Fast , With Analysis and Efficiently to improve performance

November 4, 2011 Leave a comment Go to comments

Hi friends ,

Recently I have blogged on How to kill process !

But according to me this one is Big Procedure to take Decision to kill which process.

Or Else we can say following is basic link for how to kill process in details

BISQL # 64 – How to Check for all running process and How to Kill running process

But in our so fast life we need thing which are faster .

So I have generated following script  which u can execute anywhere and kill process

This script itself generate output as

  1. SPID
  2. DBID
  3. Database Name
  4. Query ID
  5. Executing Query – for analysis
  6. Kill Script –Directly kill script auto generated

Script :

USE
     Master
GO
SELECT 
    s.SPID,d.DBID,d.name as 'Database Name',
    y.sql_handle as 'Query ID',
    (Select text from sys.dm_exec_sql_text(y.sql_handle)) as 'Executing Query',
    'Kill ' + cast (s.SPID as nvarchar)+'--'+d.name as 'Kill Script'
FROM   SYSPROCESSES s
Left Outer join SYSDATABASES d
on s.dbid = d.dbid
Left Outer Join sys.sysprocesses y
on s.spid = y.spid
WHERE 
s.DBID NOT IN (1,2,3,4)-- 1 for Master, 2 for Tempdb, 3 for Mode, 4 for MSDB
AND  s.SPID > 50 --SQL Server reserves SPID values of 1 to 50 for internal use
AND  s.spid <> @@spid  --To exclude current user process*/

Explanation :

image

Output:

image

Advantage:

1.Use Executing query for analysis which process is running

2.Use last column directly kill the process.

Thanks for visiting my blog !!

Hope you will like this script on How to Kill Process Very Fast , With Analysis and Efficiently to improve performance

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource Website

Advertisement
Categories: Query, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: