September 8, 2021 at 9:38 pm
I ran a long query.. after 20 minutes I cancelled it. The session in SSMS was showing 'cancelling....'.
After it was showing that msg for another 10 minutes and cancelling, I decided to kill the SPID. and did it via Kill XXX
from a separate session.
Peculiar enough, the Kill command reported 'completed successfully'.
Yet when I looked at active SPIDs, it is still running. and the orig SSMS session showing 'Executing' and 'Cancelling' for another 15 minutes or so. Doesn't something appear a bit 'off' or irrational in what I have just descrievd?
Thank you.
Likes to play Chess
September 8, 2021 at 10:26 pm
depending on what the process is it can either take awhile or require a SQL Instance restart.
Multiple possible reasons depending on what the session was executing.
September 8, 2021 at 10:57 pm
Although it seems odd, that's actually quite normal. The KILLed process must rollback any data it needs to. Issue a:
KILL XXX WITH STATUSONLY
command. If it reports 0 and 0%, then there's no way to know how long it will take to roll back. If it provides an actual % complete, then you can wait a few mins and issue the command again and see how long it took to do x%, which you can use to interpolate the total time it should take to complete any rollback.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 9, 2021 at 12:22 am
depending on what the process is it can either take awhile or require a SQL Instance restart.
Attempting to restart SQL Server instance is a quite common mistake so called accidental DBA's.
it only would make things worse. The transaction being rolled back does not go anywhere, it still has to complete the process, but now we have the time needed for restarting, and after that the Server has to re-read the log and restart all the possible rollbacks which could be happening at the moment of restarting.
_____________
Code for TallyGenerator
September 9, 2021 at 12:36 am
Check where the sessions is connecting from, if its from SQL Server itself, then you have to wait like the guys said earlier.
Sometimes its an outside connection like service connecting to SQL Server, it has happened to me, whenever i killed the session, it was still running, i had to go to another server and stop the service that was running, you'd think that killing the session would make the service to stop running its task but sometimes it just doesn't.
September 9, 2021 at 3:20 am
And also look for OS, emailing, etc. tasks - those must be either completed (success or fail - does not matter) or terminated before the process which initiated them could be dealt with.
_____________
Code for TallyGenerator
September 9, 2021 at 7:25 am
frederico_fonseca wrote:depending on what the process is it can either take awhile or require a SQL Instance restart.
Attempting to restart SQL Server instance is a quite common mistake so called accidental DBA's.
it only would make things worse. The transaction being rolled back does not go anywhere, it still has to complete the process, but now we have the time needed for restarting, and after that the Server has to re-read the log and restart all the possible rollbacks which could be happening at the moment of restarting.
as I thought I had made very clear it all depends on what the process is doing - there are a few cases where the only solution is a restart - majority is "wait till it finishes"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply