Leggere il log delle transazioni di un database di SQL Server

Il log delle transazioni su SQL Server contiene le informazioni relative ai cambiamenti avvenuti sui nostri database. Esso possiede le informazioni tali da recuperare un database in un preciso istante, per rilanciare o annullare un cambiamento. Come visualizzare il suo contenuto, come cercare una singola transazione, come capire cosa è successo per annullare i cambiamenti?

Per controllare il contenuto di un log delle transazioni online oppure da un suo backup non è un’operazione semplice.

Aprire i file LDF e TRN tramite editor binari mostrerebbe qualcosa di non leggibile, quindi non è possibile leggerlo direttamente. Questo è un esempio di file LDF:

Utilizzare la funzione fn_dblog

fn_dblog è una funzione non documentata di SQL Server che legge le porzioni attive di un log delle transazioni online.

Osserviamone un esempio:

1. Eseguire la lettura dalla funzione

Select * FROM sys.fn_dblog(NULL,NULL)



Siccome la funzione ritorna 129 colonne è consigliabile filtrare almeno in base al tipo della transazione. O comunque selezionare solo quelle necessarie alla ricerca.

2. Ecco come filtrare

Inserimenti

SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
       [Begin time]
       FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_INSERT_ROWS');



Cancellazioni

SELECT [begin time], 
       [rowlog contents 1], 
       [Transaction Name], 
       Operation
  FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_DELETE_ROWS');



3. Ricavare le colonne che contengono i valori inseriti o cancellati (RowLog Contents 0, 1, 2, 3 e 4, Description e Log Record). Siccome i valori sono salvati su diverse colonne è necessario conoscere come vengono usate in base al tipo di transazione.

   Le righe inserite e cancellate sono mostrate in esadecimale. Per trasformare questa codifica in campi, vi è da conoscere perfettamente la struttura che si va a ricercare (come il numero di colonne che ha l’oggetto ad esempio)

4. Convertire i dati binari in tabelle tenendo conto dei tipi di dato delle colonne. Inoltre, ogni meccanismo di conversione va valutato per ogni tipo di dato specifico.

fn_dblog è molto potente ed è free ma, come possiamo immaginare, ha alcune limitazioni. Ad esempio, leggere i record del log delle transazioni per gli oggetti che cambiano è piuttosto complesso e di solito coinvolge anche oggetti di sistema. Poiché la porzione attiva è quella che viene ritornata dalla funzione, non sono disponibili tutte le ricostruzioni UPDATE/BLOB.

L’operazione di UPDATE è registrata in maniera minima (minimally logged) nel log delle transazioni. Questo significa che nessun vecchio o nuovo valore viene effettivamente salvato, solo quello che è effettivamente cambiato per il record (ad esempio, SQL Server potrebbe tenere traccia del cambiamento di un carattere “G” in “F”, mentre in realtà il valore del campo è passato dalla stringa “Gloat a “Float”). L’unica possibilità per avere questa informazione è quella di costruirsi manualmente tutti gli stati intermedi.

Quando si eseguono cancellazioni sui BLOB essi non sono inseriti nel log delle transazioni, quindi il tentativo di lettura di questi oggetti non porterà a nulla. Solo nel caso in cui esiste l’inserimento del BLOB cancellato (e se si considerano entrambe le operazioni accoppiate) è possibile recuperare il BLOB cancellato con la fn_dblog.

Utilizzare fn_dump_dblog

Per leggere il log delle transazioni nativamente, oppure da un backup compresso, anche senza avere il database online, è possibile usare la funzione fn_dump_dblog. Anch’essa non è documentata. Ecco come leggere con questa funzione:

1. Eseguirla su uno specifico log backup. Attenzione, vi sono da specificare tutti i 63 parametri:

SELECT *
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT);



Lo stesso numero di colonne (129) viene tornato. Anche in questo caso è consigliabile utilizzare solo quelle necessarie.

SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
     [transaction name],
       Description
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT);

Anche in questo caso vi è da decifrare i valori esadecimali.



Come per la fn_dblog, è necessario ricostruire i valori manualmente, gli stati per le update, ed effettuare le operazioni sui BLOB.

2. Se non si vuole estrarre le transazioni dal log backup, bensì da un ripristino di un database in uno specifico istante (ad esempio prima che un’operazione sia stata eseguita), è possibile:

   • Determinare il LSN (Log Sequence Number) della transazione specifica
   • Convertire il LSN nel formato usato nella clausola WITH STOPBEFOREMARK = ‘‘ (ad esempio 00000070:00000011:0001 dovrà essere trasformato in 112000000001700001)
   • Effettuare il ripristino di tutta la catena dei backup dei log fino a raggiungere l’istante in cui la transazione si è verificata (usando WITH STOPBEFOREMARK = ‘‘)

   RESTORE LOG AdventureWorks2012
   FROM
       DISK = N'E:\ApexSQL\backups\AW2012_05232013.trn'
   WITH
       STOPBEFOREMARK = 'lsn:112000000001700001',
       NORECOVERY;

Utilizzare DBCC PAGE

Un altro metodo (sempre non documentato) è servirsi del comando console DBCC PAGE, al fine di leggere il contenuto dei file del database online (MDF/NDF e LDF). La sintassi è la seguente:

DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Per fare il dump della prima pagina del log delle transazioni del database AdventureWorks2012 utilizzare:

SELECT FILE_ID ('AdventureWorks2012_Log') AS 'File ID' 
-- to determine Log file ID = 2
DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Con esso avremo:

DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

Di default l’output non è mostrato. Per visualizzare il risultato su SQL Server Management Studio, abilitare il trace flag 3604:

DBCC TRACEON (3604, -1)

E poi rieseguire il comando seguente:

DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Ora riceveremo un set di errori e altri messaggi relativi all’intestazione danneggiata, ma è possibile ignorarli. Alla fine avremo l’output desiderato per il file LDF, sempre in esadecimale:

Di certo non si tratta della migliore visualizzazione dei nostri database e non si discosta tanto da quanto avremmo visto usando un editor di testo avanzato (a dire il vero è pure più scomodo).

Utilizzare ApexSQL Log

ApexSQL Log è uno strumento utile a leggere log delle transazioni su SQL Server. Legge log delle transazioni online, log delle transazioni scollegati e backup dei log (nativamente compressi o meno). Se necessario riesce ad ottenere i dati necessari per ricostruzioni di successo. Inoltre può eseguire replay dei cambiamenti delle strutture e dei dati su di un database (anche quelli che sono occorsi prima dell’installazione del tool). A differenza dei metodi non documentati d cui sopra tutte le informazioni tornate saranno comprensibili e sarà chiaro tutto quanto sia successo (su che oggetto, quali nuovi e vecchi valori sono stati coinvolti, ecc).

Ma ecco come procedere:

1. Eseguire ApexSQL Log

2. Connettersi al database del quale leggere il log delle transazioni

   

3. In Select SQL logs to analyze, selezionare i log da leggere. Assicurarsi che ci sia una catena completa.

   

4. Per aggiungere un backup del log delle transazioni e gli eventuali file LDF scollegati, premere Add.

5. Con Filter setup è possibile affinare la ricerca sulle transazioni, tramite gli intervalli di date, I tipi di operazioni, ecc.

   

6. Premere Open

   Una griglia di risultati comprensibili verrà mostrata.

   Tramite essa saranno chiari anche:

   • le date in cui l’operazione è iniziata e finita
   • il tipo dell’operazione effettuata
   • lo schema ed il nome dell’oggetto
   • il nome dell’utente che ha eseguito l’operazione
   • il computer e l’applicazione di origine

   • solo per le update: nuovi e vecchi valori dei campi interessati dall’operazione

     

Insomma, per evitare i valori esadecimali, funzioni non documentate, query molto complesse e lunghe e tutti gli altri problemi sopra elencati, utilizzare ApexSQL Log. Con esso avremo letture veloci, semplici e chiare con risultati in inglese leggibile. Gli script di undo e di redo conseguenti sono disponibili con un click.

Traduttore Alessandro Alpi