February 8, 2018 at 4:29 pm
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
February 8, 2018 at 6:28 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2018 at 6:34 pm
Bouncing the server put the database in recovery state and can’t be accessible.
February 8, 2018 at 7:02 pm
SQL_Surfer - Thursday, February 8, 2018 6:34 PMBouncing 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
February 8, 2018 at 8:14 pm
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.
February 8, 2018 at 11:06 pm
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
February 9, 2018 at 8:12 am
SQL_Surfer - Thursday, February 8, 2018 6:34 PMBouncing 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2018 at 8:38 am
Alexander Zhang - Thursday, February 8, 2018 11:06 PMWhenever 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_complete real Percentage 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
February 9, 2018 at 10:31 am
GilaMonster - Friday, February 9, 2018 8:38 AMAlexander Zhang - Thursday, February 8, 2018 11:06 PMWhenever 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_complete real Percentage 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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply