question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked CarrinWu-MSFT commented

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

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TZacks-2728, we have not get a reply from you. Did the answers could help you? If there has an answer helped, do "Accept Answer". If it is not work, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ErlandSommarskog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

One last option I can think of is to change your database's Recovery Mode to SIMPLE

I strongly recommend against this if this is a production database. There could be an undetected corruption in the database, and to recover you need to restore a backup from before this purging operation. But that will not work out if the log chain has been broken.

0 Votes 0 ·