May 28, 2015 at 3:19 am
Hi,
Our jobs have been failing for a few days now because of a couple processes being left over on 2 databases (msdb + a user db). We can backup up all other databases besides these 2 due to serialisation limitations. I issued the kill commands yesterday morning but the states are still "KILLED/ROLLBACK" and 99% complete according to the not so useful KILL 127 WITH STATUSONLY (it said 99% immediately).
session_idSTATUSblocked bywait_typeWait Time (in Sec)Elapsed Time (in Sec)statement_textcommandlast_request_end_timelogin_timeopen_transaction_count
127suspended0CMEMTHREAD131110.18132453.962BACKUP DATABASE xxx WITH CHECKSUM, COMPRESSIONKILLED/ROLLBACK2015-05-26 21:302015-05-26 21:300
96suspended0CMEMTHREAD1002727.5631002837.845BACKUP DATABASE yyy WITH CHECKSUM, COMPRESSIONKILLED/ROLLBACK2015-05-16 19:302015-05-16 19:300
I don't get why a backup task would need to rollback. The host_process_ids do not exist on the server.
Any ways to overcome this other than reboot/change to SINGLE_USER mode? Surely there's some kill immediate command that can be used for tasks like a backup.
Users aren't affected by these rogues.
May 28, 2015 at 12:19 pm
dm_tran_locks output:
request_session_idresource_typeresource_subtyperequest_moderequest_typerequest_statusrequest_owner_type
127DATABASEBULKOP_BACKUP_LOGNULLLOCKGRANTTRANSACTION
127DATABASEBULKOP_BACKUP_DBULOCKGRANTTRANSACTION
127DATABASESLOCKGRANTSHARED_TRANSACTION_WORKSPACE
Apparently it's a known backup bug: http://stackoverflow.com/questions/12216866/cant-kill-spid-transaction-rollback-in-progress
but unable to find the hotfix & restarting sucks with 15 apps on the cluster :f
May 29, 2015 at 6:53 am
Are you already on the latest Service Pack?
May 29, 2015 at 7:23 am
No, 2008r2 SP2 CU 11 (of 13).
May 29, 2015 at 2:47 pm
Out of sheer curiosity; is the backup being done by a 3rd party tool like BackPro, SQSafe, or Lightspeed, etc?
If so, you'll need to kill the actual thread using something like ProcessExplorer, then it will complete the session
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
May 29, 2015 at 3:43 pm
It was done by Ola's job/script. The pid, as far as SQL Server is concerned, doesn't exist on the server any more (in task manager)...only the spid is there
May 29, 2015 at 3:47 pm
I have seen this occur once in a while. Others may frown on this but based on this statement from your original post, KILL 127 WITH STATUSONLY (it said 99% immediately), you may need to restart SQL Server. Weekend is here, may be a good time to try.
May 29, 2015 at 3:54 pm
We were thinking of doing it Tonight but decided it was too risky as the people who manage the more critical apps of the instance were on holiday Today. Decided to delay until Monday night.
If you're wondering what that thing on the floor is it's my towel 8=D
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply