Perché il registro delle transazioni continua a crescere o a esaurire lo spazio?


207

Questa sembra essere una domanda comune nella maggior parte dei forum e in tutto il web, qui viene chiesto in molti formati che suonano in genere in questo modo:

In SQL Server -

  • Quali sono alcuni motivi per cui il registro delle transazioni diventa così grande?
  • Perché il mio file di registro è così grande?
  • Quali sono alcuni modi per prevenire questo problema?
  • Cosa faccio quando mi trovo in pista con la causa sottostante e voglio mettere il mio file di registro delle transazioni in buone condizioni?
  0

La risposta veramente breve è: mettere il database in modalità ** Semplice ** (non in modalità ** Completa).Se non esegui più backup del log delle transazioni durante il giorno tra il backup notturno completo: non è necessaria la modalità ** Completa **. 07 dic. 162016-12-07 14:48:40

  0

@ IanBoyd - Di sicuro questa è la risposta più semplice.La chiave, tuttavia, è arrivare a ciò che significa.Ci ho pensato su nella risposta.Tristemente troppe persone o non si rivolgono mai a questo o semplicemente passare alla semplice senza capire perché.Modificherò la mia risposta per colpire la modalità semplice un po 'prima, anche se .. 07 dic. 162016-12-07 14:53:55

270

Una risposta più breve:

Probabilmente hai una transazione in esecuzione da molto tempo (manutenzione degli indici? Big batch delete o update?) O sei nella modalità di ripristino "predefinita" (più in basso su cosa si intende per impostazione predefinita) diFulle non ho preso unregistro di backup(o non li stanno prendendo abbastanza frequentemente).

Se si tratta di un problema relativo al modello di recupero, la risposta semplice potrebbe essere Passa aSimplemodalità di ripristino se non è necessario il ripristino puntuale e i backup regolari del registro.Molte persone, tuttavia, fanno sì che la loro risposta non comprenda modelli di recupero.Continua a leggere per capire perché è importante e poi decidere cosa fai.Potresti anche iniziare a prendere i backup del log e rimanere dentroFullrecupero.

Potrebbero esserci altri motivi, ma questi sono i più comuni.Questa risposta inizia a tuffarsi nei due motivi più comuni e ti dà alcune informazioni di base sul perché e come dietro i motivi, oltre a esplorare altre ragioni.


Una risposta più lunga:Quali scenari possono far sì che il registro continui a crescere?Ci sono molte ragioni, ma in genere questi motivi sono dei seguenti due modelli: C'è un equivoco sui modelli di recupero o ci sono transazioni a lungo termine.Continua a leggere per i dettagli.

Motivo principale 1/2: non comprendere i modelli di recupero

(Essendo inModalità di recupero completoe non prendereRegistra i backup- Questa è la ragione più comune - la stragrande maggioranza di coloro che hanno riscontrato questo problema sono.)

Anche se questa risposta non è una profonda immersione nei modelli di recupero di SQL Server, l'argomento dei modelli di recupero è fondamentale per questo problema.

In SQL Server ce ne sono trerecovery models:

  • Full,
  • Bulk-Loggede
  • Simple.

IgnoreremoBulk-Loggedper ora diremo che è un modello ibrido e la maggior parte delle persone che fanno parte di questo modello sono lì per un motivo e comprendono modelli di recupero.

I due a cui teniamo e la loro confusione sono la causa della maggior parte dei casi di persone che hanno questo problemaSimpleeFull.

Intermission: recupero in generale

Prima di parlare dei modelli di recupero: parliamo del recupero in generale.Se vuoi approfondire ulteriormente questo argomento, leggiPaul Randal's bloge tanti messaggi su di esso che vuoi.Per questa domanda, però:

  1. Crash/Restart Recovery
    Uno scopo del file di registro delle transazioni è perarresto anomalo/riavvio recupero.Per il rollforward e il rollback del lavoro che è stato fatto (rolling forward/redo) prima di un arresto anomalo o riavvio e il lavoro che è stato avviato ma non terminato dopo un arresto anomalo o riavvio (rollback/annullamento).È il lavoro del log delle transazioni per vedere che una transazione è iniziata ma non è mai stata completata (rollback o crash/restart prima della transazione).In quella situazione è il lavoro del tronco da dire"Ehi .. questo non è mai veramente finito, torniamo indietro"durante il recupero.È anche compito del registro vedere che hai finito qualcosa e che alla tua applicazione client è stato detto che è stata completata (anche se non si è ancora consolidata nel tuo file di dati) e dire"Ehi .. è successo davvero, facciamolo andare avanti, facciamo in modo che le applicazioni pensino che fosse"dopo un riavvio.Ora c'è di più ma questo è lo scopo principale.

  2. Punto in tempo di recupero
    L'altro scopo per un file di registro delle transazioni è di essere in grado di darci la possibilità di ripristinare apunto nel tempoa causa di un "oops" in un database o per garantire un punto di ripristino in caso di un guasto hardware che coinvolge i dati e/o file di registro di un database.Se questo registro delle transazioni contiene i record delle transazioni che sono state avviate e completate per il ripristino, SQL Server può e quindi utilizza queste informazioni per ottenere un database nel punto in cui si trovava prima che si verificasse un problema.Ma non è sempre un'opzione disponibile per noi.Per farlo funzionare dobbiamo avere il nostro database nel giustomodello di recuperoe dobbiamo prendereregistrare i backup.

Modelli di recupero

