SQLShack

Questo articolo esplora l’uso di più file di registro delle transazioni di SQL Server e il processo di rimozione del file di registro delle transazioni secondario.

Introduzione

Per impostazione predefinita, un database SQL Server contiene un file di dati primario e un file di log delle transazioni. È una buona pratica configurare più file di dati e dividere i dati su più file di dati. Possiamo avere questi file di dati in unità di archiviazione separate per avere più IO. Aiuta la gestione dei dati, migliora le prestazioni, pianifica gli approcci di backup in base ai filegroup.

Possiamo avere più file di log delle transazioni anche per un database.

SQL Server utilizza i file di log delle transazioni multiple in ordine sequenziale. Supponiamo di avere due file di log delle transazioni. SQL Server utilizza un file di registro alla volta e una volta che è pieno, SQL Server utilizza un altro file di registro. Poiché SQL Server non lo utilizza in parallelo, non ne otteniamo alcun vantaggio in termini di prestazioni. Idealmente, dovremmo avere un solo file di log delle transazioni per database.

Supponiamo di avere un database di produzione e riceviamo un avviso critico che il disco sta esaurendo lo spazio. Dopo l’indagine, abbiamo scoperto che questa unità contiene un file di log delle transazioni. A causa delle transazioni attive, questo file di registro delle transazioni di SQL Server è pieno. Abbiamo provato a ridurre il file di registro, ma non ha funzionato. Eseguiamo anche il backup del registro delle transazioni, ma a causa delle transazioni attive, non è stato possibile rilasciare lo spazio necessario. L’aggiunta di un altro file di log delle transazioni in un disco separato con spazio libero risolverà questo problema.

Poiché SQL Server utilizza la modalità seriale per la scrittura dei dati in un file di log delle transazioni, dovremmo rimuovere il file di log aggiuntivo in seguito. Esploriamo il processo di rimozione di un file di registro aggiuntivo.

Creare un database con più file di log delle transazioni SQL Server

Connettersi a un’istanza SQL in SQL Server Management Studio. Fare clic con il pulsante destro del mouse sul nodo Database nel riquadro Esplora oggetti e fare clic sul comando Nuovo database:

Apre la finestra Nuovo database. Specificare un nome di database e aggiungere un altro file di log delle transazioni. A scopo di test, abbiamo disabilitato la crescita automatica dei file di log delle transazioni. La dimensione iniziale del file di log delle transazioni è 8 MB:

Dopo aver impostato tutto, fare clic sul pulsante OK per creare un database in una directory predefinita di dati/file di registro. Creare una tabella e inserire i dati in:

Visualizza lo stato del file di log virtuale in SQL Server transaction log files

Nell’articolo, SQL Server Transaction Log Architecture, abbiamo esplorato l’interno del file di log delle transazioni. Ogni file di log delle transazioni è costituito da più file di log virtuali. Un file di log delle transazioni è una combinazione di più file di log virtuali (VLF). La seguente schermata mostra l’architettura fisica e logica del file di registro:

SQL Server stats un database con VLF minimo in base alla dimensione del log iniziale e al file di crescita automatica (in base alla configurazione di crescita automatica). Nell’immagine seguente, si ottiene un assaggio del file di registro delle transazioni di SQL Server:

Nel caso di un singolo file di registro delle transazioni, SQL Server utilizza un percorso di file di registro virtuale circolare. Possiamo controllare il numero di file di log virtuali e il loro stato utilizzando quanto segue:

1. DBCC LOGINFO (‘Database’) – Si tratta di una vecchia istruzione e funziona con tutte le versioni di SQL Server
2. Dynamic management view sys.dm_db_log_info (DBID). È disponibile da SQL Server 2016 SP2 o versioni successive

Qualsiasi comando può essere utilizzato per il controllo VLF. Per questo articolo, useremo la gestione dinamica di vista (DMV):

Nella schermata qui sopra, abbiamo verificato che il nostro database di esempio contiene due file di registro delle transazioni (file_id 2 e file_id 3).

• File_id 2 è attivo VLF (vlf_active=1 e vlf_status=2)
• File_id 3 non sono attive, VLF (vlf_active=0 e vlf_status=2)

basta inserire un paio di più record nella tabella in modo che l’VLF cambia:

Il log delle transazioni, l’utilizzo dello spazio di monitoraggio e VLF stato fatto con il DMV (sys.dm_db_log_space_usage):

• VLF stato

  1 2	SELEZIONARE DB_name() come DatabaseName,File_ID transaction_log_file_ID, vlf_active , vlf_status DA sys.dm_db_log_info(DB_ID(‘MultileLogFiles’));

• utilizzo del log delle Transazioni (spazio libero e utilizzato)

  1 2 3 4 5

  SELEZIONARE total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB, used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes – used_log_space_in_bytes)*1.0/1024/1024 COME free_log_space_in_MB DA sys.dm_db_log_space_usage;

otteniamo il seguente output della query di cui sopra:

• Sul lato sinistro, si vede che una volta che il file di registro principale (file_id 2) è pieno, si sposta alla successiva file di log (file_id 3). A questo punto, abbiamo VLF attivo sia nei file di registro primari che secondari. In un database con un modello di ripristino completo, abbiamo bisogno di un backup del log delle transazioni in modo che contrassegni VLF come inattivo
• L’output sul lato destro (usando DMV) mostra lo spazio utilizzato del file di log delle transazioni 8.51 MB. Come ricorderai, abbiamo impostato 8 MB di dimensione per ogni file di log delle transazioni e abbiamo disabilitato la crescita automatica del log. Quando il file di registro primario è pieno (a 8 MB), SQL Server passerà al file di registro delle transazioni secondario

Rimuovi il file di registro delle transazioni secondario SQL Server

Per rimuovere il file di registro delle transazioni secondario (file id 3), useremo l’istruzione Alter database modificata.

Aggiungeremo la clausola REMOVE FILE e specificheremo il file che vogliamo rimuovere:

l’Esecuzione di questa istruzione verrà visualizzato il seguente messaggio di errore:

Nota: active file di registro delle transazioni non possono essere rimossi.

In precedenza, abbiamo visto che una volta che il file di registro primario diventa pieno, SQL Server utilizza il file di registro secondario. Dobbiamo rendere vuoto un registro delle transazioni secondarie, in modo da poterlo rimuovere.

Nel database SQL con un modello di ripristino completo, utilizziamo i backup del log delle transazioni in modo che SQL Server possa troncare i log. Potremmo aver bisogno di più backup di log per questo a seconda delle dimensioni del log delle transazioni, VLF attivo e transazione attiva.

facciamo eseguire un completo database e backup del log delle transazioni:

• backup Completo del database

  1	backup Database MultipleLogFiles to disk=’C:\Temp\MultipleLogFiles.bak’

• backup del log delle Transazioni

  1	backup del registro MultipleLogFiles to disk=’C:\Temp\MultipleLogFiles_log.trn’

Una volta completato il backup, verificare lo stato VLF. Il VLF attivo dovrebbe essere nel primo file di registro (primario) in modo da poter rimuovere il file di registro secondario. Abbiamo verificato che solo il file di registro primario (file_id 2) è attivo (VLF Stato 2):

Ora, il file di log delle transazioni secondario dovrebbe essere rimosso senza problemi. Eseguiamo nuovamente l’istruzione Alter database modificata. Il file di registro delle transazioni secondario verrà rimosso:

Una volta rimosso il file di registro delle transazioni secondario, verificarlo utilizzando la GUI e T-SQL con system view sys._file di database:

Il file di registro delle transazioni secondario rimosso è ancora presente come da schermata seguente:

Ora fare clic destro sul database e visualizzare i file esistenti. Vediamo anche il file di log delle transazioni rimosso nella GUI. Ma perché?

Eseguiamo nuovamente l’istruzione Alter database modificata e vediamo se SQL Server rimuove nuovamente il file di log delle transazioni.

Abbiamo ricevuto il messaggio che SQL Server non riusciva a trovare il file di registro specificato:

SQL Server rimuove il file di log delle transazioni dopo un backup di log successivo. Prendiamo un altro backup del registro e verifichiamo che il file di registro delle transazioni esista ancora:

Verificare il file log delle transazioni sia nei metodi GUI che T-SQL. Vediamo che il file di log delle transazioni rimosso non viene visualizzato ora.

Conclusione

In questo articolo, abbiamo esplorato l’utilizzo del log delle transazioni SQL Server secondario e il processo di rimozione. Evitare l’utilizzo di più file di log delle transazioni, in particolare nel database di produzione. Si dovrebbe prendere un backup del database prima di pianificare qualsiasi attività e farlo in ore non di produttività.

• Autore
• Post Recenti

Come un certificato MCSA e Microsoft Certified Trainer a Gurgaon, in India, con 13 anni di esperienza, Rajendra collabora con una serie di grandi imprese di messa a fuoco sull’ottimizzazione delle prestazioni, il monitoraggio, l’alta disponibilità e disaster recovery per le strategie e l’attuazione. È autore di centinaia di articoli autorevoli su SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git e tecnologie correlate che sono stati visti da oltre 10 milioni di lettori fino ad oggi.
È il creatore di una delle più grandi collezioni online gratuite di articoli su un singolo argomento, con la sua serie di 50 parti su SQL Server Always On Availability Groups. Sulla base del suo contributo alla comunità SQL Server, è stato riconosciuto con vari premi tra cui il prestigioso “Best author of the year” ininterrottamente nel 2020 e 2021 a SQLShack.
Raj è sempre interessato a nuove sfide, quindi se hai bisogno di aiuto di consulenza su qualsiasi argomento trattato nei suoi scritti, può essere raggiunto a rajendra.gupta16 @ gmail.com
Visualizza tutti i messaggi di Rajendra Gupta

Ultimi messaggi di Rajendra Gupta (vedi tutti)

• Utilizzare il BRACCIO di modelli per distribuire Azure contenitore di istanze di SQL Server Linux immagini – 21 dicembre, 2021
• accesso Remoto al desktop per AWS RDS SQL Server con Amazon RDS Personalizzato – dicembre 14, 2021
• Memorizzare i file di SQL Server Persistente di Archiviazione di Azure Contenitore di Istanze di dicembre 10, 2021