Delete specific data but deleted data will not be there in transaction log

T.Zacks 3,986 Reputation points
2021-04-06T18:28:51.69+00:00

We know that when we issue Truncate then info not stored in transaction log file but when we issue delete from YYY table where ID >20 then removed info stored in transaction log file.

in my case i often deleting large volume of data from a table but not entire data from table. so i think if the same operation is going on after every few hour then there is high chance that my transaction log file size will increase lot. so please advise me how to remove data from table with delete statement but info will not be saved in transaction log file. any way exist? thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-04-06T21:41:27.83+00:00

    We know that when we issue Truncate then info not stored in transaction log file

    This is not correct. TRUNCATE TABLE is a fully logged statement. However, the amount of data that is logged is a lot less that with a DELETE. A DELETE logs every row. TRUNCATE TABLE logs only the extent allocations.

    If you are deleting a lot of data, and you are anxious about the transaction log, delete the data in batches, and make sure that any of these are true:

    1. The transaction log is backed up frequently enough, maybe every five minutes.
    2. The database is in simple recovery. This is rarely an alternative for a production database, but is perfectly OK for a development database.
    0 comments
  2. CarrinWu-MSFT 6,851 Reputation points
    2021-04-07T05:49:41.097+00:00

    Hi @T.Zacks ,

    Truncate command does not log each row deletion in the transaction log, but it does not means that will not store any records in transaction log. Under full or bulk logged recovery modal, all data modifications in SQL are logged. It's not possible to do any modification without logging it. Here have some suggetions may help you:

    1.If you are deleting more than 80-90 Percent of the data, say if you have total of 11 million rows and you want to delete 10 million another way would be to Insert these 1 million rows (records you want to keep) to another staging table. Truncate this large table and Insert back these 1 million rows.
    2.Or if permissions/views or other objects which has this large table as their underlying table doesn't get affected by dropping this table, you can get these relatively small amounts of the rows into another table, drop this table and create another table with same schema, and import these rows back into this ex-Large table.
    3.One last option I can think of is to change your database's Recovery Mode to SIMPLE and then delete rows in smaller batches using a while loop something like this:

     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)  LargeTable   
      WHERE readTime < dateadd(MONTH,-7,GETDATE())  

   SET @Deleted_Rows = @@ROWCOUNT;  
 END

    and don't forget to change the Recovery mode back to full and I think you have to take a backup to make it fully effective (the change or recovery modes). Please refer to this link and this blog to get more details

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.