Sui modelli di recupero:

  • Modello di recupero semplice
    Quindi, con l'introduzione sopra, è più facile parlareSimple Recoveryprima il modello.In questo modello, stai dicendo a SQL Server:"Sto bene con te usando il tuo file di registro delle transazioni per crash e riavvio recupero ..."(Non hai davvero scelta lì. Guarda in altoACID propertiese questo dovrebbe avere un senso rapidamente.)"... ma una volta che non ne hai più bisogno per quel crash/riavvio scopo di recupero, vai avanti e riutilizzare il file di registro."

    SQL Server ascolta questa richiesta in Simple Recovery e conserva solo le informazioni di cui ha bisogno per eseguire il crash/riavvio del ripristino.Una volta che SQL Server è sicuro che può essere ripristinato perché i dati sono induriti nel file di dati (più o meno), i dati che sono stati rafforzati non sono più necessari nel log e sono contrassegnati per il troncamento, il che significa che vengono riutilizzati.

  • Modello di recupero completo
    ConFull Recovery, si sta dicendo a SQL Server che si desidera essere in grado di eseguire il ripristino in un momento specifico, purché il file di registro sia disponibile o in un momento specifico coperto da un backup del registro.In questo caso, quando SQL Server raggiunge il punto in cui sarebbe sicuro troncare il file di registro in Simple Recovery Model, non lo farà.AnzichéPermette al file di registro di continuare a cresceree gli permetterà di continuare a crescere,finché non si esegue un backup del registro(o esaurito lo spazio sull'unità del file di registro) in circostanze normali.

Passare da semplice a pieno ha un Gotcha.

Ci sono regole ed eccezioni qui.Di seguito parleremo delle transazioni a lungo termine.

Ma un avvertimento da tenere a mente per la modalità di recupero completo è questo: se si passa aFull Recoverymodalità, ma mai fare un backup completo iniziale, lo farà SQL Servernononora la tua richiesta di essere presenteFull Recoverymodello.Il registro delle transazioni continuerà a funzionare come nelSimplefino a quando non si passa al modello di recupero completo e si prende il primoFull Backup.

Il modello di recupero completo senza backup del log non è corretto.

Quindi, questa è la ragione più comune per la crescita incontrollata del registro?Risposta: Essere in modalità di recupero completo senza avere alcun backup di registro.

Questo succedetuttiil tempo per le persone.

Perché questo è un errore così comune?

Perché succede sempre?Poiché ogni nuovo database ottiene le impostazioni iniziali del modello di recupero esaminando il database del modello.

L'impostazione del modello di recupero iniziale del modello è sempreFull Recovery Model- fino a quando ea meno che qualcuno lo cambi.Quindi potresti dire che "il modello di recupero predefinito" èFull.Molte persone non ne sono a conoscenza e hanno i loro database in esecuzioneFull Recovery Modelsenza backup di log e quindi un file di registro delle transazioni molto più grande del necessario.Questo è il motivo per cui è importante modificare i valori predefiniti quando non funzionano per la tua organizzazione e le sue esigenze)

Il modello di recupero completo con troppo pochi backup del log non è corretto.

Puoi anche metterti nei guai qui non tenendo abbastanza spesso i backup dei log.
L'esecuzione di un backup del log al giorno può sembrare un problema, ma richiede un ripristino con meno comandi di ripristino, ma tenendo presente la discussione precedente, il file di registro continuerà a crescere e crescere fino a quando non si eseguono i backup del log.

Come faccio a sapere di quale frequenza di backup del registro ho bisogno?

È necessario considerare la frequenza di backup del registro tenendo presenti due aspetti:

  1. Bisogni di recupero- Si spera che questo sia il primo.Nel caso in cui l'unità contenente il registro delle transazioni non funzioni correttamente o si verifichi un danneggiamento grave che influisce sul backup del registro, quanti dati possono essere persi?Se il numero non supera i 10-15 minuti, è necessario eseguire il backup del registro ogni 10-15 minuti, fine della discussione.
  2. Registra la crescita- Se la tua organizzazione sta andando bene a perdere più dati a causa della possibilità di ricreare facilmente quel giorno, potresti avere un backup del registro molto meno frequente di 15 minuti.Forse la tua organizzazione sta bene ogni 4 ore.Ma devi vedere quante transazioni generate in 4 ore.La possibilità che il registro continui a crescere in quelle quattro ore rende troppo grande un file di registro?Ciò significa che i backup del log richiedono troppo tempo?

Motivo principale 2/2: Transazioni lunghe

("Il mio modello di recupero va bene! Il registro sta ancora crescendo!)

Questo può anche essere una causa di crescita del log incontrollata e sfrenata.Non importa il modello di recupero, ma spesso si presenta come"Ma sono in Simple Recovery Model - perché il mio registro continua a crescere ?!"

Il motivo è semplice: se SQL sta utilizzando questo log delle transazioni per scopi di recupero come descritto sopra, allora deve ricominciare dall'inizio di una transazione.

Se una transazione richiede molto tempo o esegue molte modifiche, il registro non può troncare sul checkpoint per nessuna delle modifiche ancora presenti nelle transazioni aperte o avviate da quella transazione.

Ciò significa che una grande eliminazione, l'eliminazione di milioni di righe in una istruzione di eliminazione è una transazione e il registro non può eseguire troncamenti finché non viene eseguita l'eliminazione completa.NelFull Recovery Model, questa eliminazione viene registrata e potrebbero essere presenti molti record di registro.Stessa cosa con il lavoro di ottimizzazione dell'indice durante le finestre di manutenzione.Significa anche che una cattiva gestione delle transazioni e la mancata osservanza e chiusura di transazioni aperte possono davvero danneggiare te e il tuo file di registro.

Cosa posso fare per queste transazioni a lungo termine?

Puoi salvarti qui da:

  • Dimensionamento corretto del file di registro per tenere conto dello scenario peggiore, ad esempio la manutenzione o le operazioni note più importanti.E quando cresci il tuo file di log dovresti guardare a questoguidance(e i due link che ti invia) di Kimberly Tripp.Il giusto dimensionamento è super critico qui.
  • Guardando il tuo utilizzo delle transazioni.Non avviare una transazione nel server delle applicazioni e iniziare lunghe conversazioni con SQL Server e rischiare di lasciarne aperta una troppo lunga.
  • Guardando iltransazioni implicitenelle tue istruzioni DML.Per esempio:UPDATE TableName Set Col1 = 'New Value'è una transazione.Non ho messo unBEGIN TRANlì e io non devo, è ancora una transazione che si impegna automaticamente quando fatto.Pertanto, se si eseguono operazioni su un numero elevato di righe, considerare la possibilità di raggruppare tali operazioni in blocchi più gestibili e fornire il tempo di registrazione per il ripristino.Oppure considera la giusta dimensione per affrontarlo.O forse esaminare i modelli di recupero che cambiano durante una finestra di caricamento di massa.

Queste due ragioni si applicano anche alla spedizione dei log?

Risposta breve: sì.Risposta più lunga di seguito.

Domanda:"Sto utilizzando la distribuzione dei log, quindi i miei backup dei log sono automatizzati ... Perché continuo a vedere la crescita del log delle transazioni?"

Risposta: continua a leggere.

Cos'è il log shipping?

La distribuzione dei log è proprio come sembra - stai spedendo i tuoi backup del log delle transazioni su un altro server per scopi di DR.C'è un po 'di inizializzazione, ma dopo questo il processo è abbastanza semplice:

  • Un lavoro per il backup del log su un server,
  • un lavoro per copiare quel backup del registro e
  • un lavoro per ripristinarlo senza recupero (neancheNORECOVERYoSTANDBY) sul server di destinazione.

Ci sono anche alcuni lavori da monitorare e avvisare se le cose non vanno come le hai pianificate.

In alcuni casi, è possibile eseguire il ripristino del log shipping solo una volta al giorno o ogni tre giorni o una volta alla settimana.Questo va bene.Ma se si apporta questa modifica a tutti i lavori (compresi i processi di backup e copia del registro), significa che si sta aspettando tutto il tempo necessario per eseguire un backup del registro.Ciò significa che avrai un sacco di crescita dei log, perché lo seiin modalità di recupero completo senza backup del registro- e probabilmente significa anche un grande file di log da copiare.È necessario modificare solo la pianificazione del processo di ripristino e lasciare che i backup e le copie del registro avvengano su una base più frequente, altrimenti si risentirebbe del primo problema descritto in questa risposta.


Risoluzione dei problemi generali tramite codici di stato

Ci sono ragioni diverse da queste due, ma queste sono le più comuni.Indipendentemente dalla causa: c'è un modo per analizzare la ragione di questa crescita del log inspiegabile/mancanza di troncamenti e vedere cosa sono.

Con l'interrogazione delsys.databasesvisualizzazione del catalogo è possibile visualizzare le informazioni che descrivono il motivo per cui il file di registro potrebbe essere in attesa di troncare/riutilizzare.

C'è una colonna chiamatalog_reuse_waitcon un ID di ricerca del codice motivo e alog_reuse_wait_desccolonna con una descrizione del motivo di attesa.Dall'articolo in linea dei libri di riferimento sono la maggior parte dei motivi (quelli che è probabile vedere e quelli che possiamo spiegare i motivi. I missing sono fuori uso o per uso interno) con alcune note sull'attesa incorsivo:

  • 0 = Niente
    Quello che sembra .. Non dovrebbe essere in attesa

  • 1 = Checkpoint
    In attesa che si verifichi un checkpoint.Questo dovrebbe accadere e dovresti stare bene - ma ci sono alcuni casi da cercare qui per risposte successive o modifiche.

  • 2 = backup del registro
    Stai aspettando che si verifichi un backup del log.O li hai programmati e succederà presto, o hai il primo problema descritto qui e ora sai come risolverlo

  • 3 = backup o ripristino attivo
    Un'operazione di backup o ripristino è in esecuzione sul database

  • 4 = Transazione attiva
    C'è una transazione attiva che deve essere completata (in entrambi i casi -ROLLBACKoCOMMIT) prima che il log possa essere sottoposto a backup.Questa è la seconda ragione descritta in questa risposta.

  • 5 = mirroring del database
    O uno specchio si trova dietro o sotto una certa latenza in una situazione di mirroring ad alte prestazioni o il mirroring viene messo in pausa per qualche motivo

  • 6 = Replica
    Ci possono essere problemi con la replica che causerebbero questo - come un agente di log reader non in esecuzione, un database che pensa che sia contrassegnato per la replica che non è più e vari altri motivi.Puoi anche vedere questo motivo ed è perfettamente normale perché stai guardando al momento giusto, proprio come le transazioni vengono consumate dal log reader

  • 7 = Creazione di snapshot del database
    Stai creando un'istantanea del database, lo vedrai se guardi al momento giusto mentre viene creata un'istantanea

  • 8 = Scansione registro
    Devo ancora incontrare un problema con questo che corre da sempre.Se si guarda abbastanza a lungo e abbastanza frequentemente è possibile vedere che ciò accada, ma non dovrebbe essere una causa di una crescita eccessiva del registro delle transazioni, che ho visto.

  • 9 = Una replica secondaria di Gruppi di disponibilità AlwaysOn applica i record del registro delle transazioni di questo database a un database secondario corrispondente.A proposito della descrizione più chiara ancora ..

+1

La divisione delle pagine aumenterà la registrazione.Una ragione significativa (nella mia esperienza) che non è stata menzionata per una crescita elevata che potrebbe richiedere frequentemente un restringimento, risolta in molti dei miei casi, sarebbe quella di utilizzare le scelte di indice appropriate, compreso il corretto FillFactor mgmt.Io uso le seguenti impostazioni, con osservazione ravvicinata.Impostazioni FF: (0/100) tabelle con letture alte/basse, (90) per leggermente modificate, (80) medie/basse, (70) alte scritture, (60) Raggiungo difficilmente questo livello o qualcos'altro potrebbe essere sbagliato.Utilizzare quindi indicizzare correttamente le pianificazioni di gestione corrispondenti al volume di dati. 08 ott. 152015-10-08 19:31:03


98

Dal momento che non sono veramente soddisfatto di nessuna delle risposteover on Stack Overflow, compreso il suggerimento più pesantemente votato, e poiché ci sono alcune cose che vorrei rispondere a quella risposta di Mike, non ho pensato di fornire il mio contributo anche qui.Ho messo una copia di questa risposta anche lì.

Rendere più piccolo un file di registro dovrebbe essere riservato agli scenari in cui ha riscontrato una crescita inaspettata che non ti aspetti che succeda di nuovo.Se il file di registro tornerà a essere di dimensioni uguali, non si otterrà molto da ridurre temporaneamente.Ora, a seconda degli obiettivi di recupero del database, queste sono le azioni da intraprendere.

Innanzitutto, fai un backup completo

Non apportare mai modifiche al database senza assicurarsi di poterlo ripristinare in caso di problemi.

Se ti interessa il recupero point-in-time

(E con il recupero puntuale, voglio dire che ti interessa essere in grado di ripristinare qualcosa di diverso da un backup completo o differenziale.)

Presumibilmente il tuo database è inFULLmodalità di ripristino.In caso contrario, assicurati che sia:

ALTER DATABASE yourdb SET RECOVERY FULL;

Anche se si stanno eseguendo regolari backup completi, il file di registro crescerà e crescerà fino a quando non si esegue unceppobackup - questo è per la tua protezione, per non consumare inutilmente il tuo spazio su disco.Dovresti eseguire questi backup del log abbastanza frequentemente, in base ai tuoi obiettivi di recupero.Ad esempio, se si dispone di una regola aziendale che stabilisce che è possibile permettersi di perdere almeno 15 minuti di dati in caso di emergenza, è necessario eseguire un processo di backup del log ogni 15 minuti.Ecco uno script che genererà nomi di file con data e ora basati sull'ora corrente (ma è possibile farlo anche con piani di manutenzione ecc., Semplicemente non scegliere nessuna delle opzioni di restringimento nei piani di manutenzione, sono orribili).

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\yourdb_' 
    + CONVERT(CHAR(8), GETDATE(), 112) + '_'
    + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
    + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

Nota che\\backup_share\dovrebbe essere su una macchina diversa che rappresenta un diverso dispositivo di archiviazione sottostante.Il backup di questi sulla stessa macchina (o su una macchina diversa che utilizza gli stessi dischi sottostanti, o una VM diversa che si trova sullo stesso host fisico) in realtà non ti aiuta, poiché se la macchina esplode, hai perso il tuo databaseei suoi backup.A seconda dell'infrastruttura di rete, può essere più logico eseguire il backup localmente e quindi trasferirli in una posizione diversa dietro le quinte;in entrambi i casi, si desidera rimuoverli dalla macchina del database principale il più rapidamente possibile.

Ora, una volta che si eseguono regolarmente i backup del log, dovrebbe essere ragionevole ridurre il file di registro a qualcosa di più ragionevole di qualsiasi altra cosa sia saltata in aria.Questo fanonsignifica correreSHRINKFILEpiù e più volte fino a quando il file di registro è 1 MB, anche se si esegue il backup del registro frequentemente, deve comunque contenere la somma di tutte le transazioni simultanee che possono verificarsi.Gli eventi autogrow del file di registro sono costosi, poiché SQL Server deve azzerare i file (diversamente dai file di dati quando l'inizializzazione del file istantaneo è abilitata) e le transazioni dell'utente devono attendere mentre ciò accade.Vuoi fare questa routine di restringimento-crescita-contrazione-crescita il meno possibile, e certamente non vuoi che i tuoi utenti paghino per questo.

Si noti che potrebbe essere necessario eseguire il backup del registro due volte prima che uno strizzacervelli sia possibile (grazie Robert).

Quindi, devi trovare una dimensione pratica per il tuo file di registro.Nessuno qui può dirti di cosa si tratta senza sapere molto di più sul tuo sistema, ma se hai frequentemente ridotto il file di registro ed è nuovamente cresciuto, una buona filigrana è probabilmente del 10-50% più alta della più grande che sia stata .Diciamo che arriva a 200 MB e vuoi che gli eventi successivi di aumento automatico siano di 50 MB, quindi puoi regolare le dimensioni del file di registro in questo modo:

USE [master];
GO
ALTER DATABASE Test1 
    MODIFY FILE
    (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

Si noti che se il file di registro è attualmente> 200 MB, potrebbe essere necessario eseguire prima questo:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

Se non ti interessa il recupero point-in-time

Se si tratta di un database di test e non ti interessa il recupero temporizzato, devi assicurarti che il tuo database siaSIMPLEmodalità di ripristino.

ALTER DATABASE yourdb SET RECOVERY SIMPLE;

Mettendo il database inSIMPLEmodalità di ripristino si assicurerà che SQL Server riutilizza parti del file di registro (essenzialmente eliminando gradualmente le transazioni inattive) anziché crescere per mantenere un record dituttitransazioni (comeFULLil recupero funziona finché non si esegue il backup del registro).CHECKPOINTgli eventi contribuiranno a controllare il log e assicurarsi che non abbia bisogno di crescere a meno che non venga generata una grande quantità di attività di t-logCHECKPOINTS.

Successivamente, dovresti essere assolutamente sicuro che questa crescita del log sia stata veramente dovuta a un evento anormale (ad esempio, una pulizia annuale di primavera o la ricostruzione dei tuoi indici più grandi), e non a causa del normale uso quotidiano.Se si riduce il file di registro a una dimensione ridicolmente piccola, e SQL Server deve solo crescere di nuovo per adattarsi alla normale attività, cosa hai ottenuto?Sei riuscito a utilizzare lo spazio su disco che hai liberato solo temporaneamente?Se hai bisogno di una correzione immediata, puoi eseguire quanto segue:

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
GO

Altrimenti, imposta una dimensione e un tasso di crescita appropriati.Come nell'esempio nel caso di recupero temporizzato, è possibile utilizzare lo stesso codice e la stessa logica per determinare quale dimensione del file è appropriata e impostare parametri di autogrowth ragionevoli.

Alcune cose che non vuoi fare

  • Eseguire il backup del registro conTRUNCATE_ONLYopzione e poiSHRINKFILE.Per uno, questoTRUNCATE_ONLYl'opzione è stata deprecata e non è più disponibile nelle versioni correnti di SQL Server.Secondo, se ci seiFULLmodello di recupero, questo distruggerà la catena di log e richiederà un nuovo backup completo.

  • Scollegare il database, eliminare il file di registro e ricollegarlo.Non posso sottolineare quanto possa essere pericoloso.Il tuo database potrebbe non essere ripristinato, potrebbe sorgere il sospetto, potresti dover ripristinare un backup (se ne hai uno), ecc. Ecc.

  • Utilizzare l'opzione "Shrink database".DBCC SHRINKDATABASEe l'opzione del piano di manutenzione per fare lo stesso sono idee sbagliate, specialmente se si ha davvero bisogno solo di risolvere un problema di log.Scegli come target il file che desideri regolare e regolalo in modo indipendente, usandoDBCC SHRINKFILEoALTER DATABASE ... MODIFY FILE(esempi sopra).

  • Riduci il file di registro a 1 MB.Questo sembra allettante perché, hey, SQL Server mi permetterà di farlo in determinati scenari, e guarderà tutto lo spazio che libera!A meno che il tuo database sia di sola lettura (e lo è, dovresti contrassegnarlo come tale usandoALTER DATABASE), questo porterà assolutamente a molti eventi di crescita non necessari, in quanto il log deve contenere le transazioni correnti indipendentemente dal modello di recupero.Qual è lo scopo di liberare temporaneamente quello spazio, solo così SQL Server può riprenderlo lentamente e dolorosamente?

  • Creare un secondo file di registro.Ciò fornirà un sollievo temporaneo per l'unità che ha riempito il disco, ma questo è come provare a riparare un polmone perforato con un cerotto.Dovresti gestire direttamente il file di registro problematico invece di aggiungere un altro potenziale problema.A parte il reindirizzamento di alcune attività del registro delle transazioni su un'unità diversa, un secondo file di registro non fa realmente nulla per te (diversamente da un secondo file di dati), poiché solo uno dei file può essere utilizzato in qualsiasi momento.Paul Randal also explains why multiple log files can bite you later.

Sii proattivo

Invece di ridurre il numero di file di registro a una piccola quantità e lasciarlo aumentare automaticamente a una velocità ridotta, impostarlo su dimensioni ragionevolmente grandi (uno che soddisferà la somma della più grande serie di transazioni simultanee) e impostare un aumento di volume ragionevole impostazione come fallback, in modo che non debba crescere più volte per soddisfare singole transazioni e in modo che sia relativamente raro che debba crescere durante le normali operazioni commerciali.

Le peggiori impostazioni possibili sono una crescita di 1 MB o una crescita del 10%.Abbastanza divertente, questi sono i valori predefiniti per SQL Server (di cui mi sono lamentato easked for changes to no avail) - 1 MB per i file di dati e il 10% per i file di registro.Il primo è troppo piccolo al giorno d'oggi, e il secondo porta ogni volta a eventi sempre più lunghi (ad esempio, il file di registro è di 500 MB, la prima crescita è di 50 MB, la crescita successiva è di 55 MB, la crescita successiva è di 60,5 MB ecc. ecc. - e sull'I/O lento, credetemi, noterete davvero questa curva).

Ulteriori letture

Per favore non fermarti qui;mentre gran parte dei consigli che si vedono in merito alla riduzione dei file di registro è intrinsecamente grave e persino potenzialmente disastrosa, ci sono alcune persone che si preoccupano più dell'integrità dei dati che della liberazione dello spazio su disco.


21

Puoi anche vedere il contenuto del tuo file di registro.A tale scopo, è possibile utilizzare fn_dblog non documentato o un lettore di registri delle transazioni, ad esempio ApexSQL Log .

Non mostra la riorganizzazione dell'indice, ma mostra tutto il DML e vari eventi DDL: ALTER , CREATE , DROP , attivazione/disattivazione del trigger, autorizzazioni di concessione/revoca, rinominazione dell'oggetto.

ApexSQLLogProject.temp - ApexSQL.log

Dichiarazione di non responsabilità: lavoro per ApexSQL come tecnico di supporto


1

Questo è il problema più frequente per quasi tutti i DBA in cui i registri crescono e riempiono il disco.

• Quali sono alcuni motivi per cui il registro delle transazioni diventa così grande?

  1. Transazione attiva lunga
  2. Elevate transazioni di registrazione come la ricostruzione degli indici, riorganizzare, inserire in blocco, eliminare ecc.
  3. Qualsiasi HA come Replication, Mirroring configurato che detiene il log e non consente di rilasciare lo spazio del log

• Perché il mio file di registro è così grande?

Controlla illog_reuse_wait_desc colonna insys.databasestabella per sapere cosa sta trattenendo i registri dal troncamento:

select name, log_reuse_wait_desc 
from sys.databases

• Quali sono alcuni modi per prevenire questo problema?

I backup del registro ti aiuteranno a controllare la crescita del registro, a meno che non ci sia qualcosa che trattiene i registri dal riutilizzo.

• Cosa faccio quando mi trovo in pista con la causa sottostante e voglio mettere il mio file di registro delle transazioni in buone condizioni?

Se hai identificato ciò che effettivamente lo sta causando, prova a risolverlo di conseguenza come spiegato nella pagina seguente.

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

Avere pianificato backup dei log appropriati è il modo migliore per gestire la crescita dei registri, a meno che non si tratti di una situazione insolita.