First published on MSDN on Jan 20, 2017
We are receiving multiple service requests where our customers cancelled or kill a session that was running a huge transaction (delete/insert/update).
After this cancelation, they were waiting for the same time that the huge transaction took to be able to do any action with the tables involved in this transaction.
This situation is normal in any SQL Server edition, but in Azure SQL Database depending on the database tier that you have, this time may be more depending on the resources assigned to it.
For this reason, I would like to share with you a how the recovery method is implemented on Azure, the best practices to prevent this such situation and additional information to monitor the TEMPDB and Transaction Log space.
In Azure SQL Database all databases have the recovery full mode activate by default and there is not possible to change it:
All operations will be logged in the transaction log.
We have an automatic internal process that runs a backup of this transaction log and reduce the transaction log size.
If you cancel the query or kill the process the transaction need to rollback all operations done, so, this process may take the same time that the execution took until this cancelation/kill.
My suggestion is to wait until this process finished.
To stop/restart the database is not an option, because when the database is starting up again, Azure SQL Database needs to rollback or re-apply the transactions pending. Therefore, you may wait the same time if you kill or cancel the transaction, even more. Please, review this very good video about Microsoft SQL Server Transaction Log Internals
Before performing a huge transaction, is to scale up to higher database tier, for example, Premium (if your database is running on a standard edition ) where the resources are better and once the huge transaction finishes, scale down to previous database tier