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 (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5383 Visits: 1173
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 (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)

Group: General Forum Members
Points: 290740 Visits: 33911
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 (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5383 Visits: 1173
Bouncing the server put the database in recovery state and can’t be accessible.
Sue_H
Sue_H
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46525 Visits: 12200
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 Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 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
SSChasing Mays
SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)

Group: General Forum Members
Points: 605 Visits: 249
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 (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)SSC Guru (290K reputation)

Group: General Forum Members
Points: 290740 Visits: 33911
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 (721K reputation)SSC Guru (721K reputation)SSC Guru (721K reputation)SSC Guru (721K reputation)SSC Guru (721K reputation)SSC Guru (721K reputation)SSC Guru (721K reputation)SSC Guru (721K reputation)

Group: General Forum Members
Points: 721047 Visits: 48474
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
SSChasing Mays
SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)

Group: General Forum Members
Points: 605 Visits: 249
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