Czym różnią się te dwa wycofania programu SQL Server?

W SQL Server 2008 R2 czym różnią się te dwa wycofania:

1. Uruchom ALTERinstrukcję przez kilka minut, a następnie naciśnij „Anuluj wykonywanie”. Całkowite cofnięcie zajmuje kilka minut.

2. Uruchom tę samą ALTERinstrukcję, ale upewnij się, że LDFplik nie jest wystarczająco duży, aby mógł zakończyć się powodzeniem. Po osiągnięciu LDFlimitu i braku zezwolenia na „autogrowth” wykonywanie zapytania zatrzymuje się natychmiast (lub następuje wycofanie) z komunikatem o błędzie:

The statement has been terminated.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'SampleDB' is full. 
To find out why space in the log cannot be reused, see the 
log_reuse_wait_desc column in sys.databases

Czym różnią się te dwa punkty w następujących punktach?

1. Dlaczego drugie „wycofanie” jest natychmiastowe? Nie jestem do końca pewien, czy można to nazwać wycofaniem. Domyślam się, że dziennik transakcji jest zapisywany w miarę postępu wykonywania, a gdy zorientuje się, że nie ma wystarczającej ilości miejsca do pełnego wykonania zadania, po prostu zatrzymuje się z komunikatem „koniec”, bez zatwierdzania.

2. Co się stanie, gdy pierwsze wycofanie zajmie tyle czasu (czy wycofanie jest jednowątkowe)?
   2.1 Czy program SQL Server cofnie się i cofnie wpisy wprowadzone w LDFpliku?
   2.2 Rozmiar LDFpliku zmniejsza się na końcu przywracania (od DBCC SQLPERF(LOGSPACE))

3. Jedno dodatkowe pytanie: w drugim scenariuszu SQL Server LDFdość szybko zaczyna pobierać pliki. W moim przypadku wzrósł z 18% do 90% w ciągu pierwszych kilku minut (<4 min). Ale kiedy osiągnęło 99%, pozostało tam przez kolejne 8 minut, przy zmiennym zużyciu od 99,1% do 99,8%. Zwiększa się (99,8%) i zmniejsza (99,2%) i ponownie rośnie (99,7%) i spada (99,5%) kilka razy przed zgłoszeniem błędu. Co dzieje się za kulisami?

Doceniamy wszelkie linki MSDN, które mogłyby pomóc w wyjaśnieniu tego bardziej.

Zgodnie z sugestią Ali Razeghi dodaję perfmon: Disk Bytes/sec

Scenariusz 1:

Scenariusz 2:

Jak wskazano powyżej, po przeprowadzeniu większej liczby testów doszedłem do obliczonych wniosków. Podsumowałem je wszystkie w blogu tutaj , ale skopiuję trochę treści do tego postu dla potomności.

Domysł (na podstawie niektórych testów)

Na razie nie mam jasnego wyjaśnienia, dlaczego tak jest. Ale poniżej są moje szacunki na podstawie artefaktów zebranych podczas testów.

Wycofywanie ma miejsce w obu scenariuszach. Jeden jest jawnym wycofaniem (użytkownik naciska przycisk Anuluj), drugi jest niejawny (serwer Sql podejmuje tę decyzję wewnętrznie).

W obu scenariuszach ruch do pliku dziennika jest spójny. Zobacz zdjęcia poniżej:

Scenariusz 1:

Scenariusz 2:

• Jednym artefaktem, który wzmocnił ten tok myślenia, jest uchwycenie śladu sql podczas obu scenariuszy.

  • Scenariusz 1 jest oczywisty, gdy po naciśnięciu przycisku „Anuluj” wycofuje się.
  • W scenariuszu 2 komunikat o błędzie jest wyświetlany po niejawnym wykonaniu „wycofania”. W Sql Trace pojawia się komunikat o błędzie „Dziennik transakcji dla bazy danych„ SampleDB ”jest pełny” na długo przed wyświetleniem komunikatu na ekranie. Domyślam się, że wycofywanie ma miejsce w obu scenariuszach, ale komunikat o błędzie to Scenariusz 2 jest wyświetlany po pomyślnym i całkowitym wykonaniu wycofania.
• Scenariusz 2 wydaje się trwać dłużej, ponieważ postępuje znacznie dalej, więc wycofywanie trwa dłużej.

Niewyjaśnione zachowanie:

