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

Killed 5 Processes Table Locks Yesterday, Still in Killed/Rollback Status Expand / Collapse
Author
Message
Posted Saturday, July 7, 2012 8:04 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 got a e-mail yesterday after hours from a Lead and he told me that he thought they had locks on a table and asked me to investigate.

Sure enough there were locks 5 locks on the same table.

I killed the processes yesterday, 17 hours ago and the processes are still in a rollback status.

I try to kill the processes again the the completion of the rollback is zero percent.

What can I do for it appears that the rollback is not going to complete?


SPID 57: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
SPID 71: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
SPID 69: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
SPID 61: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.


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 #1326427
Posted Saturday, July 7, 2012 8:17 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 restarted the SQL Server Service for that instance to get rid of those processes

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 #1326429
Posted Saturday, July 7, 2012 9:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 17,729, Visits: 15,597
Welsh Corgi (7/7/2012)
I restarted the SQL Server Service for that instance to get rid of those processes


Unfortunately, as a last resort, that is the only other thing that can be done. First option is to wait it out.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1326445
Posted Saturday, July 7, 2012 10:10 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 had killed the processes 17 hours before I restarted the Service.

The percent complete of the rollback was 0% on all four processes.

What I found odd was that the Last Batch Dates were one 6/09/2012 and three 6/10/2012.

They were executing Stored Procedures with an OPENQUERY Statement to a remote DB2 Database on an AS400.



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 #1326452
Posted Saturday, July 7, 2012 10:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:48 PM
Points: 72, Visits: 204
Sometimes restarting the SQL Services will not clear the killed rollback state. Please restart the server. It will get cleared.

Regards
S.R.Kundur
Post #1326456
Posted Saturday, July 7, 2012 12:52 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Welsh Corgi (7/7/2012)
I had killed the processes 17 hours before I restarted the Service.

The percent complete of the rollback was 0% on all four processes.

What I found odd was that the Last Batch Dates were one 6/09/2012 and three 6/10/2012.

They were executing Stored Procedures with an OPENQUERY Statement to a remote DB2 Database on an AS400.



Actually, this is a well know problem to those using OPENQUERY to a remote DB2 database on an AS400. I went through the very same problem just about a week ago. It turns out that there's a "CONNECT" item on the subject and MS closed it with a "Call Customer Support when this happens" answer.

What happens with these things is that the rollbacks were actually successful and the "0%" to go message (in these instances) is actually correct. The problem with the continued attempts at rollbacks is that the SPIDs are actually consuming a whole lot of CPU time. It's like they're stuck in a loop or something. They also keep locks on the database which can sometimes interfere with things such as dropping the database (which I do every night on my test restore system).

Once all other methods to try to overcome this problem have been exhausted, bouncing the service (or even the machine itself) is the only way to kill these things. The problem with that is that not all rollbacks are so kind. If there actually is something to rollback, the rollback (maybe roll forward) will continue during the startup. I've built some code at work that I used to determine if there was actually anything to rollback to see if it was actually safe to bounce the service.

There's one more thing that you have to do after that... alert the DB2 folks because killing the job will leave a long running job open on the DB2 side, as well. It does about the same thing as it does in SQL Server... it spins its wheels eating a lot of CPU to do nothing.


--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 #1326473
Posted Saturday, July 7, 2012 12:58 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
Jeff,

Thanks for all of the information.

I appreciate it.

Regards...


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 #1326475
Posted Sunday, July 8, 2012 9:32 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
Thanks Jeff.

I had checked what they were doing. It was actually 5 processes, all performing merge statements on the same table with different criteria on the WHERE Clause.

Their justification for doing this is that it will increase the bandwidth and speed up the load.

I have provided them with an abundance of CPU and memory.

I converted the OPENQUERY statements to OLE DB Source (AS400) and OLE DB Destination (SQL Server) and as expected the initial load to staging completed in a small fraction of the time it took to do an OPENQUERY.

Unfortunately we can't do that with the incremental on the large and they are doing MERGE OPENQUERY.

The problem is that most of the tables do not have Insert, Modified Date on them so they can't just select on the Datetime stamps.

On the smaller tables I converted their logic to a TRUNCATE & Fast Load OLE DB.

Edit: Typo on TRUNCATE


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 #1326576
Posted Sunday, July 8, 2012 9:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 1,913, Visits: 19,470
Welsh Corgi (7/8/2012)


The problem is that most of the tables do not have Insert, Modified Date on them so they can't just select on the Datetime stamps.

On the smaller tables I converted their logic to a TRUNCDATE & Fast Load OLE DB.



...had to do similar on a third pty database.

smaller tables...truncate and reload
and if IIRC ended up paying to get modified date columns on larger (excess 1m rows)..then there was still an on going debate about creating relevant index...hey ho


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1326579
Posted Tuesday, September 4, 2012 3:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 7, 2013 4:44 AM
Points: 1, Visits: 50
Would utilising sysinternals TCPView tool to identify the process and terminate it outside of MSSQL not help with these SPIDS? Often with past experience and SQL versions, the SQL process had lost its handle to the operating system PID when this kind of thing had occured.

1) Download TCPView http://technet.microsoft.com/en-us/sysinternals/bb897437
2) Run TCPView, locate the connection to linked server and close the connection.
Post #1353780
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse