with - Come si cancella il log delle transazioni di SQL Server?




transaction log sql server (14)

Esempio:

DBCC SQLPERF(LOGSPACE)

BACKUP LOG Comapny WITH TRUNCATE_ONLY

DBCC SHRINKFILE (Company_log, 500)

DBCC SQLPERF(LOGSPACE)

Non sono un esperto di SQL e mi viene in mente ogni volta che devo fare qualcosa oltre le basi. Ho un database di test di dimensioni non grandi, ma il log delle transazioni lo è sicuramente. Come posso cancellare il registro delle transazioni?


  1. DB di backup
  2. Scollega DB
  3. Rinomina file di registro
  4. Allega DB (durante la connessione rimuovi rinominato .ldf (file di registro). Seleziona e rimuovi premendo il pulsante Rimuovi)
  5. Nuovo file di registro verrà ricreato
  6. Elimina il file di registro rinominato.

Questo funzionerà ma si consiglia di eseguire prima il backup del database.


Alla mia esperienza sulla maggior parte dei server SQL non è presente alcun backup del log delle transazioni. Backup completi o backup differenziali sono una pratica comune, ma i backup del log delle transazioni sono davvero rari. Quindi il file di registro delle transazioni cresce per sempre (fino a quando il disco è pieno). In questo caso il modello di recupero dovrebbe essere impostato su " semplice ". Non dimenticare di modificare anche i database di sistema "model" e "tempdb".

Un backup del database "tempdb" non ha senso, quindi il modello di recupero di questo db dovrebbe sempre essere "semplice".


Database → tasto destro del mouse Proprietà → file → aggiungi un altro file di registro con un nome diverso e imposta il percorso come il vecchio file di registro con un nome file diverso.

Il database preleva automaticamente il file di registro appena creato.


Ecco un modo semplice, molto inelegante e potenzialmente pericoloso .

  1. DB di backup
  2. Scollega DB
  3. Rinomina file di registro
  4. Allega DB
  5. Nuovo file di registro verrà ricreato
  6. Elimina il file di registro rinominato.

Suppongo che tu non stia facendo i backup del log. (Che tronca il log). Il mio consiglio è di cambiare il modello di recupero da full a simple . Ciò impedirà il log bloat.


Il log delle transazioni di SQL Server deve essere gestito correttamente per evitare una crescita indesiderata. Ciò significa eseguire i backup del log delle transazioni abbastanza spesso. Non facendolo, rischi che il log delle transazioni diventi pieno e inizi a crescere.

Oltre alle risposte a questa domanda, consiglio di leggere e comprendere i miti comuni del log delle transazioni. Queste letture possono aiutare a capire il log delle transazioni e decidere quali tecniche utilizzare per "cancellarle":

Dai 10 miti più importanti del log delle transazioni di SQL Server :

Mito: il mio SQL Server è troppo occupato. Non voglio creare backup del log delle transazioni di SQL Server

Una delle operazioni con prestazioni più elevate in SQL Server è un evento di crescita automatica del file di registro delle transazioni online. Non facendo abbastanza spesso i backup del log delle transazioni, il log delle transazioni online diventerà pieno e dovrà crescere. La dimensione di crescita predefinita è del 10%. Più il database è più occupato, più veloce sarà il registro delle transazioni online se i backup del log delle transazioni non vengono creati. La creazione di un backup del log delle transazioni di SQL Server non blocca il log delle transazioni online, ma avviene un evento di auto-crescita. Può bloccare tutte le attività nel registro delle transazioni online

Dai miti dei log delle transazioni :

Mito: la riduzione periodica del registro è una buona pratica di manutenzione

FALSO. La crescita del log è molto costosa perché il nuovo blocco deve essere azzerato. Tutte le attività di scrittura si fermano su quel database fino al termine dell'azzeramento, e se la scrittura del disco è lenta o la dimensione dell'aumento automatico è grande, tale pausa può essere enorme e gli utenti lo noteranno. Questa è una delle ragioni per cui vuoi evitare la crescita. Se riduci il log, questo ricrescerà di nuovo e stai solo sprecando l'operazione del disco in un inutile gioco di differenze inventariali


La maggior parte delle risposte qui finora presuppongono che non sia effettivamente necessario il file Registro transazioni, tuttavia se il database utilizza il modello di recupero FULL e si desidera conservare i backup in caso sia necessario ripristinare il database, quindi non troncare o eliminare il file di registro come suggeriscono molte di queste risposte.

L'eliminazione del file di registro (troncandolo, scartandolo, cancellandolo, ecc.) Interromperà la catena di backup e impedirà il ripristino in qualsiasi momento dopo l'ultimo backup completo, differenziale o del log delle transazioni, fino al successivo completo o il backup differenziale è fatto.

Dall'articolo di Microsoft su BACKUP

Ti consigliamo di non utilizzare mai NO_LOG o TRUNCATE_ONLY per troncare manualmente il log delle transazioni, poiché ciò interrompe la catena di log. Fino al successivo backup completo o differenziale del database, il database non è protetto dall'errore del supporto. Utilizzare il troncamento del registro manuale solo in circostanze molto speciali e creare immediatamente backup dei dati.

Per evitare ciò, eseguire il backup del file di registro sul disco prima di ridurlo. La sintassi sarebbe simile a questa:

BACKUP LOG MyDatabaseName 
TO DISK='C:\DatabaseBackups\MyDatabaseName_backup_2013_01_31_095212_8797154.trn'

DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)

Prova questo:

USE DatabaseName

GO

DBCC SHRINKFILE( TransactionLogName, 1)

BACKUP LOG DatabaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE( TransactionLogName, 1)

GO 

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

Per prima cosa, 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 la recovery point-in-time, voglio dire che ti interessa essere in grado di ripristinare qualcosa di diverso da un backup completo o differenziale.)

Presumibilmente il tuo database è in modalità di recupero FULL . In caso contrario, assicurati che sia:

ALTER DATABASE testdb SET RECOVERY FULL;

Anche se si stanno eseguendo regolari backup completi, il file di registro crescerà e si espanderà fino a quando non si eseguirà un backup del registro : questo è per la vostra protezione, non per consumare inutilmente il vostro 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 afferma che è possibile permettersi di non perdere più di 15 minuti di dati in caso di disastro, è necessario disporre di un lavoro che esegua il backup del registro 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\testdb_' 
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';

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

Si noti che \\backup_share\ deve essere su una macchina diversa che rappresenta una diversa periferica 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 database e i 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. Ciò non significa eseguire SHRINKFILE più e più volte fino a quando il file di registro non è 1 MB, anche se si esegue spesso il backup del log, ma 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-restringimento 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 una riduzione 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 sia in modalità di recupero SIMPLE .

ALTER DATABASE testdb SET RECOVERY SIMPLE;

Mettendo il database in modalità di ripristino SIMPLE si assicurerà che SQL Server riutilizzi porzioni del file di registro (essenzialmente eliminando gradualmente le transazioni inattive) anziché crescere per mantenere un record di tutte le transazioni (come il ripristino FULL finché non si esegue il backup del log) . CHECKPOINT eventi CHECKPOINT aiuteranno a controllare il log e assicurarsi che non abbia bisogno di crescere a meno che non si generi molta attività di t-log tra i punti CHECKPOINT .

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 utilizzo quotidiano. Se si riduce il file di registro a una dimensione ridicolmente piccola, e SQL Server deve solo ricominciare a crescere 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
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
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 con l'opzione TRUNCATE_ONLY e quindi SHRINKFILE . Per uno, questa opzione TRUNCATE_ONLY è stata deprecata e non è più disponibile nelle versioni correnti di SQL Server. In secondo luogo, se si è nel modello di recupero FULL , questo distruggerà la catena di log e richiederà un nuovo backup completo.

  • Scollega il database, elimina il file di registro e ricollegalo . 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 SHRINKDATABASE e 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. Indirizzare il file che si desidera regolare e regolarlo in modo indipendente, utilizzando DBCC SHRINKFILE o ALTER DATABASE ... MODIFY FILE (esempi sopra).

  • Riduci il file di registro a 1 MB . Questo sembra allettante perché, ehi, 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 utilizzando ALTER DATABASE ), questo porterà solo a molti eventi di crescita non necessari, poiché 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 spiega anche perché più file di registro possono morderti in seguito .

Sii proattivo

Invece di ridurre il 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 qui 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 e ho chiesto modifiche senza alcun risultato ): 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 molti dei consigli che si vedono sulla riduzione dei file di registro sono intrinsecamente cattivi e persino potenzialmente disastrosi, ci sono alcune persone che si preoccupano maggiormente dell'integrità dei dati piuttosto che liberare spazio su disco.

Un post sul blog che ho scritto nel 2009, quando ho visto alcuni "ecco come ridurre il file di registro" .

Un post sul blog Brent Ozar ha scritto quattro anni fa, puntando a più risorse, in risposta a un articolo di SQL Server Magazine che non avrebbe dovuto essere pubblicato .

Un post sul blog di Paul Randal che spiega perché la manutenzione di t-log è importante e perché non si dovrebbero ridurre i file di dati .

Mike Walsh ha una grande risposta che copre anche alcuni di questi aspetti, inclusi i motivi per cui potresti non essere in grado di ridurre immediatamente il tuo file di registro .


Se non si utilizzano i registri delle transazioni per i ripristini (ad esempio, si eseguono solo backup completi), è possibile impostare la Modalità di ripristino su "Semplice" e il log delle transazioni si restringerà molto rapidamente e non si riempirà mai più.

Se si utilizza SQL 7 o 2000, è possibile abilitare "Truncate log on checkpoint" nella scheda delle opzioni del database. Questo ha lo stesso effetto.

Questo non è consigliato in ambienti di produzione, ovviamente, dal momento che non sarà possibile ripristinarlo in un dato momento.


È successo con me dove il file di registro del database era di 28 GB.

Cosa puoi fare per ridurre questo? In realtà, i file di registro sono quei dati di file che il server SQL conserva quando una transazione ha avuto luogo. Per una transazione per elaborare SQL Server alloca le pagine per lo stesso. Ma dopo il completamento della transazione, questi non vengono rilasciati improvvisamente sperando che possa esserci una transazione in arrivo come la stessa. Questo sostiene lo spazio.

Passo 1: Primo Eseguire questo comando nel punto di controllo esplorato dalla query del database

Passaggio 2: fare clic con il pulsante destro del mouse sul database Attività> Backup Selezionare tipo di backup come Registro transazioni Aggiungere un indirizzo di destinazione e un nome file per mantenere i dati di backup (.bak)

Ripeti questo passaggio ancora e in questo momento dai un altro nome di file

Passo 3: Ora vai nel database Fai clic destro sul database

Attività> Shrink> File Scegli il tipo di file come azione Riduci registro come spazio inutilizzato

Passaggio 4:

Controllare normalmente il file di registro in SQL 2014 in cui è disponibile

C: \ Programmi \ Microsoft SQL Server \ MSSQL12.MSSQL2014EXPRESS \ MSSQL \ DATA

Nel mio caso, è ridotto da 28 GB a 1 MB


NOTA BENE: Si prega di leggere attentamente i commenti di seguito, e presumo che abbiate già letto la risposta accettata. Come ho detto quasi 5 anni fa:

se qualcuno ha commenti da aggiungere per situazioni in cui questa NON è una soluzione adeguata o ottimale, si prega di commentare qui sotto

  • Fare clic con il tasto destro sul nome del database.

  • Seleziona Attività → Riduci → Database

  • Quindi fare clic su OK !

Solitamente apro la directory di Windows Explorer che contiene i file del database, quindi posso immediatamente vedere l'effetto.

In realtà ero abbastanza sorpreso che funzionasse! Normalmente ho usato DBCC in precedenza, ma l'ho appena provato e non ha ridotto nulla, quindi ho provato la GUI (2005) e ha funzionato benissimo - liberando 17 GB in 10 secondi

In modalità di ripristino completo potrebbe non funzionare, quindi è necessario eseguire prima il backup del registro o passare al ripristino semplice, quindi ridurre il file. [grazie a @onupdatecascade per questo]

-

PS: Apprezzo ciò che alcuni hanno commentato riguardo ai pericoli di questo, ma nel mio ambiente non ho avuto problemi a farlo da solo, soprattutto perché faccio sempre un backup completo prima. Quindi, per favore, prendi in considerazione quale sia il tuo ambiente e come questo influenzi la tua strategia di backup e la sicurezza del tuo lavoro prima di continuare. Tutto quello che stavo facendo era indirizzare le persone verso una funzionalità fornita da Microsoft!


Alcune delle altre risposte non hanno funzionato per me: non era possibile creare il checkpoint mentre il db era online, perché il log delle transazioni era pieno (che ironia). Tuttavia, dopo aver impostato il database in modalità emergenza, sono stato in grado di ridurre il file di registro:

alter database <database_name> set emergency;
use <database_name>;
checkpoint;
checkpoint;
alter database <database_name> set online;
dbcc shrinkfile(<database_name>_log, 200);

Registro transazioni DB Riduci a dimensioni min :

  1. Backup: registro delle transazioni
  2. Riduci i file: registro delle transazioni
  3. Backup: registro delle transazioni
  4. Riduci i file: registro delle transazioni

Ho fatto dei test su diversi numeri di DB: questa sequenza funziona .

Di solito si riduce a 2 MB .

O da una sceneggiatura:

DECLARE @DB_Name nvarchar(255);
DECLARE @DB_LogFileName nvarchar(255);
SET @DB_Name = '<Database Name>';               --Input Variable
SET @DB_LogFileName = '<LogFileEntryName>';         --Input Variable
EXEC 
(
'USE ['[email protected]_Name+']; '+
'BACKUP LOG ['[email protected]_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''[email protected]_LogFileName+''', 2) ' +
'BACKUP LOG ['[email protected]_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''[email protected]_LogFileName+''', 2)'
)
GO




transaction-log