Lesson Learned #17: Should I cancel or kill a session running a huge transaction?

First published on MSDN on Jan 20, 2017
Hello Everyone,



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.

• Recovery Method: 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.
  
  
  
  

• Best Practices
  
  
  
  • 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 URL
  
  
  • 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 .
  
  
  • Review this link about the best practices for batching process .
  
  
  
  

• Monitoring space of TempDB and Transaction Log:
  
  
  
  • During the execution of the huge transaction you could monitor the
    
    
    
    • TEMPDB database space using this TSQL
    
    
    • Transaction log space using this TSQL
    
    
    
    
  
  
  
  

Regards,