Jak usunąć duże dane tabeli w SQL bez dziennika?

Mam dużą tabelę danych. W tej tabeli jest 10 milionów rekordów.

Jaki jest najlepszy sposób na to zapytanie

   Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())

1. Jeśli usuwasz wszystkie wiersze w tej tabeli, najprostszą opcją jest obcięcie tabeli, coś w rodzaju

   TRUNCATE TABLE LargeTable
   GO

   Truncate table po prostu opróżni tabelę, nie możesz użyć klauzuli WHERE do ograniczenia liczby usuwanych wierszy i żadne wyzwalacze nie zostaną uruchomione.

2. Z drugiej strony, jeśli usuwasz więcej niż 80-90 procent danych, powiedzmy, jeśli masz łącznie 11 milionów wierszy i chcesz usunąć 10 milionów innym sposobem byłoby wstawienie tych 1 miliona wierszy (rekordów, które chcesz zachować ) do innej tabeli pomostowej. Obetnij tę dużą tabelę i wstaw z powrotem te 1 milion wierszy.

3. Lub jeśli upuszczenie tej tabeli nie ma wpływu na uprawnienia / widoki lub inne obiekty, które mają tę dużą tabelę jako tabelę bazową, możesz przenieść tę stosunkowo niewielką liczbę wierszy do innej tabeli i utworzyć inną tabelę z tym samym schematem i zaimportować je wiersze z powrotem do tej byłej dużej tabeli.

4. Ostatnią opcją, o której przychodzi mi do głowy, jest zmiana bazy danych, Recovery Mode to SIMPLEa następnie usuwanie wierszy w mniejszych partiach za pomocą pętli while, podobnej do tego.

   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

i nie zapomnij zmienić trybu odzyskiwania z powrotem na pełny i myślę, że musisz wykonać kopię zapasową, aby była w pełni skuteczna (tryby zmiany lub odzyskiwania).

@ m-ali odpowiedź jest prawidłowa, ale pamiętaj również, że dzienniki mogą się znacznie rozrosnąć, jeśli nie zatwierdzisz transakcji po każdej porcji i nie wykonasz punktu kontrolnego. Oto jak bym to zrobił i wziął ten artykuł http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes jako odniesienie, z testami wydajności i wykresami:

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

Możesz także użyć GO +, ile razy chcesz wykonać to samo zapytanie.

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, tylko drobna poprawka. COMMIT musi być użyty po ustawieniu zmiennej, w przeciwnym razie WHILE zostanie wykonane tylko raz:

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

Ta odmiana M.Ali działa dobrze dla mnie. Usuwa niektóre, czyści dziennik i powtarza. Patrzę, jak kłoda rośnie, spada i zaczynam od nowa.

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

Jeśli chcesz (i możesz) zaimplementować partycjonowanie, jest to skuteczna technika usuwania dużych ilości danych przy niewielkim nakładzie czasu wykonywania. Jednak nieopłacalne w przypadku jednorazowego ćwiczenia.

Udało mi się usunąć 19 milionów wierszy z mojej tabeli z 21 milionami wierszy w ciągu kilku minut . Oto moje podejście.

Jeśli masz automatycznie zwiększający się klucz podstawowy w tej tabeli, możesz użyć tego klucza podstawowego.

1. Uzyskaj minimalną wartość klucza podstawowego dużej tabeli, gdzie readTime <dateadd (MONTH, -7, GETDATE ()). (Dodaj indeks w readTime, jeśli jeszcze nie jest obecny, ten indeks i tak zostanie usunięty wraz z tabelą w kroku 3). Przechowujmy to w zmiennej „min_primary”

2. Wstaw wszystkie wiersze z kluczem podstawowym> min_primary do tabeli pomostowej (tabela pamięci, jeśli liczba wierszy nie jest duża).

3. Opuść duży stół.

4. Odtwórz tabelę. Skopiuj wszystkie wiersze z tabeli pomostowej do tabeli głównej.

5. Porzuć stół pomostowy.

Możesz usuwać małe partie za pomocą pętli while, na przykład:

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

Inne zastosowanie:

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

Opcjonalny;

