Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Can't Kill SPID “Transaction Rollback in Progress” Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 4:01 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1424273
Posted Tuesday, February 26, 2013 9:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424315
Posted Wednesday, February 27, 2013 12:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1424354
Posted Wednesday, February 27, 2013 6:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1424492
Posted Sunday, March 3, 2013 9:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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.


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/

Post #1425966
Posted Sunday, March 3, 2013 9:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1425967
Posted Sunday, March 3, 2013 9:53 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1425970
Posted Sunday, March 3, 2013 10:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1425972
Posted Sunday, March 3, 2013 10:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1425976
Posted Sunday, March 3, 2013 10:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1425977
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse