May 16, 2011 at 12:57 pm
I've got a staging database that's had a transaction rolling back since last Thursday. KILL 85 WITH STATUSONLY still shows 0% done, 0 seconds remaining, on the rollback, 5 days later, on the affected SPID. (I really do love MS's time estimates on how long things will take to complete. Files downloading with 1 minute left, no wait, 2 minutes left, now 10 seconds left, but really 14 hours left, done. 0 seconds remaining, for 5 days. Gotta laugh at that!)
I've worked around that by restoring a prior backup with a new name, and pointing the affected ETL processes at that copy of the database. But I still don't like leaving a transaction rolling back indefinitely. (The whole job that had to be killed usually takes about 15 minutes to commit, so a 5-day rollback seems excessive.)
What I'm looking at potentially doing is forcing a stop on the SQL service on that server, moving the data file for that database so it can't be brought back online, and then starting the service back up. I can make sure all the other databases are clean before doing that, so a forced shut-down should only affect that one database. Worst case scenario is a hardware kill and reboot.
So, it can be done, but should it? Any better plans out there on this one?
- 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 16, 2011 at 1:27 pm
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 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 16, 2011 at 1:33 pm
Yeah, probably best to try that first.
- 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 17, 2011 at 1:11 am
Before restarting the server How about running DBCC CHECKDB to check database consistancy and any errors?
If able to get sufficient time to perform it as a safer side.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 17, 2011 at 6:59 am
free_mascot (5/17/2011)
Before restarting the server How about running DBCC CHECKDB to check database consistancy and any errors?If able to get sufficient time to perform it as a safer side.
HTH
Already did that. Nothing came 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 17, 2011 at 7:22 am
Oh, forgot to ask. Is this a distributed transaction? (some reference to a remote server)?
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 18, 2011 at 6:09 am
GilaMonster (5/17/2011)
Oh, forgot to ask. Is this a distributed transaction? (some reference to a remote server)?
Yes. It's an ETL staging database that pulls from one server and feeds to another. That's why it won't matter if I have to dump it and replace it.
I have tried restarting the DTC service, if that's what you're going to ask next.
- 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 18, 2011 at 6:34 am
I was. :hehe:
Also check see if you have any locks owned by spid -2.
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 18, 2011 at 9:16 am
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id < 0;
Returns zero rows.
Does that look like the right query for that?
- 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 18, 2011 at 9:20 am
I'd have used sp_WhoisActive (parameter for system transactions = 1). Maybe that can give you another hint.
I never faced that issue so this is more of a shotgun approach to see what sticks!
May 18, 2011 at 9:25 am
Yup, and 0 is what you want. So much for that train of thought
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 18, 2011 at 9:29 am
Wow that's a scary thought. A problem that both of your brains combined have troubles dealing with :w00t:.
I never thought I'd see that day!
May 18, 2011 at 9:34 am
Ninja's_RGR'us (5/18/2011)
I'd have used sp_WhoisActive (parameter for system transactions = 1). Maybe that can give you another hint.I never faced that issue so this is more of a shotgun approach to see what sticks!
Doesn't reveal anything that seems to bear on this.
Two open transactions on the unkillable SPID, nothing blocking it, it's rolling back, etc.
- 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 18, 2011 at 9:38 am
Ok now that's 3 of us. Reallllly getting freaky here :blink:.
hopefully gail has another train of thoughs on the rails for you!
May 18, 2011 at 9:44 am
Ninja's_RGR'us (5/18/2011)
Wow that's a scary thought. A problem that both of your brains combined have troubles dealing with :w00t:.I never thought I'd see that day!
Nah. My brain knows what to do, as does Gail's. Restart the SQL service. It's just such an ugly solution that I'm hoping for something better. Not expecting, hoping.
Like, if there were an option to tell SQL Server, "Yes, I know that transaction needs to roll back, but in this particular case, I'm actually perfectly fine with an inconsistent database. Kill the rollback, I'll fix it, and then I'll tell you when it's okay to call it consistent again." Because of the horrible damage inexperienced/untrained/willfully incompetent DBAs would certainly do with such a feature, I can understand leaving it out, but there are times when I wish they didn't have to always code for the lowest common denominator in these regards.
Would be kind of like buying a car without a crash frame and crumple zones and bumpers and such. Yeah, a skilled driver on a safe road would get better fuel efficiency, et al, with it. But you just know that thousands of teenagers suffering from testosterone toxicity would kill themselves and others in it.
- 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 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply