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

by

  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.

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

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

More Related Contents:

  1. How to split a comma-separated value to columns
  2. Using T-SQL, return nth delimited element from a string
  3. How to get a float result by dividing two integer values using T-SQL?
  4. Get first day of week in SQL Server
  5. SQL Server add auto increment primary key to existing table
  6. Why use the INCLUDE clause when creating an index?
  7. Confused about UPDLOCK, HOLDLOCK
  8. Retrieve column definition for stored procedure result set
  9. How to set a default value for an existing column
  10. Change Notification with Sql Server 2008
  11. Regular Expressions in SQL Server servers?
  12. Unique constraint on multiple columns
  13. An attempt was made to access a socket in a way forbidden by its access permissions
  14. How do I find a stored procedure containing ?
  15. How to find the port for MS SQL Server 2008?
  16. Should every User Table have a Clustered Index?
  17. How to set monday as first day of week in SQL Server
  18. Drop primary key using script in SQL Server database [duplicate]
  19. How to convert number of minutes to hh:mm format in TSQL?
  20. Changing SQL Server collation to case insensitive from case sensitive?
  21. Temporarily disable all foreign key constraints
  22. How to display two digits after decimal point in SQL Server
  23. The request failed or the service did not respond in a timely fashion?
  24. Merge multiple rows into a single row
  25. What are row, page and table locks? And when they are acquired?
  26. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  27. Error converting data types when importing from Excel to SQL Server 2008
  28. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?
  29. SQL Server 2008 Spatial: find a point in polygon
  30. Implementing one-to-zero-or-one relation in SQL Server

Leave a Comment