Miejsce na dysku pełne podczas wkładania, co się dzieje?

17

Dzisiaj odkryłem, że dysk twardy, w którym przechowywane są moje bazy danych, był pełny. Stało się to wcześniej, zwykle przyczyna jest dość oczywista. Zwykle występuje błędne zapytanie, które powoduje, że ogromne wycieki powodują tempdb i rosną aż do zapełnienia dysku. Tym razem stało się nieco mniej oczywiste, co się stało, ponieważ tempdb nie był przyczyną pełnego dysku, lecz samą bazę danych.

Fakty:

Zwykła wielkość bazy danych wynosi około 55 GB, wzrosła do 605 GB.
Plik dziennika ma normalny rozmiar, plik danych jest ogromny.
Plik danych ma 85% dostępnego miejsca (interpretuję to jako „powietrze”: miejsce, które zostało wykorzystane, ale zostało zwolnione. SQL Server rezerwuje całe miejsce po przydzieleniu).
Rozmiar Tempdb jest normalny.

Znalazłem prawdopodobną przyczynę; jest jedno zapytanie, które wybiera o wiele za dużo wierszy (złe połączenie powoduje wybór 11 miliardów wierszy, gdzie oczekuje się kilkuset tysięcy). Jest to SELECT INTOzapytanie, które sprawiło, że zastanawiałem się, czy mógł wystąpić następujący scenariusz:

WYBIERZ INTO jest wykonywany
Tabela docelowa jest tworzona
Dane są wstawiane zgodnie z ich wyborem
Dysk zapełnia się, powodując awarię wkładki
SELECT INTO jest przerywane i wycofywane
Wycofanie zwalnia miejsce (dane już wstawione są usuwane), ale SQL Server nie zwalnia zwolnionego miejsca.

Jednak w tej sytuacji nie spodziewałbym się, że utworzona przez nią tabela SELECT INTOnadal będzie istnieć, powinna zostać usunięta przez wycofanie. Testowałem to:

BEGIN TRANSACTION 
SELECT  T.x
INTO    TMP.test
FROM    (VALUES(1))T(x)

ROLLBACK

SELECT  * 
FROM    TMP.test

To skutkuje:

(1 row affected)
Msg 208, Level 16, State 1, Line 8
Invalid object name 'TMP.test'.

Jednak tabela docelowa istnieje. Rzeczywiste zapytanie nie zostało jednak wykonane w jawnej transakcji, czy to może wyjaśnić istnienie tabeli docelowej?

Czy założenia, które tu naszkicowałem, są prawidłowe? Czy to prawdopodobny scenariusz?

— HoneyBadger
źródło

17

Rzeczywiste zapytanie nie zostało jednak wykonane w jawnej transakcji, czy to może wyjaśnić istnienie tabeli docelowej?

Tak, dokładnie tak.

Jeśli wykonasz prosty select intopoza explicit transaction, są dwa transactionsw trybie automatycznego zatwierdzania: pierwszy tworzytable i drugi wypełnia go.

Możesz to udowodnić w ten sposób:

W dedykowanym databasena serwerze testowym w simple recovery model, najpierw zrób a checkpointi upewnij się, że dziennik zawiera tylko kilka wierszy (3 w przypadku 2016) związanych z checkpoint. Następnie uruchom select intojeden z wierszy i sprawdź logponownie, szukając begin tranpowiązania z select into:

checkpoint;

select *
from sys.fn_dblog(null, null);

select 'a' as col
into dbo.t3;  

select *
from sys.fn_dblog(null, null)
where Operation = 'LOP_BEGIN_XACT'
      and [Transaction Name] = 'SELECT INTO';

Dostaniesz 2 rzędy, pokazując, że masz 2 transactions .

Czy założenia, które tu naszkicowałem, są prawidłowe? Czy to prawdopodobny scenariusz?

Tak, są poprawne.

insertCzęścią select intobyło rolled back, ale to nie zwalnia żadnej przestrzeni danych. Możesz to sprawdzić, wykonując sp_spaceused; zobaczysz dużounallocated space .

Jeśli chcesz, aby baza danych zwolniła to nieprzydzielone miejsce, powinieneś shrinkplik danych.

— sepupiczny
źródło

15

Masz rację, SELECT...INTOpolecenie nie jest atomowe. Nie było to udokumentowane w momencie publikacji oryginalnego postu, ale jest teraz wywoływane specjalnie na stronie SELECT - INTO Clause (Transact-SQL) w MS Docs (tak, open source!):

SELECT...INTOOświadczenie działa w dwóch części - zostaje utworzona nowa tabela, a następnie wiersze są wstawiane. Oznacza to, że jeśli wstawki zawiodą, wszystkie zostaną wycofane, ale nowa (pusta) tabela pozostanie. Jeśli potrzebujesz, aby cała operacja zakończyła się powodzeniem lub niepowodzeniem, użyj jawnej transakcji .

Stworzę bazę danych, która wykorzystuje pełny model odzyskiwania. Dam mu dość mały plik dziennika, a następnie powiem, że plik dziennika nie może się automatycznie rejestrować:

CREATE DATABASE [SelectIntoTestDB]
ON PRIMARY 
( 
    NAME = N'SelectIntoTestDB', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SelectIntoTestDB.mdf', 
    SIZE = 8192KB, 
    FILEGROWTH = 65536KB
)
LOG ON 
( 
    NAME = N'SelectIntoTestDB_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SelectIntoTestDB_log.ldf', 
    SIZE = 8192KB, 
    FILEGROWTH = 0
)

A potem spróbuję wstawić wszystkie posty z mojej kopii bazy danych StackOverflow2010. To powinno napisać kilka rzeczy do pliku dziennika.

USE [SelectIntoTestDB];
GO

SELECT *
INTO dbo.Posts
FROM StackOverflow2010.dbo.Posts;

Spowodowało to następujący błąd po uruchomieniu przez 4 sekundy:

Msg 9002, poziom 17, stan 4, wiersz 1
Dziennik transakcji dla bazy danych „SelectIntoTestDB” jest pełny z powodu „ACTIVE_TRANSACTION”.

Ale w mojej nowej bazie danych jest pusta tabela Postów:

Tak więc, jak podejrzewasz, CREATE TABLEudało się, ale INSERTcała część została wycofana. Obejściem byłoby użycie jawnej transakcji (co już zauważyłeś w swoim pytaniu).

— Josh Darnell
źródło