Today I had a situation where I wanted to cancel an update to a table and several indexes and immediately restore back the database from a backup.
I issued a kill command to kill the updating SPID. I then issued a kill with statusonly and there was several tens of thousands of seconds roll back awaiting. I needed to restore the database in a hurry (therefore wasn't concerned with rolling back) - but you cant kill a SPID that's rolling back (as far as I know), or restore a database with outstanding connections.
I decided to stop the sql services, delete the database's mdf and ldf physical files, start the sql services - which shows the database in a suspect state. Then restored the database.
Is there a more controlled / sensible way to approach this situation?