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.