Jeśli dziennik transakcji jest włączony, wyłącz dzienniki transakcji.

ALTER DATABASE dbname SET RECOVERY SIMPLE;

Krótsza składnia

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

Jeśli używasz programu SQL Server 2016 lub nowszego i jeśli w Twojej tabeli są utworzone partycje na podstawie kolumny, którą próbujesz usunąć (na przykład kolumna Timestamp), możesz użyć tego nowego polecenia do usunięcia danych według partycji.

TRUNCATE TABLE WITH (PARTYCJE ({|} [, ... n]))

Spowoduje to usunięcie danych tylko z wybranych partycji i powinno być najbardziej wydajnym sposobem usunięcia danych z części tabeli, ponieważ nie utworzy dzienników transakcji i zostanie wykonane tak samo szybko, jak zwykłe obcinanie, ale bez usuwania wszystkich danych ze stołu.

Wadą jest to, że jeśli twoja tabela nie jest skonfigurowana z partycją, musisz przejść do starej szkoły i usunąć dane zwykłym podejściem, a następnie ponownie utworzyć tabelę z partycjami, abyś mógł to zrobić w przyszłości, co zrobiłem. Dodałem tworzenie i usuwanie partycji do samej procedury wstawiania. Miałem tabelę z 500 milionami wierszy, więc była to jedyna opcja, aby skrócić czas usuwania.

Aby uzyskać więcej informacji, skorzystaj z poniższych linków: https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-2017

SQL Server 2016 Obetnij tabelę z partycjami

Poniżej opisano, co najpierw zrobiłem, aby usunąć dane, zanim mogłem odtworzyć tabelę z partycjami z wymaganymi danymi. To zapytanie będzie działało przez kilka dni w określonym przedziale czasu, aż do usunięcia danych.

:connect <<ServerName>>
use <<DatabaseName>>

SET NOCOUNT ON;
DECLARE @Deleted_Rows INT;
DECLARE @loopnum INT;
DECLARE @msg varchar(100);
DECLARE @FlagDate datetime;
SET @FlagDate =  getdate() - 31;
SET @Deleted_Rows = 1;
SET @loopnum = 1;

/*while (getdate() < convert(datetime,'2018-11-08 14:00:00.000',120))
BEGIN
    RAISERROR( 'WAIT for START' ,0,1) WITH NOWAIT   
    WAITFOR DELAY '00:10:00'
END*/
RAISERROR( 'STARTING PURGE' ,0,1) WITH NOWAIT   

WHILE (1=1)
BEGIN
    WHILE (@Deleted_Rows > 0 AND (datepart(hh, getdate() ) >= 12 AND datepart(hh, getdate() ) <= 20)) -- (getdate() < convert(datetime,'2018-11-08 19:00:00.000',120) )
      BEGIN
       -- Delete some small number of rows at a time
         DELETE TOP (500000)  dbo.<<table_name>>
         WHERE timestamp_column < convert(datetime, @FlagDate,102)
         SET @Deleted_Rows = @@ROWCOUNT;
         WAITFOR DELAY '00:00:01'
         select @msg = 'ROWCOUNT' + convert(varchar,@Deleted_Rows);
         set @loopnum = @loopnum + 1
         if @loopnum > 1000
             begin 
                 begin try
                        DBCC SHRINKFILE (N'<<databasename>>_log' , 0, TRUNCATEONLY)
                        RAISERROR( @msg ,0,1) WITH NOWAIT
                 end try
                 begin catch
                     RAISERROR( 'DBCC SHRINK' ,0,1) WITH NOWAIT  
                 end catch
                 set @loopnum = 1
             end
        END
WAITFOR DELAY '00:10:00'
END 
select getdate()

Jeśli powiem bez pętli, mogę użyć GOTOinstrukcji do usunięcia dużej ilości rekordów za pomocą serwera sql. exa.

 IsRepeat:
    DELETE TOP (10000)
    FROM <TableName>
    IF @@ROWCOUNT > 0
         GOTO IsRepeat

w ten sposób możesz usunąć dużą ilość danych przy mniejszym rozmiarze.

daj mi znać, jeśli potrzebujesz więcej informacji.