Come ridurre un file di registro delle transazioni del database Microsoft SQL Server

I file di registro del database di Microsoft SQL Server possono aumentare notevolmente, causando la disconnessione del database e l'indisponibilità in caso di esaurimento dello spazio nel percorso dell'unità del registro delle transazioni. Ecco come comprimere il file di registro per ridurre lo spazio utilizzato e risolvere il problema.

Ciao, sono Curt. Sono Senior Principal Engineer e lavoro con il Team GSE.

Questo video illustra come compattare un file di registro delle transazioni del database Microsoft SQL Server. Verranno esaminati i diversi modi per compattare il log delle transazioni e le azioni da eseguire se l'operazione di compattazione del log non viene eseguita come previsto.

Sono disponibili un paio di modi diversi per ridurre il file di registro. Passiamo a SQL Server Management Studio. Passare alle proprietà del database ed eseguire l'operazione di riduzione per dimostrarla.

Quindi abbiamo anche l'operazione di eseguire il comando TSQL ShrinkFile. L'altro aspetto di cui vogliamo parlare in questo particolare video è cosa succede se l'operazione di compattazione del file non viene eseguita come previsto e discuteremo le opzioni da adottare in questo caso Passiamo ora a Studio Management.

Lavoreremo con il database 'AdventureWorks2017' e se andiamo su 'Tasks' nel menu del database e scendiamo su 'Shrink' abbiamo due opzioni diverse.

Potremmo lavorare a livello di database occupandoci di tutti i file all'interno del database o di file specifici, e in questo caso vogliamo assolutamente andare con file specifici. Per impostazione predefinita, nella nostra tavolozza viene visualizzato "Dati" in "Tipo di file".

Non faremo nulla con il file di database, ma stiamo cercando di ridurre il registro delle transazioni, in modo che il registro delle transazioni sia letteralmente "Registro" sotto "Tipo di file". Ora abbiamo alcune informazioni sul database.

Il registro del database è piuttosto piccolo, si tratta di un nuovo database, vediamo le dimensioni allocate a tale registro delle transazioni e lo spazio libero. Lo spazio libero può essere importante perché senza spazio libero non è possibile eseguire un'operazione di riduzione del file su un database.

Ci sono diverse opzioni. Non entreremo nei dettagli in questa sede, ma abbiamo la possibilità di liberare lo spazio inutilizzato, che in pratica tronca il registro delle transazioni. È sufficiente eseguire questa operazione cliccando su OK per eseguire una riduzione o un tentativo di riduzione del database. Procederemo ora con l'opzione TSQL per la compattazione del database.

Sono un po' pigro oggi, quindi vado avanti e vado fino a 'Azione script negli Appunti' che estrae la transazione, il comando TSQL che sarebbe stato eseguito se avessimo fatto clic su OK qui.

Clicco su Annulla e incollo il comando TSQL per eseguire una riduzione nel database AdventureWorks2017. Per eseguire ancora una volta il comando, si tratta di un comando di riduzione del file. Si tratta del log di AdventureWorks2017 ed è possibile specificare le dimensioni desiderate che sia tale registro.

In questo caso, l'impostazione predefinita è zero. Se non si definisce nulla qui, o si lascia il valore come zero, si tenterà di ridurre il file alle dimensioni originali. Abbiamo l'opzione "truncateonly" che consiste nel restituire spazio libero all'interno del registro delle transazioni al sistema operativo.

Mentre lo eseguiamo, abbiamo un report di ciò che ha fatto e delle dimensioni attuali. Quindi è andato avanti e l'ha ridotto a circa un mega, il che è fantastico per noi. Ci è stato restituito molto più spazio su disco aggiuntivo. Quindi, una delle cose che vogliamo anche discutere in questo video è cosa succede se eseguiamo il file di compattazione e non viene eseguito correttamente.

Il motivo principale per cui l'operazione di riduzione file non riesce è perché non c'è spazio libero sufficiente all'interno del database per eseguire il comando di riduzione del file. La prima cosa che vorremo fare è capire la quantità di spazio libero all'interno del file.

Come abbiamo visto nel menu della GUI, possiamo anche eseguire questo particolare comando: ora abbiamo cambiato focus qui, stiamo lavorando con il database AdventureWorks2019. L'esecuzione di questo particolare comando, il comando select, fornisce la quantità di spazio libero visibile nel database.

Attualmente stiamo vedendo tre mega di spazio libero all'interno del database. Esistono due opzioni diverse che potremmo usare per provare a ridurre il database nel caso in cui abbiamo esaurito lo spazio libero all'interno di quel particolare database. Possiamo modificare l'opzione, l'opzione di ripristino, per il database di Adventure Works 2019.

Se modifichiamo l'opzione di ripristino in "simple" ed eseguiamo il comando dbcc shrink file, il file dovrebbe essere ridotto a una dimensione molto più piccola di quella originale. Ogni volta che si imposta il database in modalità semplice, il registro delle transazioni si occupa delle transazioni attive e tutto ciò che non è attivo viene eliminato dal registro.

È possibile eseguire questi comandi specifici. La maggior parte dei database di produzione sarà in ripristino completo, quindi passare a simple ci consente, o almeno ci dà la possibilità, di eseguire correttamente il comando di riduzione del file. Quindi siamo in modalità semplice e ancora una volta il nostro scenario è che non siamo riusciti a eseguirlo correttamente.

Dopo essere passati alla modalità semplice, eseguiamo con successo l'operazione di compattazione del file di sincronizzazione, che ci fornisce le informazioni sulla nuova dimensione del file e l'ultima cosa che vorremmo fare in quel caso particolare è rimetterlo in modalità di ripristino completo.

A questo punto, abbiamo preso un file di log transazionale ingestibile in termini di comando shrink file e lo abbiamo portato a uno stato in cui siamo in grado di ridurre quel file e lo abbiamo riportato in una modalità in cui ora può essere eseguito il backup per assicurarci di ottenere le informazioni sulle transazioni più recenti.

Con questo si conclude la demo su come ridurre un log delle transazioni del database Microsoft SQL Server.

Grazie per l'attenzione.