• Dlaczego użycie pliku dziennika jest tak różne?
  • Zwiększa się do 90%, następnie do 85%, a następnie do 99% i unosi się tam przez długi czas. Widzę to kilka razy w górę i w dół: 99,2%, 99,8%, 99,1%, 99,7%. Dlaczego to się dzieje?
  • Jednym z możliwych wyjaśnień jest to, że może istnieć proces w tle (coś w rodzaju Log Flush), który czyści plik dziennika co kilka minut. Za każdym razem, gdy się uruchamia, niektóre wpisy są usuwane, co zapewnia więcej wolnego miejsca.

Wszelkie pomysły, które pomogą lepiej wyjaśnić to zachowanie, są mile widziane.

Próbowałem następującego eksperymentu i uzyskałem podobne wyniki. W obu przypadkach fn_dblog () pokazuje wycofywanie i wydaje się, że dzieje się to szybciej w Scenariuszu 2 niż w Scenariuszu 1.

Nawiasem mówiąc, umieściłem zarówno MDF, jak i LDF na tym samym pojedynczym zewnętrznym dysku (USB 2.0).

Mój wstępny wniosek jest taki, że w tym przypadku nie ma różnicy w działaniu wycofania i prawdopodobnie każda widoczna różnica prędkości jest związana z podsystemem I / O. To tylko moja robocza hipoteza.

Scenariusz 1:

• Utwórz bazę danych z plikiem dziennika, który zaczyna się od 1 MB, rośnie w 4 MB porcjach i ma maksymalny rozmiar 100 MB.
• Otwórz jawną transakcję, uruchom ją na 10 sekund, a następnie ręcznie anuluj w ramach SSMS
• Spójrz na liczbę fn_dblog () i rozmiar rezerwy dziennika i sprawdź DBCC SQLPERF (LOGSPACE)

Scenariusz 2:

• Utwórz bazę danych z plikiem dziennika, który zaczyna się od 1 MB, rośnie w 4 MB porcjach i ma maksymalny rozmiar 100 MB.
• Otwórz jawną transakcję, uruchom ją, aż wyświetli się komunikat o błędzie
• Spójrz na liczbę fn_dblog () i rozmiar rezerwy dziennika i sprawdź DBCC SQLPERF (LOGSPACE)

Wyniki Monitora wydajności:

Scenariusz 1:

Scenariusz 2:

Kod:

USE [master];
UDAĆ SIĘ

JEŚLI DATABASEPROPERTYEX (N'SampleDB ', N'Version')> 0
ZACZYNAĆ
    ALTER DATABASE [SampleDB] SET SINGLE_USER
        Z NATYCHMIASTOWYMI ROLKAMI;
    DROP DATABASE [SampleDB];
KONIEC;
UDAĆ SIĘ

UTWÓRZ BAZA DANYCH [SampleDB] NA PODSTAWIE 
( 
      NAME = N'SampleDB '
    , FILENAME = N'E: \ data \ SampleDB.mdf ' 
    , ROZMIAR = 3 MB 
    , FILEGROWTH = 1 MB 
)
ZALOGOWAĆ SIĘ 
( 
      NAME = N'SampleDB_log '
    , FILENAME = N'E: \ data \ SampleDB_log.ldf '
    , ROZMIAR = 1 MB 
    , MAXSIZE = 100 MB 
    , FILEGROWTH = 4 MB 
);
UDAĆ SIĘ

UŻYJ [SampleDB];
UDAĆ SIĘ

- Dodaj stolik
UTWÓRZ TABELĘ dbo.test
(
    c1 CHAR (8000) NOT NULL DEFAULT DEPLICATE („a”, 8000)
) W dniu [PODSTAWOWY];
UDAĆ SIĘ

- Upewnij się, że nie jesteśmy pseudo prostym modelem odzyskiwania
KOPIA ZAPASOWA SampleDB
DO DYSKU = „NUL”;
UDAĆ SIĘ

- Utwórz kopię zapasową pliku dziennika
KOPIA ZAPASOWA SampleDB
DO DYSKU = „NUL”;
UDAĆ SIĘ

- Sprawdź używane miejsce na dziennik
DBCC SQLPERF (LOGSPACE);
UDAĆ SIĘ

- Ile rekordów jest widocznych za pomocą fn_dblog ()?
WYBIERZ * Z fn_dblog (NULL, NULL); - Około 9 w moim przypadku

