[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1743
  • Last Modified:

spid stuck in ROLLBACK

I killed a spid earlier - it's command is currently KILLED/ROLLBACK and has been for a very long time.  (couple hours, plus)
the cputime and disk i/o are very minimal and haven't changed at all, subsequent attempts to kill the spid return this:

spid 1: transaction rollback in progress.  Estimated rollback completion: 100%.  Estimated time remaining: 0 seconds.

but, status is RUNNABLE
and certain retrievals are actually blocked by this spid
 
very important, please advise as soon as possible
0
dbaSQL
Asked:
dbaSQL
  • 2
  • 2
1 Solution
 
DonKronosCommented:
Only way I have been able to clear this is to stop and start SQL Server. :(
0
 
dbaSQLAuthor Commented:
so you've seen this before then?  do you know what causes it?  a restart isn't feasible right now at all.
0
 
DonKronosCommented:
I've had two cases when it happened.

1.) A stored procedure was stuck in an infinite loop.  Killing the spid stopped the infinite loop but the process stayed around until I did a stop\start.

2.)  I was copying DTS packages from one msdb database to another msdb database on another server.  The copy was being done via a trigger that had a misspelling in the table name.  The trigger would not end and both msdb databases were hung until I stopped/started SQL Server.

These were both stupid mistakes I made while trying to do something "quick". :(   Fortunately the number of users at the site was small so no real problem with the stop/start.
0
 
ptjcbCommented:
I have also seen this with xp_ procedures (xp_cmdshell, etc). These are external stored procedures that run in the SQL Server address space, but SQL Server does not have any knowledge or control of them.

DonKronos is right - you will have to stop/start the service to clear this spid out.
0
 
dbaSQLAuthor Commented:
well, unfortunately i can't do that right now.  but thank you both for your advice
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now