Jasne i ciemne strony baz danych

… w szczególności MS SQL Server ;-)

SQL Server – Kontrola dzienników transakcji

Posted by C3PO w dniu 7 lipca 2010


Jednymi z problemów najczęściej poruszanych na forum poświęconym SQL Serverowi na portalu WSS.pl są wszelkiej maści kłopoty wynikające z zaniedbania dziennika transakcji bazy danych. Rekordziści prześcigają się w wielkości plików dziennika transakcji (185 GB dla bazy kilkugigabajtowej to dobry przykład). Często padają pytania, jak to wszystko (recovery model, dziennik transakcji, backup dziennika transakcji) działa. Nie zamierzam się rozpisywać o podstawach logowania operacji DML czy o tym, jak zbudowany jest dziennik transakcji. Pomyślałem za to, że fajnie byłoby napisać skrypt dla DBA lub konsultanta, dzięki któremu będzie można, wchodząc na „cudzy” (lub, o zgrozo, także swój!) serwer, zdiagnozować podstawowe problemy związane z higieną dziennika transakcji (a w zasadzie z brakiem tej higieny). Wspomniany skrypt prezentuje się tak:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#VLFs', 'U') IS NOT NULL
  DROP TABLE #VLFs;
CREATE TABLE #VLFs (
  FileId int NULL,
  FileSize bigint NULL,
  StartOffset bigint NULL,
  FSeqNo bigint NULL,
  Status tinyint NULL,
  Parity smallint NULL,
  CreateLSN numeric(25,0) NULL
);
IF OBJECT_ID('tempdb.dbo.#NumberOfVLFs', 'U') IS NOT NULL
  DROP TABLE #NumberOfVLFs;
CREATE TABLE #NumberOfVLFs (
  DatabaseName sysname NOT NULL,
  NumberOfVLFs int NOT NULL
);
DECLARE @DBName sysname, @SQL nvarchar(4000);
DECLARE DBCursor CURSOR LOCAL STATIC
FOR
  SELECT name FROM sys.databases WHERE state_desc = 'ONLINE';
OPEN DBCursor;
FETCH NEXT FROM DBCursor INTO @DBName;
WHILE @@FETCH_STATUS = 0 BEGIN
  DELETE FROM #VLFs;
  SET @SQL =
  'INSERT INTO #VLFs
   EXEC ' + QUOTENAME(@DBName)
     + '.dbo.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
   INSERT INTO #NumberOfVLFs (DatabaseName, NumberOfVLFs)
   SELECT ' + QUOTENAME(@DBName, '''') + ', COUNT(*)
   FROM #VLFs;';
  EXEC (@SQL);
  FETCH NEXT FROM DBCursor INTO @DBName;
END
CLOSE DBCursor;
DEALLOCATE DBCursor; 
IF OBJECT_ID('tempdb.dbo.#LogHealth', 'U') IS NOT NULL
  DROP TABLE #LogHealth;
CREATE TABLE #LogHealth (
  DatabaseName sysname NULL,
  LogSizeMB numeric(38,2) NULL,
  LogSpaceUsed numeric(4,2) NULL,
  Status tinyint NULL
);
INSERT INTO #LogHealth EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');
SELECT
  v.DatabaseName AS [Database name],
  d.recovery_model_desc AS [Recovery model],
  CASE
    WHEN d.recovery_model = 1
      AND r.last_log_backup_lsn IS NOT NULL
    THEN 1
    ELSE 0
  END AS [Was backup taken],
  q.number_of_files AS [Number of log files],
  l.LogSizeMB AS [Log size (MB)],
  l.LogSpaceUsed AS [Log space used (%)],
  v.NumberOfVLFs AS [Number of VLFs],
  d.log_reuse_wait_desc AS [Log reuse wait cause]
FROM #NumberOfVLFs AS v
INNER JOIN #LogHealth AS l
ON v.DatabaseName = l.DatabaseName
INNER JOIN sys.databases AS d
ON v.DatabaseName = d.name
INNER JOIN sys.database_recovery_status AS r
ON d.database_id = r.database_id
INNER JOIN (
  SELECT
    m.database_id,
    COUNT(*) AS number_of_files
  FROM sys.master_files AS m
  WHERE m.type = 1
  GROUP BY m.database_id
) AS q
ON d.database_id = q.database_id;
GO

Wynikiem działania powyższego kodu jest coś takiego:

Wynik

Co oznaczają kolejne kolumny?

  • Database name – nazwa bazy danych (powinny pokazać się wszystkie bazy)
  • Recovery model – tryb pracy dziennika transakcji (dla produkcyjnych baz zalecany FULL)
  • Was backup taken – wskazuje, czy został wykonany backup rozpoczynający działanie bazy w trybie FULL
  • Number of log files – ilość plików dziennika transakcji, w zasadzie zawsze powinien być 1 plik (wyjątkiem są sytuacje, gdy musimy dodać drugi lub kolejny plik dziennika transakcji z uwagi na brak miejsca na dysku przechowującym pierwszy plik dziennika)
  • Log size (MB) – rozmiar dziennika transakcji w megabajtach (wiadomo – duży może być zwłaszcza, gdy dziennik transakcji jest zaniedbany)
  • Log space used (%) – ile procent dziennika transakcji jest zajęte przez zapisane w nim transakcje (tak dowiemy się, ile brakuje do tego, by dziennik transakcji automatycznie się rozrósł)
  • Number of VLFs – ilość wirtualnych plików dziennika transakcji (ang. Virtual Log File); zalecane jest poniżej 50, ale rząd wielkości więcej – 300-500 to też nie jest wielka tragedia; ale jeżeli liczba idzie w dziesiątki / setki tysięcy, można spodziewać się problemów przy starcie bazy oraz dłuższego czasu wykonywania backupów i przywracania bazy
  • Log reuse wait cause – informacja na temat przyczyny braku możliwości ponownego zapełniania plików dziennika transakcji od pierwszych plików wirtualnych (najczęściej otwarte transakcje, ale przyczyn może być więcej)

Sam kod wykorzystuje kilka widoków i poleceń DBCC:

  • sys.databases – widok systemowy zawierający listę baz danych i ich właściwości
  • sys.master_files – widok systemowy zawierający listę plików wszystkich baz danych
  • sys.database_recovery_status – widok systemowy zawierający informacje na temat procesów recovery baz danych
  • DBCC LOGINFO – nieudokumentowane polecenie DBCC pokazujące układ wirtualnych plików dziennika transakcji dla bieżącej bazy danych (tu użyte do zliczenia wirtualnych plików)
  • DBCC SQLPERF(LOGSPACE) – polecenie DBCC zwracające listę baz danych wraz z wielkością dzienników transakcji oraz stopnia wypełnienia tych dzienników

Mam nadzieję, że powyższy kod się przyda. Działa na SQL Server 2005 i nowszych. Przy odrobinie wysiłku można kod przerobić, by działał także na wersji 2000.

I na deser kilka odnośników do postów na blogach, które przeczytałem przed przystąpieniem do pisania kodu:

Szczególnie ciekawy jest bug wymieniony na koniec ostatniego z wymienionych postów – polecam do poczytania :-)

Reklamy

Komentarzy 5 to “SQL Server – Kontrola dzienników transakcji”

  1. nowbie said

    Czy można prosić o skomentowanie kodu jak dla nowicjusza poznającego t-sql? Z góry dziękuje.

  2. C3PO said

    Ok, krok po kroku:
    1. Tworzę tabelę tymczasową #VLFs – będzie potrzebna do zapisania tego, co dla każdej bazy zwróci DBCC LOGSPACE (chodzi o zliczenie wirtualnych plików dziennika transakcji).
    2. Tworzę tabelę tymczasową #NumberOfVLFs’ – będzie potrzebna do zapisania zliczonych VLF dla każdej bazy danych.
    3. Deklaruję dwie zmienne: @DBName – tu każda iteracja kursora będzie trzymała nazwę bazy danych (bo docelowo chcę wykonać pewne operacje na każdej bazie danych z osobna) i @SQL (przyda się do budowania dynamicznego kodu SQL w kursorze).
    4. Deklaruję kursor DBCursor, który w każdej iteracji wkłada jeden rekord wychodzący z zapytania
    SELECT name FROM sys.databases WHERE state_desc = ‚ONLINE’;
    do zmiennej @DBName.
    5. W pętli dla każdego rekordu pobranego przez kursor (czyli de facto dla każdej nazwy bazy danych ściągniętej przez zapytanie z kroku 4.) czyszczę tabelę #VLFs i wstawiam do niej rekordy zwracane przez wykonanie DBCC LOGINFO w kontekście bazy, której nazwa jest w obecnej iteracji przekazana do zmiennej @DBName. Chwyt pt. „dynamiczny SQL (baza.dbo.sp_executesql) wykonywany w dynamicznym SQLu (EXEC)” jest tu użyty do zmiany kontekstu bazy danych (inaczej wykonywałbym polecenie DBCC LOGINFO w kontekście jednej bazy, a to nie dałoby odpowiedniego wyniku).
    6. Gdy już mam w tabeli #VLFs rekordy odpowiadające VLFom jednej bazy, mogę wstawić do tabeli #NumberOfVLFs rekord – nazwa bazy (@DBName) oraz zliczoną ilość rekordów (COUNT(*) na tabeli #VLFs).
    7. Tworzę tabelę tymczasową #LogHealth i wstawiam do niej to, co zwraca DBCC SQLPERF(LOGSPACE) – czyli rozmiary i zajętość dziennika transakcji dla wszystkich baz.
    8. Piszę zapytanie SELECT, które łączy mi zawartość tabel tymczasowych #NumberOfVLFs i #LogHealth z widokami systemowymi, które zwracają dodatkowe wartościowe informacje (złączenia na ogół są dokonywane po nazwie lub identyfikatorze bazy danych – obie te kolumny są unikalne we wszystkich tabelach).

    I tyle. Mam nadzieję, że teraz daje się to jakoś ogarnąć :-)

  3. Dzień dobry, zajrzałem Twoją stronkę i mogę stwierdzić że jest naprawdę dobrze.

  4. ArekSkwarek said

    Na MSSQL2014 wyrzuca taki kominikat:
    Msg 213, Level 16, State 7, Line 1
    Column name or number of supplied values does not match table definition.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s

 
%d blogerów lubi to: