SQL Server – Kontrola dzienników transakcji

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:

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:

• Mój log jest za duży (Paweł Potasiński)
• New script: is that database REALLY in the FULL recovery mode? (Paul S. Randal)
• 8 Steps to better Transaction Log throughput (Kimberly L. Tripp)
• Transaction Log VLFs – too many or too few? (Kimberly L. Tripp)

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