Support_38
asked on
kill process lock Sql server
Good evening
Anyone would have some script that kills a lock process after a period of time and send email with headblock?
thank you
Anyone would have some script that kills a lock process after a period of time and send email with headblock?
thank you
Anyone would have some script that kills a lock process after a period of time and send email with headblock?Do you really want to kill a process automatically? This can have a very bad impact in your data (can origin orphan records, for example).
I understand the part of the notification but not the part of the killing action. What's usually is to receive the notification and go immediately check why the lock is happening so you can solve the issue. If you don't solve it, for sure the lock problem will keep occuring.
ASKER
Hi, Pawan Kumar
Good Morning
The script is very good, but I need it to kill only the process that is causing blocking, as it is, it kills any process that exceeds the time.
Thank you.
Good Morning
The script is very good, but I need it to kill only the process that is causing blocking, as it is, it kills any process that exceeds the time.
Thank you.
ASKER
Good Morning Vitor
Yes, I understand what you say, but this solution is temporary, what happens is that we are doing this process of killing manually.
The application is closed and we can not change much.
As I said, this is the temporary solution that is not ideal, but at least it does not leave the system unusable.
Thank you.
Yes, I understand what you say, but this solution is temporary, what happens is that we are doing this process of killing manually.
The application is closed and we can not change much.
As I said, this is the temporary solution that is not ideal, but at least it does not leave the system unusable.
Thank you.
When applying a temporary solution it should be something fast to do. If you're taking days then it means days that should be used to work on a properly and definitive solution.
Imagine that you can apply the temporary solution that you pretend. What if at same time there are backups or reindex occurring? They can also originate locks and you'll kill those processes as well. I really do not recommend you to go through this path. It may provoke collateral issues.
Something is originating those locks. If you already found what you can share with us so we'll try to provide a solution for the lock cause.
Imagine that you can apply the temporary solution that you pretend. What if at same time there are backups or reindex occurring? They can also originate locks and you'll kill those processes as well. I really do not recommend you to go through this path. It may provoke collateral issues.
Something is originating those locks. If you already found what you can share with us so we'll try to provide a solution for the lock cause.
ASKER
Hi,
As for the locks, I will take care of killing only the process that has a certain instruction, this avoids eliminating processes such as maintenance.
When the origin of the blockade, as I said, is a closed product, the manufacturer is already analyzing a possible correction.
Blocking always occurs by a cursor that remains open, however the status of the process is sleeping.
As the process status is sleeping, the sys.dm_exec_cursors DMV does not bring any information and I have to delete the process manually.
Attached is an example.
Lock_Pivotal_1801.PNG
As for the locks, I will take care of killing only the process that has a certain instruction, this avoids eliminating processes such as maintenance.
When the origin of the blockade, as I said, is a closed product, the manufacturer is already analyzing a possible correction.
Blocking always occurs by a cursor that remains open, however the status of the process is sleeping.
As the process status is sleeping, the sys.dm_exec_cursors DMV does not bring any information and I have to delete the process manually.
Attached is an example.
Lock_Pivotal_1801.PNG
Hi Alex,
I couldn't understand by your last comment if you got this sorted out or not.
Please let us know if this has been solved.
Cheers
I couldn't understand by your last comment if you got this sorted out or not.
Please let us know if this has been solved.
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good Morning Pawan,
I was able to implement the script.
Thank you.
I was able to implement the script.
Thank you.
A very good evening-
Please get the script from below-
1. It gets the required spids
2. then emails the list
3. then it kills the spids <everything is dynamic>
Ref - http://www.connectsql.com/2012/09/sql-server-automatically-detect-and.html
Open in new window
Hope it helps!