Raport Schema Changes History i default trace w SQL Server

SQLServer_SChemaChangesHistory_00

Szeroko pojęte operacje DDL (Data Definition Language) w SQL Server czyli wszelkiego rodzaju CREATE, DROP, ALTER powinny być używane z rozwagą szczególnie jeśli chodzi o serwery produkcyjne. Stwierdzenie to jest oczywiste dla niemal każdego, a ja przytoczyłem go ze względu na tematykę niniejszego artykułu, którym jest wbudowany w SSMS raport Schema Changes Hsitory pozwalający nam w przystępny sposób dostrzec co, gdzie i kiedy zostało wykonane na naszym serwerze. Przyjmy się nie tylko samemu raportowi ale również mechanizmowi na którym on bazuje.

Na samym wstępie powiedzmy sobie gdzie w ogóle wspomniany raport możemy znaleźć. Rzekłbym w standardowym miejscu tj. wystarczy kliknąć prawym przyciskiem myszy na serwer i tam z menu kontekstowego wybrać Reports -> Standard Reports -> Schema Changes History:

Po wykonaniu tej sekwencji kroków powinniśmy zobaczyć prosty raport przedstawiający szereg zmian wykonanych na obiektach znajdujących się na wskazanym przez nas serwerze. Zanim wyświetlimy sam raport wykonajmy kilka testowych zmian. Do tego celu przygotowałem prosty skrypt, który ma za zadanie wykonać następujące cztery czynności:

  • stworzyć bazę danych testSCH,
  • stworzyć pięć tabel testowych,
  • do każdego tabeli testowej dodać kolumnę name o typie nvarchar(20),
  • usunąć stworzone tabele.

Pomiędzy operacjami w pętli mamy dwie sekundy odstępu tak aby poszczególne operacje nie nachodziły na siebie czasowo (ogólnie zabieg ten zastosowałem w celach estetycznych aby na raporcie było widać wyraźną sekwencję kroków):

Skrypt ten powinien wygenerować tyle operacji, że powinniśmy mieć możliwość zaobserwowania tychże zmian na naszym raporcie i tak też w istocie jest:

Jak możecie zauważyć na powyższym zrzucie ekranowym raport przedstawia nam następujące informacje:

  • Database Name – nazwę bazy danych, której operacja dotyczy,
  • Object Name – nazwę obiektu w bazie, którego operacja dotyczy (chyba, że operacja dotyczy całej bazy wtedy to pole pozostanie puste),
  • Type – typ obiektu, którego operacja dotyczy
  • DDL Operation – rodzaj operacji czyli DROP/ALTER/CREATE,
  • Time – data i czas kiedy operacja została wykonana,
  • Login Name – nazwa użytkownika, który wykonał operację.

Nie są to bardzo szczegółowe operacje ale z całą pewnością w wielu przypadkach okażą się pomocne. Jeśli ktoś usunął jakiś obiekt to gdzie zacząć szukać? No właśnie ten raport jest dobrym kandydatem na punkt startowy naszych poszukiwań. W tym miejscu może się pojawić pytanie jak to wszystko działa i jaki mechanizm stoi za tym raportem? Odpowiedź nie powinna nikogo zaskoczyć i jest to nic innego jak tzw. Default trace. Jest to mechanizm będący w SQL Server od bardzo dawna  i polega on na śledzeniu określonych wydarzeń i ich rejestracji. Oczywiście nie wszystko i nie na zawsze jest rejestrowane. Trace ten składa się z pięciu plików z limitem wielkości 20MB o czym możemy dowiedzieć się odpytując sys.traces:

Po osiągnięciu progu wielkości dane zapisywane są ponownie w najstarszym pliku, nadpisując tym samym najstarsze dane (kolumna is_rollover=1). Jeśli chcemy mieć pewność, że dane będą gdzieś zachowane do późniejszej analizy to możemy je odkładać w dedykowanym miejscu bądź też np. archiwizować fizyczne pliki, ale to jest temat na zupełnie inny artykuł. Wracając do tematu to jedyną kontrolę jaką mamy nad tym mechanizmem to możemy go włączyć lub wyłączyć używając poniższej komendy konfiguracyjnej:

W default trace znajdują się nie tylko dane, które widzimy na omawianym raporcie. Oczywistym jest również fakt, że nie cała aktywność jest rejestrowana, a jedynie wybrane zdarzenia. Aby poznać całą listę rejestrowanych zdarzeń musimy wykonać poniższe zapytanie:

W rezultacie otrzymamy ponad 34 różnego rodzaju zdarzenia podzielone na siedem grup:

Database:

  • Data File Auto Grow
  • Data File Auto Shrink
  • Database Mirroring State Change
  • Log File Auto Grow
  • Log File Auto Shrink

Errors and Warnings:

  • ErrorLog
  • Hash Warning
  • Missing Column Statistics
  • Missing Join Predicate
  • Sort Warnings

Full text:

  • FT:Crawl Started
  • Full text FT:Crawl Stopped

Objects:

  • Object:Altered
  • Object:Created
  • Object:Deleted

Performance:

  • Plan Guide Unsuccessful

Security Audit:

  • Audit Add DB User Event
  • Audit Add Login to Server Role Event
  • Audit Add Member to DB Role Event
  • Audit Add Role Event
  • Audit Addlogin Event
  • Audit Backup/Restore Event
  • Audit Change Audit Event
  • Audit Change Database Owner
  • Audit Database Scope GDR Event
  • Audit DBCC Event
  • Audit Login Change Property Event
  • Audit Login Failed
  • Audit Login GDR Event
  • Audit Schema Object GDR Event
  • Audit Schema Object Take Ownership Event
  • Audit Server Alter Trace Event
  • Audit Server Starts And Stops

Server:

  • Server Memory Change

Z każdym z tych zdarzeń powiązane są określone włąściwości jak np. czas czy nazwa użytkownika. Patrząc na tą listę możemy się domyślać, które zdarzenia są wyświetlane na raporcie Schema History Changes i są to zdarzenia z grupy Objects. Możemy to bardzo łatwo zweryfikować próbując wychwycić jakie zapytanie jest wykonywane gdy uruchomimy raport:

Kawał kodu ale możemy z niego wynieść informację o głównym warunku filtrującym czyli EventClass in (46,47,164). Same numery nie wiele nam mówią ale łatwo je odszyfrować gdyż w samym skrypcie znajduje się mapowanie dla tych wartości tj.

Wnikliwe oko dostrzeże również wykluczenie dla bazy danych o ID=2. Jak dobrze wiemy jest to baza tempdb tak więc raport nie będzie pokazywał obiektów tymczasowych pod postacią tabel lub zmiennych. Całkiem mądry warunek bez którego wyciągnięcie czegoś wartościowego z raportu byłoby niemal niemożliwe. Baza tempdb jest jedynym wykluczeniem jakie mamy na raporcie tak więc wszelkie zmiany w innych bazach systemowych zostaną oczywiście zarejestrowane (daje to ciekawe możliwości wyśledzenia kto stworzył omyłkowo obiekty w bazie master co zdarza się nader często).

Wszystko wydaje być się jasne i klarowne. Ciekawie rzeczy dzieją się w sytuacji gdy default trace jest wyłączony. Przetestujmy to zatem i zobaczmy co się stanie:

Po uruchomieniu omawianego raportu wygląda on całkowicie inaczej:

Pomijając dużą część kodu zestawienie to opiera się na prostym przeszukaniu widoków systemowych:

Co ciekawe przeszukiwana jest każda baza użytkownika, tak więc otrzymany rezultat rzeczywiście będzie odnosił się do serwera, a nie pojedynczej bazy. Całkiem dobre zachowanie – dobrze, że raport wyświetla jakieś dane, a nie jest w całości pusty. Brakuje mu jednak jednej zasadniczej informacji, a mianowicie tego kto określoną operację wykonał.

Jeśli chodzi o default trace to mechanizm ten ma jedną, zasadniczą wadę, a mianowicie to, że jest wycofywany i z całą pewnością w nowych projektach nie powinniśmy budować na nim żadnych rozwiązań. Zalecaną metodą związaną ze zbieraniem wszelkich informacji jest oczywiście charakteryzujący się lekkością  Extended Events. Dlaczego zatem zawracać sobie głowę Schema Changes History i Default trace? A no dlatego, że domyślnie mechanizm ten jest włączony i w sytuacji “zastanego systemu” może być dobrym rozwiązaniem aby zbadać zmiany jakie zaszły (chyba, że ktoś wpadł na pomysł wyłączenia tego mechanizmu). W każdym bądź razie warto mieć w tyle głowy wiedzę, że coś takiego istnieje i jak z tego korzystać aby w razie potrzeby wiedzieć jak z tego skorzystać.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

Leave a Comment

Your email address will not be published. Required fields are marked *