I ran a delete statement with wrong WHERE condition (which deletes all records from table instead of just 10 records) but during execution I found it and CANCELLED the execution in Management Studio, which is still in query execution cancellation process (its a big table).
As far as I know if my cancellation completes successfully I don't lose any data, is my understanding correct? if not can anyone please tell me is there a way to recover deleted data from transaction log. The database is in simple recovery model.
Best Answer
Assuming it is a single statement, the operation will either succeed or fail as a whole - there is no way some of the rows will get deleted and some will remain. (And if the delete succeeds, as @Shanky suggested above, in simple recovery there is not much you will be able to do to recover the data aside from reverting to a prior backup.)
That said, do not get impatient and try to kill the user forcefully, or take the database offline, or shut down the service, or disconnect the network cable, or disconnect the power cord. You will need to wait for the rollback to complete - even if it's blocking other users - to be sure your data will be intact. Note that a rollback operation can take longer than the initial operation because a rollback is generally single-threaded, but the delete might have gone parallel.
In the future, you should issue a
BEGIN TRANSACTION;prior to running ad hoc / uncontrolled / cowboy DML against a production or otherwise critical system. This way no matter when you realize your mistake, you can wait for the operation to complete and then issue aROLLBACK TRANSACTION;instead of madly and repeatedly slamming the stop button in SSMS. This will save you from deleting data that you can't easily recover, but there's a caveat the other way - if you did the right thing, you need to remember toCOMMIT TRANSACTION;, since blocking the entire table while you're on lunch, home for the evening or on vacation is probably also an employment-terminating event.