January 31, 2011 at 3:17 pm
Hi,
I was wondering if there is a way to remove a killed SPID that has been in rollback for a week without restarting SQL service. The SPID was doing an index rebuild. If I use database mirroring to server B, failover, delete/restore the database on server A, and redo mirroring, will it remove the killed SPID? Is there a better way to resolve this problem?
Thank you in advance!
January 31, 2011 at 3:45 pm
Restart SQL is the fastest way. Failover to the mirror and then restart the SQL service on the principal. That's all it'll take.
Drop database, restore, restart mirroring strikes me as way more time-intensive and intrusive than restarting SQL on a server that won't even be in use (after a mirroring failover)
Besides, I don't think you'll be able to drop the DB with an open transaction, even if it is in rollback.
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
January 31, 2011 at 3:52 pm
dajonx (1/31/2011)
Hi,I was wondering if there is a way to remove a killed SPID that has been in rollback for a week without restarting SQL service. The SPID was doing an index rebuild. If I use database mirroring to server B, failover, delete/restore the database on server A, and redo mirroring, will it remove the killed SPID? Is there a better way to resolve this problem?
Thank you in advance!
Do what Gail said. I don't know why spids get stuck like this in a rollback state, but I've seen them hang around for a few months. Simply restarting the SQL service will clear them. This is usually more of an issue when you don't have a failover already available, but it usually doesn't take long unless you've got some recovery that needs to still happen.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2011 at 8:43 am
The mirroring will still exist, it'll be either suspended or disconnected (I expect).
Recovery can be anything from immediate to hours, it depends on the fragmentation in the log, the amount that has to be processed, the IO subsystem, etc.
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
February 1, 2011 at 10:06 am
SQL Error log or sys.dm_exec_requests
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
February 1, 2011 at 10:27 am
Craig Farrell (1/31/2011)
dajonx (1/31/2011)
Hi,I was wondering if there is a way to remove a killed SPID that has been in rollback for a week without restarting SQL service. The SPID was doing an index rebuild. If I use database mirroring to server B, failover, delete/restore the database on server A, and redo mirroring, will it remove the killed SPID? Is there a better way to resolve this problem?
Thank you in advance!
Do what Gail said. I don't know why spids get stuck like this in a rollback state, but I've seen them hang around for a few months. Simply restarting the SQL service will clear them. This is usually more of an issue when you don't have a failover already available, but it usually doesn't take long unless you've got some recovery that needs to still happen.
I've seen several instances of such a thing happening. Although I don't claim it to be a panacea, I've found the another session on a different SPID is typically involved in one form or another. So far, everytime I've been able to find and KILL that other SPID, the stuck ROLLBACK completes in seconds.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2011 at 10:34 am
Jeff Moden (2/1/2011)
Craig Farrell (1/31/2011)
dajonx (1/31/2011)
Hi,I was wondering if there is a way to remove a killed SPID that has been in rollback for a week without restarting SQL service. The SPID was doing an index rebuild. If I use database mirroring to server B, failover, delete/restore the database on server A, and redo mirroring, will it remove the killed SPID? Is there a better way to resolve this problem?
Thank you in advance!
Do what Gail said. I don't know why spids get stuck like this in a rollback state, but I've seen them hang around for a few months. Simply restarting the SQL service will clear them. This is usually more of an issue when you don't have a failover already available, but it usually doesn't take long unless you've got some recovery that needs to still happen.
I've seen several instances of such a thing happening. Although I don't claim it to be a panacea, I've found the another session on a different SPID is typically involved in one form or another. So far, everytime I've been able to find and KILL that other SPID, the stuck ROLLBACK completes in seconds.
You mean like a deadlock never resolving?
February 1, 2011 at 10:57 am
Jeff Moden (2/1/2011)
I've seen several instances of such a thing happening. Although I don't claim it to be a panacea, I've found the another session on a different SPID is typically involved in one form or another. So far, everytime I've been able to find and KILL that other SPID, the stuck ROLLBACK completes in seconds.
Good point. Check that the rolling back session doesn't have another session referenced in the blocked_by column. Also check the wait type
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
February 1, 2011 at 2:54 pm
Ninja's_RGR'us (2/1/2011)
Jeff Moden (2/1/2011)
Craig Farrell (1/31/2011)
dajonx (1/31/2011)
Hi,I was wondering if there is a way to remove a killed SPID that has been in rollback for a week without restarting SQL service. The SPID was doing an index rebuild. If I use database mirroring to server B, failover, delete/restore the database on server A, and redo mirroring, will it remove the killed SPID? Is there a better way to resolve this problem?
Thank you in advance!
Do what Gail said. I don't know why spids get stuck like this in a rollback state, but I've seen them hang around for a few months. Simply restarting the SQL service will clear them. This is usually more of an issue when you don't have a failover already available, but it usually doesn't take long unless you've got some recovery that needs to still happen.
I've seen several instances of such a thing happening. Although I don't claim it to be a panacea, I've found the another session on a different SPID is typically involved in one form or another. So far, everytime I've been able to find and KILL that other SPID, the stuck ROLLBACK completes in seconds.
You mean like a deadlock never resolving?
Kind of. More like someone doing a BEGIN TRAN and never doing a COMMIT.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2011 at 2:56 pm
GilaMonster (2/1/2011)
Jeff Moden (2/1/2011)
I've seen several instances of such a thing happening. Although I don't claim it to be a panacea, I've found the another session on a different SPID is typically involved in one form or another. So far, everytime I've been able to find and KILL that other SPID, the stuck ROLLBACK completes in seconds.Good point. Check that the rolling back session doesn't have another session referenced in the blocked_by column. Also check the wait type
In several cases, there wasn't an entry in the blocked column. Admittedly, it's sometimes a bit difficult to surmise what to kill. I've been fortunate in that folks have always known what they were running and I can figure out what to look for in those other sessions.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2011 at 3:19 pm
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.
February 2, 2011 at 6:48 am
I also had that issue a while back.
It's probably not as restricted as what I'll list but this is what I've observed so far:
Not machine dependent (happened 3 times on 3 different machine)
It happens only on a table having a clustered index.
One query is doing a select having a keylook up operation
The other query is trying to rebuild the index.
No LOB into the table being rebuilt.
Table was pretty small (less than 10k rows)
No transaction is involved in either queries.
All of the heap tables never got afflicted by this (or I'm lucky it didn't)
Fortunately, those tables having a clustered index are never changes so I've rebuild all indexes with 100% fill factor & pad index and removed them from the maintenance. Never had any issue since that but it might not be possible for all table on your side.
It happened using 2008. We have moved to r2 since but the fix's still in place so I cannot say if r2 solved that or not.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply