There should be a way to delete data in SQL Server without overloading the log

Question

I need to delete a bunch of data and not have disk space so that the log continues to grow. After thinking about this question, it seems that there is no way around this, but I thought that I would definitely ask; It’s hard for me to believe that something so simple is impossible.

I tried a loop, removing pieces and causing file compression in the log after each iteration. SQL Server simply ignores the shrinkfile command. The same thing happened with the backup log (then deleting the backup file afterwards). The same thing - the magazine just continues to grow. The recovery model in the database on which I am trying to do this is simple - I thought it would simplify, but it is not.

Answer 1

Do the deletion in chunks, but instead of trying to reduce the time between logs, back up the logs between chunks (that is, if you are in full recovery)

The problem is that the journal is full and therefore must grow. If it is full, an attempt to reduce it is useless; there is no free space in the log for accessing the OS. Instead, make the space inside the file reusable.

Since the database is in simple recovery, start the deletion in pieces with the CHECKPOINT command between each fragment. You cannot backup logs in easy recovery mode

, ( ). . , .

(SQL 2005 . SQL 2000 TOP SET ROWCOUNT)

DECLARE @Done BIT
SET @Done = 0
WHILE @Done = 0
  BEGIN
    DELETE TOP (20000) -- reduce if log still growing
      FROM SomeTable WHERE SomeColumn = SomeValue 
    IF @@ROWCOUNT = 0 
      SET @Done = 1
    CHECKPOINT -- marks log space reusable in simple recovery
  END

, - http://www.sqlservercentral.com/articles/64582/

Answer 2

, , , , :

• " " ( )

• temp

, , , , .

, ( ) , , , " " bcp, bcp.

, , , , .: -)

Answer 3

"", , .