/ **********************************
             SCENARIUSZ 1
********************************** /
- Otwórz nową transakcję, a następnie wycofaj ją
ROZPOCZNIJ TRANSAKCJĘ

    WPROWADŹ DO dbo.test WARTOŚCI DOMYŚLNE;
    GO 10000 - Let jest uruchamiany przez 10 sekund, a następnie naciśnij przycisk Anuluj w oknie zapytania SSMS

    - Anuluj transakcję
    - Zakończenie zajmie kilka sekund


- Nie musisz cofać transakcji, ponieważ anulowanie już to zrobiło.
-- Po prostu spróbuj. Otrzymasz ten błąd
- Msg 3903, poziom 16, stan 1, wiersz 1
- Żądanie TRANSAKCJI ROLLBACK nie ma odpowiadającej POCZĄTKU TRANSAKCJI.
TRANSAKCJA ROLKOWA;

- Jaka jest używana przestrzeń dziennika? Powyżej 100%.
DBCC SQLPERF (LOGSPACE);
UDAĆ SIĘ

- Ile rekordów jest widocznych za pomocą fn_dblog ()?
WYBIERZ * 
FN_dblog (NULL, NULL); - Około 91 926 w moim przypadku

- Całkowita rezerwa dziennika pokazana przez fn_dblog ()?
SELECT SUM ([Log Log]) AS [Total Log Reserve]
FN_dblog (NULL, NULL); - Około 88,72 MB


/ **********************************
             SCENARIUSZ 2
********************************** /
- Zdmuchnij DB i zacznij od nowa
USE [master];
UDAĆ SIĘ

JEŚLI DATABASEPROPERTYEX (N'SampleDB ', N'Version')> 0
ZACZYNAĆ
    ALTER DATABASE [SampleDB] SET SINGLE_USER
        Z NATYCHMIASTOWYMI ROLKAMI;
    DROP DATABASE [SampleDB];
KONIEC;
UDAĆ SIĘ

UTWÓRZ BAZA DANYCH [SampleDB] NA PODSTAWIE 
( 
      NAME = N'SampleDB '
    , FILENAME = N'E: \ data \ SampleDB.mdf ' 
    , ROZMIAR = 3 MB 
    , FILEGROWTH = 1 MB 
)
ZALOGOWAĆ SIĘ 
( 
      NAME = N'SampleDB_log '
    , FILENAME = N'E: \ data \ SampleDB_log.ldf '
    , ROZMIAR = 1 MB 
    , MAXSIZE = 100 MB 
    , FILEGROWTH = 4 MB 
);
UDAĆ SIĘ

UŻYJ [SampleDB];
UDAĆ SIĘ

- Dodaj stolik
UTWÓRZ TABELĘ dbo.test
(
    c1 CHAR (8000) NOT NULL DEFAULT DEPLICATE („a”, 8000)
) W dniu [PODSTAWOWY];
UDAĆ SIĘ

- Upewnij się, że nie jesteśmy pseudo prostym modelem odzyskiwania
KOPIA ZAPASOWA SampleDB
DO DYSKU = „NUL”;
UDAĆ SIĘ

- Utwórz kopię zapasową pliku dziennika
KOPIA ZAPASOWA SampleDB
DO DYSKU = „NUL”;
UDAĆ SIĘ

- Teraz wysadźmy plik dziennika w ramach naszej transakcji
ROZPOCZNIJ TRANSAKCJĘ
    WPROWADŹ DO dbo.test WARTOŚCI DOMYŚLNE;
    GO 10000

- Cofanie nigdy nie działa. Spróbuj. Otrzymasz błąd.
- Msg 3903, poziom 16, stan 1, wiersz 1
- Żądanie TRANSAKCJI ROLLBACK nie ma odpowiadającej POCZĄTKU TRANSAKCJI.
TRANSAKCJA ROLKOWA;

- Czy plik dziennika jest w 100% pełny? 
DBCC SQLPERF (LOGSPACE);

- Ile rekordów jest widocznych za pomocą fn_dblog ()?
WYBIERZ * 
FN_dblog (NULL, NULL); - Około 91 926 w moim przypadku
UDAĆ SIĘ

- Całkowita rezerwa dziennika pokazana przez fn_dblog ()?
SELECT SUM ([Log Log]) AS [Total Log Reserve]
FN_dblog (NULL, NULL); - 88,72 MB
UDAĆ SIĘ