Как удалить большие данные таблицы в SQL без журнала?

Ответ 1

• Если вы удаляете все строки в этой таблице, самым простым вариантом является таблица Truncate, что-то вроде

  TRUNCATE TABLE LargeTable
  GO
  

  Таблица обрезания просто очистит таблицу, вы не можете использовать предложение WHERE, чтобы ограничить удаляемые строки и не запускать триггеры.

• С другой стороны, если вы удаляете более 80-90 процентов данных, скажем, если у вас всего 11 миллионов строк, и вы хотите удалить 10 миллионов, другим способом будет вставить эти 1 миллион строк (записи, которые вы хотите сохранить) в другую промежуточную таблицу. Усекайте эту большую таблицу и вставьте назад эти 1 миллион строк.

• Или если разрешения/представления или другие объекты, которые имеют эту большую таблицу в качестве основной таблицы, не пострадают, если вы отбросить эту таблицу, вы можете получить эти относительно небольшое количество строк в другую таблицу, чтобы удалить эту таблицу и создать другую таблицу с той же схемой и импортировать эти строки обратно в эту таблицу ex-Large.

• Один из последних вариантов, о которых я могу думать, - это изменить вашу базу данных Recovery Mode to SIMPLE, а затем удалить строки в меньших партиях, используя цикл while, что-то вроде этого.

  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
  

и не забудьте полностью изменить режим восстановления, и я думаю, вам нужно сделать резервную копию, чтобы сделать ее полностью аффективной (режимы изменения или восстановления).

Ответ 2

@m-ali ответ прав, но также имейте в виду, что журналы могут сильно расти, если вы не совершаете транзакцию после каждого блока и не выполняете контрольную точку. Вот как я это сделаю и возьму эту статью http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes в качестве справочной информации с помощью тестов производительности и графиков:

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

Ответ 3

Вы также можете использовать GO +, сколько раз вы хотите выполнить тот же запрос.

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

Ответ 4

@Francisco Goldenstein, только незначительная коррекция. COMMIT следует использовать после установки переменной, иначе WHILE будет выполняться только один раз:

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

Ответ 5

Этот вариант M.Ali отлично работает для меня. Он удаляет некоторые, очищает журнал и повторяет. Я наблюдаю, как журнал растет, падает и начинается.

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

Ответ 6

Если вы готовы (и можете) реализовать разбиение на разделы, это эффективный метод удаления большого количества данных с небольшими затратами времени выполнения. Однако это не выгодно для одноразового упражнения.

Ответ 7

Вы можете удалить небольшие партии с помощью цикла while, примерно так:

DELETE TOP (10000)  LargeTable 
WHERE readTime < dateadd(MONTH,-7,GETDATE())
WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (10000)  LargeTable 
    WHERE readTime < dateadd(MONTH,-7,GETDATE())
END

Ответ 8

Мне удалось удалить 19 миллионов строк из моей таблицы из 21 миллиона строк за считанные минуты. Вот мой подход.

Если в этой таблице имеется автоматически увеличивающий первичный ключ, вы можете использовать этот первичный ключ.

• Получите минимальное значение первичного ключа большой таблицы, где readTime < DateAdd (месяц, -7, GETDATE()). (Добавьте индекс в readTime, если он еще не присутствует, этот индекс в любом случае будет удален вместе с таблицей на шаге 3.). Позволяет хранить его в переменной "min_primary"

• Вставьте все строки с первичным ключом > min_primary в промежуточную таблицу (таблица памяти, если количество строк не велико).

• Отбросьте большую таблицу.

• Восстановите таблицу. Скопируйте все строки из промежуточной таблицы в главную таблицу.

• Отпустите промежуточную таблицу.

Ответ 9

Другое использование:

SET ROWCOUNT 1000 -- Buffer

DECLARE @DATE AS DATETIME = dateadd(MONTH,-7,GETDATE())

DELETE LargeTable  WHERE readTime < @DATE
WHILE @@ROWCOUNT > 0
BEGIN
   DELETE LargeTable  WHERE readTime < @DATE
END
SET ROWCOUNT 0

Необязательно;

Если журнал транзакций включен, отключите журналы транзакций.

ALTER DATABASE dbname SET RECOVERY SIMPLE;