My disk is running out of space and as a result I decided to free some space by deleting old data. I tried to delete 100,000 by 100,000 as there are 240 million records to be deleted. But I am unable to delete them at once and shrinking the database doesn't free much space. This is the error im getting at times.

The transaction log for database 'TEST_ARCHIVE' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

 How can I overcome this situation and delete all the old records? Plea

What does the below command return?

DBCC LOGINFO (dbid)

Looks like you need to backup log files and then issue a shrink file

     

Looks like transaction log is not able to accommodate 100,000 delete in 1 shot. You can reduce the delete in small chunks. You can use below script:

SET NOCOUNT ON;

DECLARE @r INT;
 SET @r = 1;

WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (10000) -- this will change
    dbo.SalesOrderDetailEnlarged
    WHERE ProductID IN (712, 870, 873);

  SET @r = @@ROWCOUNT;
   COMMIT TRANSACTION;

    -- BACKUP LOG ... -- if full
END

After every successful delete take tlog backup to free up space inside LDF file.  See script above and add backup log command.
If you are deleting all row in a table you can use truncate command.

• Proposed as answer by Michelle LiMicrosoft contingent staff, Moderator 17 hours 18 minutes ago

In order to delete the SQL Server need to write the information in the log file, and you do not have the place for those rows in the log file. You might succeeded to delete less in each time -> next backup the log file each time -> next shrink the log file... but this is not the way that I would chose.

Best option is probably to add another disc (a simple disk do not cost a lot), move the log file there permanently. It will increase the database work as well (it is highly recommend not to put the log file on the same disk as the data file in most cases).

If you can't add new disk permanently then add one temporary. Then add file to the database in this disk -> create new table in this disk -> move all the data that you do not want to delete to the new table -> truncate the current table -> bring back the data om the new table -> drop the new table and the new file to release the temporary disk.

Are you using full mode or simple recovery mode ?

* in full mode you have to backup the log file if you want to shrink it

This is what the LOGINFO command returns:

How to Stop Growing Log File Too Big

The transaction log for database 'TEST_ARCHIVE' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The message is plain and simple there is not enough space on drive where log file is present to accommodate information related to delete of 240 million records and as good practice even if you have space you should not delete all records in one go.

The first thing I would check is autogrowth of log file for database test_archive it should NOT BE IN PERCENTAGE. If it is so change it to MB if you want to take starting value take 200-300 MB

Second thing is when you delete rows every row deletion would be logged in full recovery model( you did not tell us recovery model so I assumed) so it is bound to increase log file. You must delete in batches I cannot tell you exact count but start with 30K to 40K in one shot. After you are done with delete operation perform Transaction log backup.

If the recovery model is simple again delete in batches and add manual checkpoint command in the delete script. Perhaps if you run checkpoint command twice it would be better.

NO NEED TO CHANGE RECOVERY MODEL IN ANY CASE

Ok, now issue backup log file, see at the bottom status=0, then you will  be able to shrink file and  delete the data