May 18, 2011 at 9:58 am
Nice parabol. :hehe:
I'll keep following along but I have nothing <else> of value to offer except maybe a good luck or 2.
May 18, 2011 at 10:30 am
If only Syed Jahanzaib Bin hassan were here...
May 18, 2011 at 10:42 am
I see the irony. But I truly believe he's turning his act around so I stopped pounding on him :-).
May 19, 2011 at 1:22 am
I've got the same problem with distributed transaction and monstrous SELECT to linked SQL Server. This transaction (executed few times a day) is stuck occasionally (2 times a week) and killing this transaction(s) never ends. Moreover, this process takes 10% CPU (per transaction) and the only solution is to restart SQL Server. After many days of unsuccessful searching for solution, I "solved" this problem by moving linked data to the same instance. :o((
AS
May 19, 2011 at 8:11 am
My guess is that either the transaction has already completed or you've rebooted the server by now. However, I found an article on Microsoft's CSS site about troubleshooting and resolving an orphaned DTC transaction. A comment mentions using the Distributed Transaction Coordinator service to list all active DTC transactions, and then choose the option of "Commit", "Abort" or "Forget".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 19, 2011 at 3:11 pm
GilaMonster (5/16/2011)
Restart SQL. No need to do anything else, the transaction will finish rolling back during crash recovery (it's just kinda stuck, probably waiting for something it will never get), DB will come online most likely immediately after the restart.
Gail,
Restarting SQL Server was my first thought, too. But, what if whatever has stuck the rollback persists when SQL Server restarts?
Is it possible that a hung rollback could prevent SQL Server from restarting and becoming fully operational, effectively keeping the server offline?
LC
May 19, 2011 at 11:34 pm
Lee Crain (5/19/2011)
Restarting SQL Server was my first thought, too. But, what if whatever has stuck the rollback persists when SQL Server restarts?
Highly, highly unlikely.
If it does, Gus falls back on his plan, stop SQL, move the data and log files away, restart SQL again. Can't rollback if the DB files aren't there. Database goes recovery_pending, gets dropped, gets restored.
p.s. What would you suggest instead of restarting?
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
May 20, 2011 at 6:34 am
Had to failover the server to the passive node to get it done, but it's a done deal at this point.
You should see the "Total CPU Time" on that query in my server trace!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2011 at 6:40 am
Eric M Russell (5/19/2011)
My guess is that either the transaction has already completed or you've rebooted the server by now. However, I found an article on Microsoft's CSS site about troubleshooting and resolving an orphaned DTC transaction. A comment mentions using the Distributed Transaction Coordinator service to list all active DTC transactions, and then choose the option of "Commit", "Abort" or "Forget".
Good entry, but not really applicable here. I had already issued the kill command, and it was the rollback that hung up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2011 at 6:42 am
GilaMonster (5/19/2011)
Lee Crain (5/19/2011)
Restarting SQL Server was my first thought, too. But, what if whatever has stuck the rollback persists when SQL Server restarts?p.s. What would you suggest instead of restarting?
Restarting SQL Server was the only idea that came to my mind.
Removing the database data and log files sounds like a good plan B. I'm making a mental note to remember it.
LC
May 20, 2011 at 6:48 am
GSquared (5/20/2011)
Eric M Russell (5/19/2011)
My guess is that either the transaction has already completed or you've rebooted the server by now. However, I found an article on Microsoft's CSS site about troubleshooting and resolving an orphaned DTC transaction. A comment mentions using the Distributed Transaction Coordinator service to list all active DTC transactions, and then choose the option of "Commit", "Abort" or "Forget".Good entry, but not really applicable here. I had already issued the kill command, and it was the rollback that hung up.
So all that time the transaction wasn't blocked or waiting on a hung remote DTC call; it was just backing out a huge number of table changes?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 27, 2011 at 6:59 am
Eric M Russell (5/20/2011)
GSquared (5/20/2011)
Eric M Russell (5/19/2011)
My guess is that either the transaction has already completed or you've rebooted the server by now. However, I found an article on Microsoft's CSS site about troubleshooting and resolving an orphaned DTC transaction. A comment mentions using the Distributed Transaction Coordinator service to list all active DTC transactions, and then choose the option of "Commit", "Abort" or "Forget".Good entry, but not really applicable here. I had already issued the kill command, and it was the rollback that hung up.
So all that time the transaction wasn't blocked or waiting on a hung remote DTC call; it was just backing out a huge number of table changes?
It was stuck at 0 percent done, 0 seconds remaining.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply