Jasne i ciemne strony baz danych

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

Posts Tagged ‘administracja’

SQL Server 2008 – Szybkie tworzenie polis

Posted by C3PO w dniu 22 lipca 2010


Ostatnio zdarzyło mi się parę razy użyć mechanizmu Policy-Based Management dostępnego w SQL Server 2008 (i 2008 R2). Zacząłem eksperymentować i szukać wszelkich opcji, które umożliwiłyby w miarę szybkie i sprawne tworzenie polis takiemu leniwemu DBA, jak ja :-)

Jedną z takich opcji jest opcja dostępna w menu kontekstowym okna Object Explorer w Management Studio. Klikamy na przykład prawym przyciskiem myszy na nazwie instancji SQL Servera i wybieramy opcję Facets.

Facets

Pojawia się okno właściwości obiektu (w tym przypadku serwera) w kontekście wybranego szablonu (pole Facet). A na samym dole okna… Tada! Przycisk Export Current State as Policy :-)

Export as policy

Klikamy, zapisujemy polisę do pliku XML albo od razu na instancję SQL Server, otwieramy świeżutko utworzoną polisę, edytujemy. Czysta przyjemność! A wszystko w oparciu o przygotowany wzorzec serwera, bazy, obiektu w bazie danych (można sprawdzić, że pozycja Facets pojawia się w menu kontekstowym po kliknięciu w zasadzie na cokolwiek w oknie Object Explorer).

Nie powiem, zabawka w sam raz dla takich leniuszków, jak ja. Łatwiej zrobić polisę opartą o nieco bardziej rozbudowany warunek i usuwać zbędne właściwości z warunku, niż tworzyć wszystko rękami w pocie czoła ;-)

Reklamy

Posted in MS SQL Server | Otagowane: , , , | 47 Komentarzy »

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 :-)

Posted in MS SQL Server | Otagowane: , , , | 5 Komentarzy »