SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can't Kill SPID “Transaction Rollback in Progress”


Can't Kill SPID “Transaction Rollback in Progress”

Author
Message
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10170 Visits: 4894
I'm running into frequent Blocking on a Development Server.

I have been correctly the code or making recommendations to prevent this but new code keeps getting executed and by the time I find out about it it is too late.

When I attempt to kill the SPID the Rollback is stuck at 0%.

I get the following message when I retry killing the SPID.

transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

KILL SPID WITH StatusOnly does not work.

I have had to restart the SQL Server Service, Set the Database OFFLINE and try to bring it back ONLINE and and force it to recover.

This is making me nervous.

Any suggestions?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85875 Visits: 41091
There are no easy methods to kill the damned things. The 0% rollback is actually a documented problem and, IIRC, has an open/active CONNECT item against it.

There are two types of 0% rollbacks... those that are benign and those that eat the processing time of nearly a full CPU. You can just ignore the ones that are NOT using CPU time. Tthe ones that are using scads of CPU are obviously a problem and, as of right now, the only ways to remove them is to either bounce the service or bounce the server.

As a side bar, I try not to kill spids. I send the user an email. Then I find out where they sit and pay them a personal visit to try show them what they're doing wrong. I might even help them with their code. Most of the folks are absolutely grateful.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87147 Visits: 45267
Welsh Corgi (2/26/2013)
I have had to restart the SQL Server Service, Set the Database OFFLINE and try to bring it back ONLINE and and force it to recover.


Just a restart will do, no need for the rest.

What's the query doing?

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


Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10170 Visits: 4894
The blocking that occurred happened yesterday was from a Statement that was Deleting from a Table using a Subquery.

This query was placing Exclusive Table Locks.
on two tables.

The Transaction Log was growing and since it did not seem that the Database was Rolling Back, 0%, I made an assumption that it was stuck. Did I make an incorrect assumption?


I had them using locking hints and no more blocking.

After I went through restarting the Service and recovery.

I set the Database Offline but when I attempted to bring it back online and I got an error, something to the effect that the database did not exist or I did not have permissions.

I wonder if I had included with Rollback Immediate I could have avoided this problem?

The Database was stuck offline. I checked the event viewer and I did not see any entries that indicated that the Database was in Recovery.

So I bounced the Service again and shortly after I started seeing entries in the Event Log that the Database was recovering.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10170 Visits: 4894
I have blocking going on between 2 different developers.

The blocking has been going on for nearly an hour.

I would have thought that there would have been a deadlock victim by now.

How can I get out of this situation without a database recovery or worse yet a database restore?

Edit: The machine is essentialy a production machine (long story) so it is critical.Crazy

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87147 Visits: 45267
Welsh Corgi (3/3/2013)
I have blocking going on between 2 different developers.

The blocking has been going on for nearly an hour.

I would have thought that there would have been a deadlock victim by now.


If it was a deadlock, SQL would have rolled one back. That it has not means that's not a deadlock. Just long duration blocking. Probably someone's left a transaction open and it's holding locks.

Look at the blocking chains, find whoever's at the head of the blocking chain, ask them to stop their query or commit their transaction, whichever's appropriate. If they refuse, kill the session that's at the head of the blocking chain (it'll be the one that everything else is waiting for, that's not blocked itself.

Do not restart SQL, that will result in the DB going into recovery.

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


Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10170 Visits: 4894
GilaMonster (3/3/2013)
Welsh Corgi (3/3/2013)
I have blocking going on between 2 different developers.

The blocking has been going on for nearly an hour.

I would have thought that there would have been a deadlock victim by now.


If it was a deadlock, SQL would have rolled one back. That it has not means that's not a deadlock. Just long duration blocking. Probably someone's left a transaction open and it's holding locks.

Look at the blocking chains, find whoever's at the head of the blocking chain, ask them to stop their query or commit their transaction, whichever's appropriate. If they refuse, kill the session that's at the head of the blocking chain (it'll be the one that everything else is waiting for, that's not blocked itself.

Do not restart SQL, that will result in the DB going into recovery.


Thanks Gail!

I certainly could Google Blocking Chains but do you know of a good article? I know you are probably the author. :-)

One of the developes is at developers the other is not responding.

But what about the problem when I kill the process it is stuck at 0% complete?

How can I avoid that?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87147 Visits: 45267
Welsh Corgi (3/3/2013)
I certainly could Google Blocking Chains but do you know of a good article? I know you are probably the author. :-)


Yes, you could google it. Right now I don't feel like pulling up google to search for you.

But what about the problem when I kill the process it is stuck at 0% complete?

How can I avoid that?


Most times I've seen that it's been a process that has some external component. DTC, remote procedure call, external access, extended procedure, backup. Killing a normal SQL process you shouldn't have any problems. Just don't do anything silly like restarting SQL part way through a rollback or deleting the transaction log.

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


Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10170 Visits: 4894
The block went away.

I did several searches and btw I did not expect you to search for me. I thought that you might have a favorite.

I like the following.



SELECT
spid
,sp.STATUS
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(CHAR(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0




For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10170 Visits: 4894
GilaMonster (3/3/2013)
[quote]Welsh Corgi (3/3/2013)

Most times I've seen that it's been a process that has some external component. DTC, remote procedure call, external access, extended procedure, backup. Killing a normal SQL process you shouldn't have any problems. Just don't do anything silly like restarting SQL part way through a rollback or deleting the transaction log.


Yes but what do you do when the rollback does not make any process 0% of 0% completed?

byw, The situation does not fall into any of the categories that you mentioned.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search