INF: Le cause del Log delle transazioni SQL riempiono

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

In questa pagina

Sommario

Il log delle transazioni di SQL Server può diventare completo, che impedisce ulteriori aggiornamenti, eliminareo inserire l'attività nel database, inclusi i punti di arresto. In genere percepito come errore 1105:
Impossibile allocare spazio per oggetto syslogs in dbname database perché il segmento del log è pieno. Se ha esaurito lo spazio in syslogs, eseguire il dump del log delle transazioni. In caso contrario, utilizzare ALTER DATABASE o sp_extendsegment per aumentare la dimensione del segmento.
Ciò può verificarsi in qualsiasi database, inclusi i master o tempdb. In questo articolo vengono illustrate le possibili cause e soluzioni dei problemi che hanno provocato l'errore 1105. Se il log delle transazioni è pieno e viene visualizzato l'errore 1105, è necessario svuotare il registro utilizzando l'istruzione DUMP TRANSACTION . Per ulteriori informazioni sull'utilizzo di DUMP TRANSACTION, vedere la documentazione di SQL Server.

Informazioni

Una caratteristica fondamentale dei database relazionali, ad esempio Microsoft SQL Server, è l'integrità delle transazioni. Qualsiasi transazione deve essere completamente atomico (ovvero, funzionalmente indivisibile) in quanto tutte le modifiche devono essere applicate o non applicate anche in caso di un errore di sistema. In una transazione definita dall'utente, tutte le istruzioni racchiuse tra le istruzioni BEGIN TRANSACTION e COMMIT TRANSACTION sono applicate o non applicate. In una transazione implicita, ciascuna istruzione SQL è considerata un'unità indivisibile.

Questa capacità consente a SQL Server in cui si verifica un'interruzione dell'alimentazione, arresto del sistema operativo e così via in produzione e dopo il riavvio, pertanto automaticamente ripristinare il database in uno stato coerente, senza l'intervento dell'uomo necessari. Questo contrasta con i sistemi non relazionali che richiedono spesso lunghe procedure manuali per ispezionare il database per problemi di coerenza dopo un errore di sistema.

Il meccanismo del log delle transazioni è ciò che offre questa funzionalità. Poiché l'integrità transazionale viene considerato una caratteristica fondamentale, intrinseca di SQL Server, la registrazione non può essere disattivata. Determinate utilità o operazioni di manutenzione, quali BCP e SELECT INTO, eseguono una registrazione minima, ma anche queste allocazioni di extent del registro in modo che è possibile eseguire il rollback.

I requisiti di spazio per la registrazione possono essere considerevoli. Ad esempio, nella maggior parte dei casi la prima e dopo l'immagine di ogni aggiornato la riga di dati deve essere registrato, oltre che di qualsiasi riga di indice interessata. Dal momento che una quantità fissa di record di transazione deve essere registrata per ogni riga registrata, il rapporto tra dati aggiornati per registrare il consumo di spazio variano a seconda della larghezza di riga. Per una riga stretta, la quantità di spazio consumata per un determinato aggiornamento, eliminare o inserire potrebbe essere dieci volte quella occupata dai dati. Per le righe più ampi, la quantità di spazio consumata sarà in proporzione inferiore. Spazio del log è una conseguenza inevitabile per garantire che l'integrità transazionale. L'amministratore del Database deve fornire spazio di log sufficiente per la propria installazione particolare.

La quantità di spazio di log richiesto può variare a seconda di molti fattori ed è molto difficile da prevedere con precisione in anticipo. Mentre le figure di norma generale, ad esempio 15 al 30% delle dimensioni del database, a volte indicate come punto di partenza per il ridimensionamento del registro, in realtà questo varia notevolmente. Installazioni di SQL Server spesso eseguire alcuni semplici test empirici per valutare indicativamente i requisiti di spazio di log per i dati e le applicazioni e la dimensione del log in base a queste. Tentativo di ridimensionare il registro basato esclusivamente su calcoli e senza test è difficile e spesso imprecise.

Diversi fattori difficile prevedere possono influenzare la variazione di spazio del log. Un fattore è query optimizer. Per un'istruzione di modifica di dati SQL specificata, il piano di accesso può variare nel tempo a seconda della distribuzione statistica dei dati. Diversi piani di accesso possono richiedere diversi quantitativi di spazio del log. Un altro fattore è la frammentazione inevitabile database interno, in grado di influenzare il numero di divisioni di pagina eseguite. Non c'è niente che può essere eseguita o deve essere eseguita per esaminare o influenzare questo processo, come SQL Server gestisce automaticamente i dati per l'utente.

Un esempio di un semplice test sarebbe eseguire DBCC CHECKTABLE (syslogs), che restituisce il numero di pagine di dati a 2048 byte nel registro, prima e dopo l'esecuzione di un campione rappresentativo delle query di modifica dei dati. Ciò può farsi un'idea approssimativa del requisito di spazio di log per questi tipi di query. È consigliabile eccedere per eccesso quando viene fornito il log o spazio su disco per i database relazionali, ad esempio SQL Server.

Per SQL Server 7.0 e 2000 Server, il log delle transazioni è in grado di espandersi quanto richiesto. L'entità dell'aumento può essere controllata dall'utente o può occupare tutta la capacità disponibile su disco. Un file di log è composto da un numero di file di Log virtuali. Il numero e la dimensione di questi file di log virtuale sono determinati da SQL Server e non può essere configurati. Quando si crea un database, ogni file di log fisico dispone di un minimo di due file Log virtuali. In alcuni casi l'amministratore del database consentirà l'opzione "Tronca log in corrispondenza del checkpoint" di un database allo scopo di evitare l'esaurimento dello spazio del log. Lo scopo di questa opzione è possibile fornire un metodo automatico di troncamento del log principalmente per sviluppare o testare database che non si basano sui log dump per il backup. Questa opzione non consente di disattivare l'integrità transazionale o registrazione. Provoca semplicemente il gestore di checkpoint tentare un troncamento del log ogni 60 secondi. Si noti che il log non verrà troncato durante l'emissione di un comando di checkpoint manuale in un database con "Tronca log in corrispondenza del checkpoint" in. Questa opzione è sempre per il database tempdb, anche se non è indicato nella colonna stato dell'output sp_help stored procedure.

Anche con l'opzione "Tronca log in corrispondenza del checkpoint" attivata, una serie di fattori può causare esaurimento dello spazio del log. Questi sono elencati di seguito:
  1. Una transazione atomica grandi dimensioni, soprattutto bulk UPDATE, INSERT o DELETE: ciascuna istruzione SQL è considerata un che di unità atomica deve essere applicata o non applicata nella sua interezza. Per questo motivo, tutti i rowalterations deve essere connesso e la transazione non può essere troncata su itsduration. Ad esempio, se una lunga istruzione collettiva INSERT è stato rilasciato che aveva un runningtime di cinque minuti, il log consumato da questa transazione non può essere truncatedfor in questo periodo. L'amministratore del database deve fornire sufficienti log spacefor l'operazione di massa più grande possibile prevedere oppure eseguire i gruppi di insmaller operazione di massa.
  2. Una transazione senza commit: il registro può essere solo truncatedprior per la transazione senza commit meno recente. Esistono più causesof possibile una transazione senza commit, la maggior parte dei quali sono errori dell'applicazione. Theseinclude:
    1. Una transazione collettiva: come visto in precedenza, per la durata di una transazione collettiva di grandi dimensioni, il registro record generati da quest'ultimo non può essere troncato. Tuttavia, per tale transazione significa inoltre che il troncamento del log di altre transazioni più brevi che eseguono il commit nello stesso periodo.

      Ad esempio, che l'amministratore del database abbia dimensionato il log in modo che risulti sufficiente per la transazione collettiva più voluminosa. Ancora durante l'esecuzione di questa transazione, altre istruzioni di modifica dei dati più breve possano anche consumare spazio del log. Questo spazio di registro non può essere troncato dal momento che la transazione collettiva di grandi dimensioni avviato per prima e pertanto è diventata la transazione senza commit meno recente. L'amministratore deve tenere in considerazione la concorrenza e l'impatto del Registro di una transazione collettiva di grandi dimensioni e dimensione del log in modo appropriato.
    2. Un'applicazione mal progettata che consente l'input dell'utente o altre attività protratte nel tempo all'interno di una transazione definita dall'utente. Ad esempio, dopo l'esecuzione di un'istruzione BEGIN TRANSACTION, un'applicazione potrebbe richiedere all'utente l'input potrebbe richiedere molto tempo, a seconda del comportamento dell'utente. Fino a quando l'utente risponde e l'applicazione invierà un COMMIT, non sarà più possibile troncamento del log.
    3. Un errore dell'applicazione in cui una transazione non viene eseguito il commit: una causa comune di questa è la gestione non corretta della dbcancel () chiamata DB-Library all'interno di una transazione definita dall'utente. Quando si annulla una query tramite dbcancel (), istruzione SQL in esecuzione viene interrotta e il rollback, ma non della transazione esterna. L'applicazione deve tenere conto di queste ed eseguire l'istruzione ROLLBACK TRANSACTION o COMMIT TRANSACTION necessaria per chiudere la transazione. In caso contrario può spesso comportare errore 3902:
      Il commit della transazione non dispone di alcuna istruzione BEGIN TRANSACTION corrispondente.
      Può essere utile per l'applicazione per l'invio di un @@TRANCOUNT SELECT per determinare il livello di nidificazione di transazione esiste. Tuttavia, l'applicazione non ciecamente eseguire questa operazione e quindi eseguire il COMMIT/ROLLBACK per ottenere @@TRANCOUNT = 0. Infatti, se @@TRANCOUNT è sempre diverso da quanto si aspetta l'applicazione, ciò indica che l'applicazione ha perduto traccia del livello di nidificazione delle transazioni, che è un errore di progettazione dell'applicazione. Emissione COMMIT/ROLLBACK a questo punto potrebbe comportare l'applicazione o l'interruzione di transazioni involontarie, dato che l'applicazione non conosce le transazioni che hanno generato il livello di transazione involontario. Invece il programmatore deve eseguire il debug dell'applicazione e le stored procedure interessata per determinare la causa del livello di transazione involontario.
    4. Un errore di rete che non informa SQL Server di una connessione di rete interrotta: se la workstation client si blocca, si riavvia o arresta all'interno di una transazione definita dall'utente, la rete dovrebbe essere informato di SQL Server di questo. Se la rete non correttamente tale scopo, dal punto di vista di SQL Server verrà visualizzato il client sia presente e la transazione aperta con tale client verrà mantenuta. Questo è un problema di rete e deve essere risolto come tale. Per risolvere il problema, l'amministratore potrebbe essere in grado di determinare sp_who, sp_lock o un'utilità di rete quale sessione client ancora esistente e terminarlo manualmente.
    5. Transazione senza commit a causa di blocco: In un ambiente multiutente è possibile che una transazione aperta si blocchi a causa di blocchi mantenuti attivi da un altro processo. In questo caso, la transazione rimarrà comunque aperta, impedendo il troncamento del log. Per rilevare questo comportamento il programmatore o l'amministratore del database sarà necessario utilizzare sp_who, sp_lock o altri strumenti per analizzare l'ambiente di concorrenza. Nella maggior parte dei casi problemi di blocco possono essere ridotto o eliminati tramite query più appropriata, indice e struttura del database.
    6. Tentativo non riuscito per annullare una query di modifica dei dati: se l'applicazione invia un dbcancel () e la query non viene annullata a causa di un problema SQL o di rete, continua a eseguire la query e la transazione rimarrà aperta. Se si sospetta un problema in questo caso, è possibile utilizzare sp_who per vedere se la query viene annullata. Se è stato effettuato un tentativo di annullamento da un client di socket TCP/IP, provare il test da un client named pipe o eseguire l'applicazione client sul computer server che utilizza pipe locali. Ciò consente di distinguere se un problema SQL o rete impedisce l'annullamento.
  3. Checkpoint gestore troncamento della larghezza di banda superato: Althoughthe log viene troncato ogni 60 secondi, la frequenza con cui è finito il takesplace di troncamento. Questo scenario è comune e le altre possibili cause di logoverflow devono essere considerate e risolvere prima di prendere thispossibility. Tuttavia, è possibile superare il troncamento massimo client ifmany tasso stanno inviando contemporaneamente aggiornamenti di grandi dimensioni. È simile a afunnel che può solo distogliere fluido con una determinata frequenza, e può essere stata superata la capacità evenwhile svuotamento. In questo scenario che l'applicazione può essere ristrutturato reducethe numero di righe aggiornate, che deve essere sempre una partizione primaria progettare goalfor qualsiasi database relazionale comunque.

    Se ciò non è fattibile, thesystem può essere riconfigurata per la larghezza di banda i/o su disco tramite striping, ulteriori controller e così via. È comune in questo caso per visualizzare thecheckpoint processo gestore spendere aumentando la quantità di tempo nello stato DUMPTRANSACTION, tentativi di stare al passo con il troncamento del log. Una volta che viene superata la soglia thetruncation (vedere sotto) la checkpointhandler potrebbe non vedere mai cercare troncamento in tale database fino a quando non iscleared il registro.
  4. Superata soglia di troncamento: handleressentially il punto di arresto è un DUMP TRANSACTION WITH TRUNCATE_ONLY. Come se questo wasissued manualmente, esso non avrà sempre successo se il log è già completa al punto acertain. Ad esempio, un'intensa attività di aggiornamento potrebbe riempire il registro to95% tra visite del gestore di checkpoint. Quando il troncamento di handlerattempts di checkpoint, mentre il registro non è completamente pieno, potrebbe essere troppo fullto consentire il troncamento. Infatti, il troncamento del log deve stesso belogged. In questo caso, l'unica soluzione è utilizzare DUMP TRANSACTION con NO_LOGto manualmente troncamento del log. Utilizzando l'opzione NO_LOG consiglia di non exceptwhen assolutamente necessario, come è un'operazione non registrata durante la quale systemfailure potrebbe causare errori di database.
  5. Interazioni tra descritte in precedenza:, ad esempio, undernormal condizioni in un ambiente a uso intensivo di aggiornamento, il tasso di handlertruncation del punto di arresto può mantenere il Registro di riempire. Se un temporaneamente opentransaction causato da una delle condizioni sopra specificate (ad esempio i conflitti di blocco) provoca il riempimento a dire il 50% del registro, sarà molto meno forhandling headroom altre situazioni di aggiornamento, rendendo molto più probabile che raggiungere la soglia di thetruncation, a quel punto troncamento automatico non sarà più possibile.In tempdb le transazioni vengono registrate come qualsiasi altro database. Poiché è Tronca LOG in corrispondenza del CHECKPOINT in tempdb, nella maggior parte dei casi che il log verrà troncato e notoverflow. Una delle circostanze descritte in precedenza, tuttavia, può causare il tofill del log tempdb backup. Tempdb viene in genere configurato per data(sysusages.segmap=7) e mista log in modo che le operazioni di dati e di log si contenderanno lo spazio sameavailable. Alcune Transact-SQL crea ad esempio GROUP BY, ORDER BY DESCe così via, richiedono automaticamente tempdb per lo spazio di lavoro.Questo causerà un record di BEGIN TRANSACTION implicito in tempdb per lo spazio di lavoro. Questa transazione di tempdb willcontinue per tutta la durata della transazione nel database utente, che può defertempdb il troncamento del log per questo periodo. Se la transazione in ishalted di db dell'utente per qualsiasi ragione, ad esempio un blocco, oppure il dbnextrow notprocessing applicazione fino al completamento, la transazione nel database tempdb viene likewisebe a sinistra tempdb aperta, impedendo l'accesso troncamento. Il programmatore deve eseguire il debug di processo e/o risolvere i problemi di concorrenza che hanno causato questa.
  6. Troncamento del log delle transazioni in SQL Server 7.0 and2000 server delle classi viene eseguito troncando il file di Log virtuali. Se anyportion del registro attivo è residente in un dato, che Filecannot di Log virtuali vengano troncati. Se il log attivo è residente in tutti i file di Log virtuale, non è possibile troncare il log. Se l'aumento automatico è attivato e nel thevolume dove si trova il log delle transazioni e la dimensione massima del file è beenreached non è disponibile spazio, il log delle transazioni aumenta del valore specificato in fileproperties il registro.
Di seguito viene descritto il comportamento di troncamento del log all'avvio SQL se Tronca LOG in corrispondenza del CHECKPOINT è impostata.
  • Se è impostato Tronca LOG in corrispondenza del CHECKPOINT e il registro risulta completo in fase di avvio, esso verrà automaticamente scaricato con no_log.
  • Tronca LOG in corrispondenza del CHECKPOINT è l'impostazione predefinita in master poiché il file di registro non può essere inserito nel dispositivo di aseparate, in modo che non può essere caricato. L'unica opzione è todiscard il registro volta pieno.
  • Se si Tronca LOG in corrispondenza del CHECKPOINT non è impostata e il registro risulta completo in fase di avvio, verrà completato il ripristino, ma il checkpoint finale non è stato scritto. Un administratorcan entrare nel database e scaricare il log utilizzando no_truncate per salvare i dati e quindi scaricare il log tramite no_log per eliminarne il contenuto (oppure semplicemente eliminarlo).

RIFERIMENTI


Per ulteriori informazioni, vedere il seguente corso Microsoft Training Certification &:
Microsoft Corporation 2072 amministrazione di un Database di Microsoft SQL Server 2000
Per problemi specifici di SQL Server 7l 0 e versioni successive, vedere il seguente articolo della Microsoft Knowledge Base:
317375 INF: Log delle transazioni aumenta in modo imprevisto o diventa pieno in SQL Server

Proprietà

Identificativo articolo: 110139 - Ultima modifica: mercoledì 18 giugno 2014 - Revisione: 2.0
Le informazioni in questo articolo si applicano a:
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 4.21a Standard Edition
Chiavi: 
kbsqlsetup kbinfo kbother kbmt KB110139 KbMtit
Traduzione automatica articoli
IMPORTANTE: il presente articolo è stato tradotto tramite un software di traduzione automatica di Microsoft ed eventualmente revisionato dalla community Microsoft tramite la tecnologia CTF (Community Translation Framework) o da un traduttore professionista. Microsoft offre articoli tradotti manualmente e altri tradotti automaticamente e rivisti dalla community con l?obiettivo di consentire all'utente di accedere a tutti gli articoli della Knowledge Base nella propria lingua. Tuttavia, un articolo tradotto automaticamente, anche se rivisto dalla community, non sempre è perfetto. Potrebbe contenere errori di vocabolario, di sintassi o di grammatica. Microsoft declina ogni responsabilità per imprecisioni, errori o danni causati da una traduzione sbagliata o dal relativo utilizzo da parte dei clienti. Microsoft aggiorna frequentemente il software e gli strumenti di traduzione automatica per continuare a migliorare la qualità della traduzione.
Clicca qui per visualizzare la versione originale in inglese dell?articolo: 110139
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.
Dichiarazione di non responsabilità per articoli della Microsoft Knowledge Base su prodotti non più supportati
Questo articolo è stato scritto sui prodotti per cui Microsoft non offre più supporto. L?articolo, quindi, viene offerto ?così come è? e non verrà più aggiornato.

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