question

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.

Hi @TZacks-2728,

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.