Deleting large data in azure SQL table will increased Database data storage

Question

I'm trying to delete large data from Azure SQL table using delete command, but when deleting data then database data storage will be increased. How could I stop that or is there any option in Azure to minimize transaction logs?

I don't want to use truncate because I don't need to delete all data in the table.

 delete top(10000)
 from Crumbs
 where CrumbId <= @maxId and 
       TenantId =@tenantId

Answer 1

Normally you would change the recovery mode of the database to SIMPLE.

It's a little bit different for Azure Sql Databases: In this case the SQL Database Engine performs a transaction log backup every 5-10 minutes - It's also shrinked automatically.

If you want full control over your database instance the option would be to run a SQL Server Managed Instance or a VM with SQL Server installed.

Answer 2

Please reference this blog:How to delete large data of table in SQL without log?.

If you don't want to use truncate, M.Ali gives you an another option:

1. change your database's Recovery Mode to SIMPLE.
2. delete rows in smaller batches using a while loop.

Example:

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;


WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time
     DELETE TOP (10000)  Crumbs
     WHERE CrumbId < <= @maxId and
           TenantId =@tenantId
  SET @Deleted_Rows = @@ROWCOUNT;
END

Maybe you can try this option.

Hope this helps.

Answer 3

You have a few options depending on your table and the records you want to delete:

• What's the proportion of your data you want to keep vs. delete? If you're intent is to delete much more data than you want to keep consider saving the data to a new table and drop the old one.
• If your table is partitioned then you can apply the above approach on a partition basis and switch in/out partitions as needed.
• Your db size is comprised of the log and the data files, if you delete non-contiguous chunks of data your indexes are going to be fragmented, so you'll have a lot of space lost in between (page splits, etc.). Once your done with your deletes run either rebuild or reorganize on the index(s) to clean up this space.
• Once you've rebuilt/reorganized, force clean up the log and the pre-allocated data space using DBCC SHRINKDATABASE. Note that you might suffer a minor perf impact upon restarting your loads since the db and log files need to grow again.