chrisolivierrsa (5/3/2013)
Hi,I am pretty sure if you stop and start the service, it stops the rollback operation, after that, you can drop the table removing the keep history button, since you are not on production it should not be a problem, unless there are other devs working on your dev environment, then you need to make sure everyone is fine with you restarting the service?
Kind regards
Simply restarting the service will not get around the problem of a long running or stuck rollback. Once initiated a rollback must complete, if you want your database back intact and available for normal use.
Recovery/Rollback Taking Longer Than Expected
There is a way to stop the service and then restart using a switch that will tell SQL Server not to go into recover transactions. However, that will leave any database with pending transactions in an inconsistent state, it's status in SSMS will show as 'RECOVERING' or 'SUSPECT', and it will be inaccessible.
Using the SQL Server Service Startup Options
http://msdn.microsoft.com/en-us/library/ms188396(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms190737(v=sql.105).aspx
Database States
http://msdn.microsoft.com/en-us/library/ms190442.aspx
At that point, you can drop the database, and then restore from backup. If you dig around on Google, you may find other options available, but all of them will involve some loss of data if you choose to keep the database without going to backup. I wish Microsoft could engineer a way to optimize the database recovery process.
To prevent this scenario from occuring again in the future, consider implementing a method where you break million row updates, inserts, deletes into "chunks".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho