INF: Il log delle transazioni aumenta in modo imprevisto o diventa pieno in SQL Server

Traduzione articoli Traduzione articoli
Identificativo articolo: 317375 - Visualizza i prodotti a cui si riferisce l?articolo.
Espandi tutto | Chiudi tutto

In questa pagina

Sommario

In SQL Server 7.0 e in SQL Server 2000, con l'impostazione di aumento automatico, i file di log delle transazioni possono espandersi automaticamente.

In genere la dimensione del file di log delle transazioni stabilisce quando pu˛ contenere il numero massimo di transazioni che si pu˛ verificare tra i troncamenti del log delle transazioni attivati dai checkpoint o dai backup del log delle transazioni.

In alcune situazioni, tuttavia, il log delle transazioni pu˛ diventare molto grande, esaurire lo spazio o diventare pieno. In genere viene visualizzato il messaggio di errore riportato di seguito quando un file di log delle transazioni occupa lo spazio su disco disponibile e non pu˛ espandersi ulteriormente:
Errore: 9002, GravitÓ: 17, Stato: 2
Il file di log del database '%.*ls' Ŕ pieno.
Oltre a questo messaggio di errore in SQL Server Ŕ possibile contrassegnare i database sospetti a causa di una mancanza di spazio per l'espansione del log delle transazioni. Per ulteriori informazioni su come risolvere questa situazione, vedere l'argomento relativo allo spazio su disco insufficiente nella Documentazione in linea di SQL Server.

L'espansione del log delle transazioni pu˛ inoltre verificarsi nelle seguenti situazioni:
  • Un file di log delle transazioni molto grande.
  • Le transazioni possono non andare a buon fine e viene avviato il rollback.
  • Le transazioni possono richiedere tempi lunghi per il completamento.
  • Possono verificarsi problemi di prestazioni.
  • Possono verificarsi blocchi.

Cause

L'espansione del log delle transazioni pu˛ verificarsi a causa delle seguenti motivazioni o scenari:

Transazioni non vincolate

Le transazioni esplicite rimangono non vincolate se non si genera un comando COMMIT o ROLLBACK esplicito. Questa evenienza si verifica con maggiore frequenza quando un'applicazione genera un comando CANCEL o Transact SQL KILL senza un comando ROLLBACK corrispondente. Si verifica l'annullamento della transazione, ma non viene eseguito il rollback. In SQL Server non quindi Ŕ possibile troncare ogni transazione che si verifica dopo questo fatto, perchÚ la transazione annullata Ŕ ancora aperta. ╚ possibile utilizzare il riferimento DBCC OPENTRAN Transact SQL per verificare se Ŕ presente una transazione attiva in un database in un momento particolare. Per ulteriori informazioni su questo scenario particolare, fare clic sui numeri degli articoli della Microsoft Knowledge Base riportati di seguito (il contenuto potrebbe essere in inglese):
295108 INF: Una transazione incompleta pu˛ contenere un numero elevato di blocchi e causare l'arresto
171224 INF: Informazioni sul funzionamento del comando Transact-SQL KILL
Vedere inoltre l'argomento relativo a DBCC OPENTRAN nella Documentazione in linea di SQL Server.

Scenari che possono dare come risultato transazioni non vincolate:
  • La progettazione di un'applicazione che presume che tutti gli errori causino i rollback.
  • La progettazione di un'applicazione che non tiene completamente in considerazione il comportamento di SQL Server quando viene eseguito il rollback alle transazioni denominate o a transazioni denominate particolarmente nidificate. Se si tenta di eseguire il rollback a una transazione denominata interna, verrÓ visualizzato il seguente messaggio di errore:
    Server: messaggio 6401, livello 16, stato 1, riga 13 Impossibile eseguire il rollback di InnerTran. Non sono stati individuati un punto di salvataggio o una transazione con tale nome.
    Dopo la visualizzazione del messaggio di errore, l'esecuzione continua all'istruzione successiva. Questo comportamento Ŕ legato a caratteristiche di progettazione. Per ulteriori informazioni, vedere l'argomento relativo alle transazioni nidificate o a SQL Server nella Documentazione in linea di SQL Server.

    Si consiglia di effettuare quanto riportato di seguito durante la progettazione dell'applicazione:
    • Aprire solo un'unitÓ di transazione (considerando la possibilitÓ che un altro processo possa chiamare la stessa unitÓ).
    • Verificare @@TRANCOUNT prima di generare un comando o istruzione COMMIT, ROLLBACK, RETURN o simile.
    • Scrivere il codice presupponendo che un altro @@TRANCOUNT potrebbe includere l'unitÓ aperta e stabilire che del @@TRANCOUNT esterno venga eseguito il rollback quando si verifica un errore.
    • Esaminare il punto di salvataggio e contrassegnare le opzioni per le transazioni (non rilasciano blocchi).
    • Eseguire una verifica completa.
  • Un'applicazione che consente l'interazione dell'utente all'interno delle transazioni. In questo modo la transazione rimane aperta a lungo, causando il blocco e l'aumento delle dimensioni del log delle transazioni, perchÚ la transazione aperta non pu˛ essere troncata e nuove transazioni vengono aggiunte al log dopo quest'ultima.
  • Un'applicazione che non verifica @@TRANCOUNT per controllare che non ci siano transazioni aperte.
  • Errori di rete o di altro tipo che chiudono la connessione dell'applicazione client a SQL Server senza notificarlo.
  • Pool di connessioni Dopo essere stati creati, i thread di lavoro vengono riutilizzati in SQL Server se non utilizzati da una connessione. Se per una connessione utente viene avviata una transazione ed eseguita la disconnessione prima del commit o del rollback della transazione e in una connessione successiva viene riutilizzato lo stesso thread, la precedente transazione rimane comunque aperta. Questa situazione dÓ come risultato blocchi che rimangono aperti dalla transazione precedente e impediscono il troncamento delle transazioni vincolate nel log, dando come risultato dimensioni di file di log elevate.Per ulteriori informazioni sui pool di connessioni, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito (il contenuto potrebbe essere in inglese):
    164221 INFO: Attivazione del pool di connessione in un'applicazione ODBC

Transazioni di dimensioni estremamente grandi

I record dei log nei file di log delle transazioni sono troncati per ogni transazione. Se l'ambito della transazione Ŕ grande, tale transazione e qualsiasi transazione successiva non verranno rimosse dal log delle transazioni se non quando complete. Questo pu˛ dare come risultato file di log di grandi dimensioni. Se la transazione Ŕ sufficientemente grande, il file di log potrebbe utilizzare tutto lo spazio su disco disponibile e causare la visualizzazione del tipo di messaggio di errore "log delle transazioni pieno" come l'errore 9002. Per ulteriori informazioni sulle operazioni da effettuare quando si riceve questo tipo di messaggio di errore, vedere la sezione "Informazioni" in questo articolo. Questa operazione richiede inoltre molto tempo e l'overhead di SQL Server per eseguire il rollback di transazioni di grandi dimensioni.

Operazioni: DBCC DBREINDEX e CREATE INDEX

A causa delle modifiche nel modello di recupero in SQL Server 2000, quando si utilizza la modalitÓ di recupero completa e si esegue DBCC DBREINDEX, il log delle transazioni potrebbe espandersi in modo significativamente maggiore rispetto a quello di SQL Server 7.0 in una modalitÓ di recupero equivalente con l'utilizzo di SELECT INTO o BULK COPY e con l'opzione "Tronca log in corrispondenza del checkpoint" disattivata.

Anche se la dimensione del log delle transazioni dopo l'utilizzo di DBREINDEX potrebbe essere un problema, questo approccio fornisce migliori prestazioni di recupero del log.

Durante il ripristino dai backup del log delle transazioni

Questa indicazione Ŕ riportata nel seguente articolo della Microsoft Knowledge Base (il contenuto potrebbe essere in inglese):
232196 INF: Lo spazio di log utilizzato sembra aumentare dopo il ripristino dal backup

Se si imposta SQL Server 2000 per utilizzare la modalitÓ con registrazione di massa e si genera un'istruzione BULK COPY o SELECT INTO, ogni modifica viene contrassegnata e ne viene eseguita una copia durante il backup del log delle transazioni. Anche se questa operazione consente di eseguire il backup dei log delle transazioni e di risolvere gli errori anche dopo l'esecuzione di operazioni di massa, la dimensione dei log delle transazioni aumenta. In SQL Server 7.0 non Ŕ inclusa questa funzionalitÓ, e vengono solo registrati gli extent modificati, ma non gli extent veri e propri. Per questo motivo la registrazione richiede un spazio maggiore in SQL Server 2000 rispetto a SQL Server 7.0 nella modalitÓ con registrazione di massa, ma non quanto Ŕ necessario per la modalitÓ completa.

Le applicazioni client non elaborano tutti i risultati

Se si genera una query in SQL Server e non si gestiscono immediatamente i risultati, si potrebbero verificare blocchi e ridurre la concorrenza nel server.

Si supponga ad esempio di generare una query che richiede le righe di due pagine per popolare il set di risultati. In SQL Server viene eseguita l'analisi, la compilazione e l'esecuzione della query. Ci˛ significa che i blocchi condivisi vengono collocati nelle due pagine che contengono le righe che devono soddisfare la query. Si supponga inoltre che non tutte le righe rientrino in un pacchetto TDS di SQL Server, vale a dire il metodo con cui il server comunica con il client. I pacchetti TDS vengono riempiti e inviati al client. Se tutte le righe della prima pagina rientrano nel pacchetto TDS, in SQL Server viene rilasciato il blocco condiviso per tale pagina ma il blocco condiviso della seconda pagina rimane. Avviene quindi l'attesa durante la quale il client richiede pi¨ dati, operazione che pu˛ essere svolta ad esempio mediante DBNEXTROW/DBRESULTS, SQLNextRow/SQLResults o FetchLast/FetchFirst.

Ci˛ significa che il blocco condiviso viene mantenuto finchÚ il client non richiede la parte restante di dati. Altri processi che richiedono i dati della seconda pagina potrebbero essere bloccati.

Le query scadono prima del completamento dell'espansione del log delle transazioni e vengono visualizzati falsi messaggi di errore che indicano che il log Ŕ pieno

In questa situazione, anche se Ŕ disponibile spazio sufficiente su disco, viene visualizzato un messaggio di errore che indica lo spazio esaurito.

Questa situazione varia in SQL Server 7.0 e in SQL Server 2000.

Una query pu˛ fare in modo che il log delle transazioni si espanda automaticamente se il log Ŕ quasi pieno. Questa operazione potrebbe richiedere altro tempo e una query potrebbe interrompersi o superare il periodo di timeout per questo motivo. In questa situazione in SQL Server 7.0 viene restituito l'errore 9002. Questo problema non si applica a SQL Server 2000.

In SQL Server 2000 se l'opzione compattazione automatica Ŕ attiva per un database, il periodo di tempo durante il quale un log delle transazioni tenta di espandersi Ŕ notevolmente breve, ma tale operazione non pu˛ essere effettuata perchÚ contemporaneamente Ŕ in esecuzione la funzione compattazione automatica. Questo fattore potrebbe anche causare false istanze dell'errore 9002.

In genere l'espansione automatica dei file di log delle transazioni si verifica rapidamente. Tuttavia, nelle situazioni riportate di seguito, pu˛ richiedere pi¨ tempo del previsto:
  • Gli incrementi della crescita sono troppo ridotti.
  • Il server Ŕ lento per vari motivi.
  • Le unitÓ disco non sono sufficientemente veloci.

Transazioni non replicate

La dimensione del log delle transazioni del database server di pubblicazione pu˛ espandersi se si utilizza la replica. Le transazioni che influiscono sugli oggetti replicati sono contrassegnate come "Per la replica". Queste transazioni, come le transazioni non vincolate, non sono eliminate dopo il checkpoint o dopo il backup del log delle transazioni finchÚ l'agente di lettura dei log copia le transazioni nel database di distribuzione e ne toglie il contrassegno. Se un problema con l'agente di lettura dei log impedisce la lettura di queste transazioni nel database server di pubblicazione, la dimensione del log delle transazioni pu˛ continuare a espandersi con l'aumento del numero delle transazioni non replicate. ╚ possibile utilizzare il riferimento DBCC OPENTRAN Transact SQL per identificare la transazione non replicata pi¨ obsoleta.

Per ulteriori informazioni sulla risoluzione di transazioni non replicate, vedere gli argomenti relativi a sp_replcounters e a sp_repldone nella Documentazione in linea di SQL Server.

Per ulteriori informazioni, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito (il contenuto potrebbe essere in inglese):
306769 FIX: Impossibile troncare il log delle transazioni di un database con pubblicazione snapshot
240039 FIX: DBCC OPENTRAN non fornisce informazioni di replica
198514 FIX: Il ripristino su un nuovo server causa la permanenza delle transazioni nel log

Informazioni

Il log delle transazioni per qualsiasi database Ŕ gestito come set di file di log virtuali VLF (virtual log files) la cui dimensione Ŕ determinata internamente da SQL Server in base alla dimensione totale del file di log e alla crescita presente durante l'espansione del log. Un log si espande sempre in unitÓ di VLF interi e pu˛ comprimersi solo in un limite del file di log virtuale. Un VLF pu˛ esistere in uno di tre stati: ACTIVE, RECOVERABLE e REUSABLE.
  • ACTIVE: la porzione attiva del log inizia dal numero minimo di sequenza dei log LSN (log sequence number) che rappresenta una transazione attiva, o non vincolata. La porzione attiva del log termina con l'ultimo LSN scritto. Qualsiasi VLF che pu˛ contenere qualsiasi parte del log attivo Ŕ considerato un VLF attivo. Lo spazio inutilizzato nel log fisico non Ŕ parte di alcun VLF.
  • RECOVERABLE: la porzione del log che precede la transazione attiva pi¨ obsoleta Ŕ necessaria solo per gestire una sequenza di backup dei log a scopo di recupero.
  • REUSABLE: se non si conservano backup dei log delle transazioni o se Ŕ giÓ stato eseguito il backup del log, i VLF vengono riutilizzati in SQL Server prima della transazione attiva pi¨ obsoleta.
Quando viene raggiunta la fine del file di log fisico, lo spazio del file fisico viene riutilizzato generando un'operazione CIRCLING BACK all'inizio dei file. In realtÓ in SQL Server viene riciclato lo spazio del file di log che non Ŕ pi¨ necessario a scopo di recupero o di backup. Se viene conservata una sequenza di backup dei log, la parte del log prima del numero LSN minimo non pu˛ essere sovrascritta finchÚ non viene eseguito il backup o il troncamento dei record del log. Dopo l'esecuzione del backup del log, Ŕ possibile tornare all'inizio del file. In seguito alla scrittura di record del log in una posizione iniziale del file di log, la porzione riutilizzabile del log si trova quindi tra la fine del log logico e la porzione attiva del log.

Per ulteriori informazioni, vedere l'argomento relativo all'architettura fisica del log delle transazioni nella Documentazione in linea di SQL Server. ╚ inoltre possibile visualizzare un ottimo diagramma e analisi di questo argomento nel testo "Inside SQL Server 7.0" (Ron Soukup, Inside Microsoft SQL Server 7.0, Microsoft Press, 1999) e nel testo "Inside SQL Server 2000" (Kalen Delaney, Inside Microsoft SQL Server 2000, Microsoft Press, 2001). I database di SQL Server 7.0 e di SQL Server 2000 dispongono delle opzioni di aumento e compattazione automatici. Queste opzioni possono essere utilizzate per comprimere o espandere il log delle transazioni.

Per ulteriori informazioni sul modo in cui queste opzioni possono influire sul server, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito (il contenuto potrebbe essere in inglese):
315512 INF: Considerazioni per la configurazione di aumento e compattazione automatici
Esiste una differenza tra il troncamento e la compressione del file di log delle transazioni. Quando viene eseguito il troncamento del file di log delle transazioni, significa che il contenuto di tale file, ad esempio delle transazioni vincolate, viene eliminato. Tuttavia quando si visualizza la dimensione del file dalla prospettiva dello spazio su disco, ad esempio in Esplora risorse oppure utilizzando il comando dir, la dimensione rimane invariata. Nonostante questo lo spazio all'interno del file ldf pu˛ essere riutilizzato dalle nuove transazioni. Solo quando la dimensione del file di log viene ridotta, si nota un reale cambiamento nella dimensione di tale file.

Per ulteriori informazioni su come ridurre la dimensione dei log delle transazioni, fare clic sui numeri degli articoli della Microsoft Knowledge Base riportati di seguito (il contenuto potrebbe essere in inglese):
256650 INF: Come ridurre il registro transazioni di SQL Server 7.0
272318 INF: Compattazione del log delle transazioni in SQL Server 2000 con DBCC SHRINKFILE
Per ulteriori informazioni sull'utilizzo del log delle transazioni in SQL Server 6.5, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito (il contenuto potrebbe essere in inglese):
110139 INF: Cause dell'esaurimento di spazio del log delle transazioni di SQL Server

ProprietÓ

Identificativo articolo: 317375 - Ultima modifica: martedý 16 luglio 2013 - Revisione: 3.2
Le informazioni in questo articolo si applicano a:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Chiavi:á
kbsqlmanagementtools kbinfo KB317375
LE INFORMAZIONI CONTENUTE NELLA MICROSOFT KNOWLEDGE BASE SONO FORNITE SENZA GARANZIA DI ALCUN TIPO, IMPLICITA OD ESPLICITA, COMPRESA QUELLA RIGUARDO ALLA COMMERCIALIZZAZIONE E/O COMPATIBILITA' IN IMPIEGHI PARTICOLARI. L'UTENTE SI ASSUME L'INTERA RESPONSABILITA' PER L'UTILIZZO DI QUESTE INFORMAZIONI. IN NESSUN CASO MICROSOFT CORPORATION E I SUOI FORNITORI SI RENDONO RESPONSABILI PER DANNI DIRETTI, INDIRETTI O ACCIDENTALI CHE POSSANO PROVOCARE PERDITA DI DENARO O DI DATI, ANCHE SE MICROSOFT O I SUOI FORNITORI FOSSERO STATI AVVISATI. IL DOCUMENTO PUO' ESSERE COPIATO E DISTRIBUITO ALLE SEGUENTI CONDIZIONI: 1) IL TESTO DEVE ESSERE COPIATO INTEGRALMENTE E TUTTE LE PAGINE DEVONO ESSERE INCLUSE. 2) I PROGRAMMI SE PRESENTI, DEVONO ESSERE COPIATI SENZA MODIFICHE, 3) IL DOCUMENTO DEVE ESSERE DISTRIBUITO INTERAMENTE IN OGNI SUA PARTE. 4) IL DOCUMENTO NON PUO' ESSERE DISTRIBUITO A SCOPO DI LUCRO.

Invia suggerimenti

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com