How to delete large data of table in SQL without log?

Question

1. If you are Deleting All the rows in that table the simplest option is to Truncate table, something like

    TRUNCATE TABLE LargeTable
    GO
   

Truncate table will simply empty the table, you cannot use WHERE clause to limit the rows being deleted and no triggers will be fired.

2. On the other hand 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.

3. 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.

4. 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).

@m-ali answer is right but also keep in mind that logs could grow a lot if you don't commit the transaction after each chunk and perform a checkpoint. This is how I would do it and take this article http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes as reference, with performance tests and graphs:

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;


WHILE (@Deleted_Rows > 0)
  BEGIN

   BEGIN TRANSACTION

   -- Delete some small number of rows at a time
     DELETE TOP (10000)  LargeTable 
     WHERE readTime < dateadd(MONTH,-7,GETDATE())

     SET @Deleted_Rows = @@ROWCOUNT;

   COMMIT TRANSACTION
   CHECKPOINT -- for simple recovery model
END

You can also use GO + how many times you want to execute the same query.

DELETE TOP (10000)  [TARGETDATABASE].[SCHEMA].[TARGETTABLE] 
WHERE readTime < dateadd(MONTH,-1,GETDATE());
-- how many times you want the query to repeat
GO 100

@Francisco Goldenstein, just a minor correction. The COMMIT must be used after you set the variable, otherwise the WHILE will be executed just once:

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
BEGIN
    BEGIN TRANSACTION

    -- Delete some small number of rows at a time
    DELETE TOP (10000)  LargeTable 
    WHERE readTime < dateadd(MONTH,-7,GETDATE())

    SET @Deleted_Rows = @@ROWCOUNT;

    COMMIT TRANSACTION
    CHECKPOINT -- for simple recovery model

END

This variation of M.Ali's is working fine for me. It deletes some, clears the log and repeats. I'm watching the log grow, drop and start over.

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time
    delete top (100000) from InstallLog where DateTime between '2014-12-01' and '2015-02-01'
    SET @Deleted_Rows = @@ROWCOUNT;
    dbcc shrinkfile (MobiControlDB_log,0,truncateonly);
END

If you are willing (and able) to implement partitioning, that is an effective technique for removing large quantities of data with little run-time overhead. Not cost-effective for a once-off exercise, though.