• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1167
  • Last Modified:

How to terminate a processes in MS SQL Server

Hi All,
I am not able to terminate a process which is locking more than 2 day's. I have tried to terminate the process, but I cant. Still the job is running. This process lock other processes.

How could i terminate the process without reboot?
0
Srikanthsubramanian
Asked:
Srikanthsubramanian
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In SQL Query Analyzer run command KILL. Ex:
Kill [ProcessNumber]
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>I have tried to terminate the process,
How did you try ? Did you use "KILL"  statement , otherwise try it

as a second option, stop SQL Server Agent service

and if it is still running, restart the SQLServr service  or else restart the machine.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You  must be careful if it's a production enviroment and you going to stop SQL Server Agent or SQL Server service. And be more careful if you have more databases in that instance. You'll stop ALL jobs and ALL databases will became offline.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
SrikanthsubramanianAuthor Commented:
Hi am expecting alternate option. I have planed to restart the service end of day. Is there any way to terminate the process without reboot?
0
 
MrNeticCommented:
Srikanthsubramanian,

You should check if it is in a middle of a rollback, you could make inconcistencies in the DB...
What is the outpup of KILL SPID WITHSTATUSONLY .
0
 
MrNeticCommented:
What is the outpup of KILL SPID WITH STATUSONLY .
What is the SQL SERVER VERSION ( select @@version )?
0
 
macentrapCommented:
along with MrNetic advise, on Kill Spid withstatusonly ( might be in Rollback state)

this might help (I have used in only twice)
http://grahambrooks.blogspot.com/2007/06/terminate-sql-server-processes-for.html
0
 
SrikanthsubramanianAuthor Commented:
I tried lot of times but not able to terminate the processes. This has been resolved after reboot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now