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


Kill long running transactions


Kill long running transactions

Author
Message
SQL_Surfer
SQL_Surfer
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6872 Visits: 1191
So, I found a long running transaction that ran for 7 + hours. I killed the spid but it’s rolling back for more than 20 hours now. Is there any other way to Kill this? I’m not worried about data loss
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)

Group: General Forum Members
Points: 347306 Visits: 34118
You can bounce the server. Sometimes that works. Other times it stops the rollback which then has to start up again, from scratch.

Look to see if there is blocking on the rollback.


----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQL_Surfer
SQL_Surfer
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6872 Visits: 1191
Bouncing the server put the database in recovery state and can’t be accessible.
Sue_H
Sue_H
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66836 Visits: 13899
SQL_Surfer - Thursday, February 8, 2018 6:34 PM
Bouncing the server put the database in recovery state and can’t be accessible.


That's usually what happens when the rollback is starting back up again. You should see messages about it in the Error log, percentage completed, phases, that type of thing.

Sue



Ivan R.
Ivan R.
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 366
agree a service 1bounce sometimes does work; when it does not the DB goes to recovery mode and you wait again, but usually this is no longer the 20 hours elapsed you mention.
Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1071 Visits: 264
If you kill active transaction, MSSQL does need rollback to guarantee the data consistence. I think it's lesson. Whenever we kill a session, first of all, we should be very carefully check the complete percentage in sys.dm_exec_requestes and evaluate the rollback time.. Sometimes, maybe it just needs 2 more minutes to finish, but 20 hours to rollback.

GASQL.com - Focus on Database and Cloud
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)

Group: General Forum Members
Points: 347306 Visits: 34118
SQL_Surfer - Thursday, February 8, 2018 6:34 PM
Bouncing the server put the database in recovery state and can’t be accessible.


yep. It's rolling back.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)SSC Guru (887K reputation)

Group: General Forum Members
Points: 887172 Visits: 48655
Alexander Zhang - Thursday, February 8, 2018 11:06 PM
Whenever we kill a session, first of all, we should be very carefully check the complete percentage in sys.dm_exec_requestes and evaluate the rollback time..

The complete percentage is only for backups and operations like that. Normal queries don't get a complete percentage, and hence the time to complete can't be evaluated.

percent_completerealPercentage of work completed for the following commands:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION


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


Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1071 Visits: 264
GilaMonster - Friday, February 9, 2018 8:38 AM
Alexander Zhang - Thursday, February 8, 2018 11:06 PM
Whenever we kill a session, first of all, we should be very carefully check the complete percentage in sys.dm_exec_requestes and evaluate the rollback time..

The complete percentage is only for backups and operations like that. Normal queries don't get a complete percentage, and hence the time to complete can't be evaluated.

percent_completerealPercentage of work completed for the following commands:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

Yes, you are correct. The complete_percent has its big limitation. And I agree it's very hard to evaluate the rollback time. My point is, we need to do it if possible. For example, if Insert/Update operations with CreateTime/InsertTime/LastUpdateTime info, we can check the row count with NOLOCK. I agree, it's probably not so helpful in many cases. But in some cases, it might prevent us from doing some bad decisions.


GASQL.com - Focus on Database and Cloud
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