sviluppo-web-qa.it

Come posso ridurre le dimensioni del backup del registro delle transazioni dopo un backup completo?

Ho tre piani di manutenzione impostati per l'esecuzione su un'istanza di SQL Server 2005:

  • Ottimizzazioni settimanali del database seguite da un backup completo
  • Backup differenziale giornaliero
  • Backup del registro delle transazioni orarie

I backup dei registri orari sono in genere compresi tra poche centinaia Kb e 10Mb a seconda del livello di attività, i differenziali giornalieri di solito aumentano a circa 250 Mb entro la fine della settimana e il backup settimanale è di circa 3,5 Gb.

Il problema che ho è che le ottimizzazioni prima del backup completo sembrano causare un aumento del backup del registro delle transazioni successivo di oltre il doppio della dimensione del backup completo, in questo caso 8 GB, prima di tornare alla normalità.

Altro che BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY, esiste un modo per ridurre le dimensioni del backup del registro o impedire che le ottimizzazioni vengano registrate nel registro delle transazioni, poiché verranno sicuramente prese in considerazione nel backup completo che precedono?

38
Dave

Alcuni suggerimenti interessanti qui, che sembrano mostrare incomprensioni su come funzionano i backup dei log. Un backup del log contiene TUTTI i log delle transazioni generati dal backup del log precedente, indipendentemente da quali backup completi o differenziali sono stati eseguiti nel frattempo. L'arresto dei backup del registro o il passaggio a backup completi giornalieri non avrà alcun effetto sulle dimensioni del backup del registro. L'unica cosa che influisce sul registro delle transazioni è un backup del registro, una volta avviata la catena di backup del registro.

L'unica eccezione a questa regola è se la catena di backup del log è stata interrotta (ad es. Accedendo al modello di recupero SEMPLICE, ripristinando un'istantanea del database, troncando il log utilizzando BACKUP LOG WITH NO_LOG/TRUNCATE_ONLY), nel qual caso il primo backup del log conterrà tutto il registro delle transazioni dall'ultimo backup completo, che riavvia la catena di backup del registro; o se la catena di backup del log non è stata avviata - quando si passa a FULL per la prima volta, si opera in una sorta di modello di recupero pseudo-SEMPLICE fino a quando non viene eseguito il primo backup completo.

Per rispondere alla tua domanda originale, senza entrare nel modello di recupero SEMPLICE, dovrai risucchiare il backup di tutto il registro delle transazioni. A seconda delle azioni intraprese, è possibile eseguire backup del registro più frequenti per ridurne le dimensioni o eseguire database più mirati.

Se riesci a pubblicare alcune informazioni sulle operazioni di manutenzione che stai eseguendo, posso aiutarti a ottimizzarle. Per caso, stai eseguendo ricostruzioni dell'indice seguite da un database di ridimensionamento per recuperare lo spazio utilizzato dalle ricostruzioni dell'indice?

Se non ci sono altre attività nel database mentre è in corso la manutenzione, è possibile effettuare le seguenti operazioni:

  • assicurarsi che l'attività dell'utente sia interrotta
  • eseguire un backup del registro finale (ciò consente di ripristinare fino al punto di inizio della manutenzione)
    • passare al modello di recupero SEMPLICE
    • eseguire la manutenzione: il registro verrà troncato su ciascun checkpoint
    • passare al modello di recupero COMPLETO e eseguire un backup completo
    • continua normalmente

Spero che questo aiuti - non vedo l'ora di maggiori informazioni.

Grazie

[Modifica: dopo tutta la discussione sul fatto che un backup completo possa modificare le dimensioni di un successivo backup del registro (non può) ho messo insieme un post completo sul blog con materiale di base e uno script che lo dimostra. Dai un'occhiata a https://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/]

35
Paul Randal

Potresti ridurli, ma cresceranno di nuovo, causando infine la frammentazione del disco. Le ricostruzioni e le deframmentazioni dell'indice creano registri delle transazioni molto grandi. Se non è necessaria la recuperabilità temporizzata, è possibile passare alla modalità di ripristino Semplice e eliminare completamente i backup del registro delle transazioni.

Immagino che tu stia usando un piano di manutenzione per le ottimizzazioni, potresti cambiarlo per usare uno script che fa deframmentare l'indice solo quando viene raggiunto un certo livello di frammentazione e probabilmente non subiresti alcun danno alle prestazioni. Ciò genererebbe registri molto più piccoli.

Vorrei saltare i differenziali giornalieri a favore dei backup completi giornalieri BTW.

5
SqlACID

La tua ultima domanda era: "A parte il BACKUP LOG WITH TRUNCATE_ONLY, esiste un modo per ridurre le dimensioni del backup del log o impedire che le ottimizzazioni vengano registrate nel registro delle transazioni, poiché sicuramente verranno contabilizzate perché nel backup completo precedono? "

No, ma ecco una soluzione alternativa. Se si sa che le uniche attività in quel database in quel momento saranno i lavori di manutenzione dell'indice, è possibile interrompere i backup del registro delle transazioni prima che inizi la manutenzione dell'indice. Ad esempio, alcuni dei miei server il sabato sera, le pianificazioni dei lavori sono così:

  • 9:30 PM - viene eseguito il backup del registro delle transazioni.
  • 9:45 PM - il backup del registro delle transazioni viene eseguito per l'ultima volta. La pianificazione si interrompe alle 9:59.
  • 10:00 PM - il processo di manutenzione dell'indice inizia e termina prima delle 11:30.
  • 11:30 PM - il processo di backup completo inizia e termina in meno di 30 minuti.
  • 12:00 - i backup del registro delle transazioni ricominciano ogni 15 minuti.

Ciò significa che non ho recuperabilità temporizzata tra le 21:45 e le 23:30, ma il risultato è una prestazione più veloce.

3
Brent Ozar

Risposta semplice: modifica il lavoro di ottimizzazione settimanale per eseguirlo in modo più equilibrato su base notturna. ad es. riindicizzare le tabelle a-e di domenica sera, f - l di lunedì sera ecc ... trovare un buon equilibrio, il registro sarà in media circa 1/6 della dimensione. Ovviamente funziona meglio se non si utilizza il lavoro di manutenzione dell'indice ssis integrato.

Il rovescio della medaglia a questo ed è significativo a seconda del carico che la tua esperienza di database è che provoca il caos con l'ottimizzatore e il riutilizzo dei piani di query.

Ma se tutto ciò che ti interessa è la dimensione del tuo t-log su base settimanale, suddividilo di giorno in giorno o di ora in ora ed esegui i backup di t-log nel mezzo.

3
Jeremy Lowell

È inoltre possibile esaminare uno strumento di terze parti (Litespeed di Quest, SQL Backup di Red Gate, Hyperbac) per ridurre le dimensioni dei backup e dei registri. Possono pagare da soli rapidamente risparmiando nastro.

2
Steve Jones

È possibile eseguire il backup speciale del registro delle transazioni in vari punti durante l'ottimizzazione del database? La dimensione totale dei tronchi t sarebbe la stessa, ma ognuno sarebbe più piccolo, forse aiutandoti in qualche modo.

Puoi fare un'ottimizzazione del database più mirata in modo da creare meno transazioni (qualcuno l'ha menzionato ma non sono sicuro che le implicazioni siano state spiegate). Come tollerare una certa quantità di frammentazione o spazio sprecato per un po '. Se il 40% dei tuoi tavoli è frammentato solo al 5%, non toccarli potrebbe risparmiare un po 'di attività.

2
ErikE

Probabilmente si può presumere che le "ottimizzazioni" includano ricostruzioni di indici. L'esecuzione di queste attività solo su base settimanale può essere accettabile su un database che non presenta molti aggiornamenti e inserimenti, tuttavia se i tuoi dati sono molto fluidi potresti voler fare un paio di cose:

  1. Ricostruisci o riorganizza gli indici ogni notte se il tuo programma lo consente e se l'impatto è accettabile. Quando si eseguono queste attività di manutenzione degli indici notturni, indirizzare solo quegli indici che sono frammentati oltre il 30% per ricostruzioni e nell'intervallo del 15-30% per rimbalzi.

  2. Queste attività sono transazioni registrate, quindi se sei preoccupato per la crescita dei registri, raccomanderei ciò che Paul ha raccomandato. Il backup del registro delle transazioni finale prima della manutenzione dell'indice, passa a Ripristino semplice, seguito dal processo di manutenzione e quindi torna al Ripristino completo seguito da un backup completo dei dati.

Ho un approccio zen ai miei file di registro: hanno le dimensioni che vogliono essere. Finché non hanno subito una crescita aberrante a causa di scarse pratiche di backup rispetto all'attività del database che è il mantra in cui vivo.

Per quanto riguarda gli script che eseguono la manutenzione discrezionale dell'indice, guardano online: ce ne sono un sacco là fuori. Andrew Kelly ne ha pubblicato uno decente su SQL Magazine circa un anno fa. SQLServerPedia ha alcuni script di Michelle Ufford e l'ultimo numero di SQL Magazine (luglio 2009 credo) ha anche un articolo completo sull'argomento. Il punto è trovare quello che funziona bene per te e renderlo tuo con personalizzazioni minime.

2
Tim Ford