January 23, 2014 at 12:36 am
Hi,
I am using Sql server 2008 r2
I Created the Job through sp_add_job
eg, Job name = 'Test_job'
with step command = Exec SPname
This SP takes half hour to execute.
Now,my requirement is, I want to Kill the job,and query running associated with this should also be stopped.
also all this should be done using commands not the wizard.
Please help me,as I need this urgently.
Thanks in advance!!!!
January 23, 2014 at 2:28 am
By running the job it starts a session. What you need is find the spid and execute
KILL spid
USE msdb
GO
EXEC dbo.sp_help_jobactivity
GO
you can find the job and see some details of it.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
January 23, 2014 at 4:44 am
You can query sys.dm_exec_requests in combination with sys.dm_exec_sql_text to identify the query and session and then kill it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 23, 2014 at 5:17 am
I do a kill on current users (there should be none) just before a full backup. I create a temp table of users with:
SELECT
P.spid as SPID,
P.loginame as CurLogin,
P.hostname as HostName,
D.name as DBName,
P.cmd as Command,
P.cpu as CPUTime,
P.physical_io as DiskIO,
P.last_batch as LastBatch,
P.[program_name] as ProgramName
FROM sys.sysprocesses P
LEFT JOIN sys.databases D ON P.dbid = D.database_id
WHERE P.spid > 50
AND P.loginame NOT IN ('SpecialLogin')
AND P.hostname NOT IN ('SpecialHost')
-- select nothing for KnownKeep that is active within the last hour from kill list
AND NOT (D.name = 'KnownKeep'
AND p.last_batch > DATEADD(hour, -1, GETDATE())); This may be of help. I am going to look into the other suggestions to see if they may be of help for my process.
January 23, 2014 at 5:50 am
Session ID > 50 is not a good way to determine system processes. Instead use a join to sys.dm_exec_sessions to use is_user_process something like this:
SELECT *
FROM sys.dm_exec_requests AS der
JOIN sys.dm_exec_sessions AS des
ON der.session_id = des.session_id
WHERE des.is_user_process = 1 ;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 23, 2014 at 5:59 am
Thanks for the tip. The code was developed in SQL2000 and never updated properly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply