Killed a SPID; Not Completing...

Question

Killed a SPID; Not Completing...

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

dajonx SSCertifiable Points: 6950 More actions · Answer 1

I have tried to failover the database to the mirror, but was blocked by the stuck SPID. Is there any other way to remove this SPID other than SQL service restart?

My Blog!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Wait, but you could wait forever. Short answer, no. If you have mirroring and you stop the service on the principal, it will failover.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dajonx SSCertifiable Points: 6950 More actions · Answer 3

dajonx

SSCertifiable

Points: 6950

February 4, 2011 at 12:30 pm

#1282640

Even without a witness server?

My Blog!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No, you do need a witness for auto failover.

Providing you're using synchronous mirroring and the state is synchronised, once the principal is down you should be able to bring the mirror online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dajonx SSCertifiable Points: 6950 More actions · Answer 5

Thank you Gail. So I would essentially have to change the mirror mode to synchronous, break the mirror, bring the mirror database out of recovery, and go from there?

My Blog!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Errr, no.

http://technet.microsoft.com/en-us/library/ms189850%28SQL.90%29.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dajonx SSCertifiable Points: 6950 More actions · Answer 7

dajonx

SSCertifiable

Points: 6950

February 4, 2011 at 12:56 pm

#1282664

Oh, ok. And this is the only way now?

My Blog!

dajonx SSCertifiable Points: 6950 More actions · Answer 8

Or how about:

ALTER DATABASE db1 WITH NO_WAIT

or

ALTER DATABASE db1 WITH ROLLBACK IMMEDIATE

Are these possible solutions?

My Blog!

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 9

dajonx (2/1/2011)
I have attached the results from sp_who2. The stuck SPID is 99. As you can see, the SPID is being blocked by itself. If there is a SPID that you might think could be actually blocking SPID 99 that I do not see, please let me know. If not, then I'll just do the failover and restart SQL service.
Thank you!
Edit: The wait type is EXECSYNC.

As a side bar... I hate Bill Gates for Office 2007 and above. He's a brilliant man forcing everyone to eventually buy new copies with his bloody format changes and I'd love to help him carry his wallet to the bank, but I hate him. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dajonx SSCertifiable Points: 6950 More actions · Answer 10

I apologize. Please find attached the .xls version of the sp_who2 results.

Does anyone happen to know if ALTER DATABASE WITH ROLLBACK IMMEDIATE or ALTER DATABASE WITH NO_WAIT will work in removing the stuck SPID? I can honestly say that this is indeed a stuck SPID because sys.dm_exec_requests results are the same except total_elapsed_time and sysprocesses results are the same along with a KPID of -29044.

Sysprocess results are (SPID, KPID, Blocked, LastWaitType, WaitResource, CPU, Physical_IO, Open_Tran, Status, Cmd):

99-2904405128646546 EXECSYNC 1891416543991suspended KILLED/ROLLBACK

9922772995257935906 LCK_M_X KEY: 12:72057594044350464 (1d01eee913b2) 456340270suspended KILLED/ROLLBACK

9929756995257562187LCK_M_X KEY: 12:72057594044350464 (fa007e20f1a5) 21000473320suspended KILLED/ROLLBACK

9927048995257935906LCK_M_X KEY: 12:72057594044350464 (3e00aa8b2e1d) 482842310suspended KILLED/ROLLBACK

If there's nothing else I can do except to ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS and then restart SQL service, then there's nothing else I can do. :/

Thank you in advance!

My Blog!

Simon Facer SSCertifiable Points: 5427 More actions · Answer 11

We had a stuck process recently - our problem was an Oracle database had issues and panicked (whatever that means), causing a query on a linked server to hang. In our case the restart was all it took, the transaction that we had tried to kill was in RollBack, but restarting SQL forces the database to go through a RollBack / RollForward cycle to clean up any incomplete transactions. I'd just follow Gail's advice and restart SQL, but be prepared for a potentially long wait on the recovery, depending on how big the transaction was; and make sure you have good backups, just in case.