KB2455009 - CORREZIONE: prestazioni ridotte quando si recupera un database se nel log delle transazioni sono presenti molti contratti multilicenza SQL Server 2005, in SQL Server 2008 o in SQL Server 2008 R2

Sintomi

Considerare lo scenario descritto di seguito:

Installare Microsoft SQL Server 2005, Microsoft SQL Server 2008 o Microsoft SQL Server 2008 R2 in un computer.
Uno o più database sono nel modello di ripristino completo o registrato in blocco.
Le dimensioni iniziali di un file di log delle transazioni per il database sono piccole. Tuttavia, il file delle transazioni cresce di grandi dimensioni.
L'incremento di crescita della crescita automatica del file di log delle transazioni è configurato su dimensioni ridotte. Ad esempio, l'incremento di crescita della crescita della crescita automatica è configurato su un megabyte o sull'uno percento. In alternativa, è possibile aumentare il file di log delle transazioni usando una piccola crescita manuale.
Il file di log delle transazioni aumenta di un piccolo incremento. Dopo un po', il file di log delle transazioni è di grandi dimensioni.

Ad esempio, il file di log delle transazioni cresce da 100 megabyte a dieci gigabyte. Il file di log delle transazioni aumenta di un incremento di megabyte in ogni crescita.

In questo scenario potrebbero verificarsi i problemi seguenti:

Il ripristino del database è lento quando il database viene avviato in caso di grandi quantità di lavoro da recuperare.

Ad esempio, una transazione è in sospeso per molto tempo. In alternativa, molte transazioni di piccole dimensioni non vengono approvate quando il SQL Server viene arrestato o riavviato. In questo caso, è possibile che venga visualizzato un errore per un lungo tempo di ripristino stimato nella fase di analisi, di annullamento o di ripristino. L'errore è simile al seguente e viene registrato nel file di registro degli errori di SQL Server 2005:

L'analisi del database 'mydatabase' (7) è completa allo 0% (restano circa 1234 secondi). Si tratta solo di un messaggio informativo. Non è richiesta alcuna azione utente.
Il ripristino del database è lento quando viene ripristinato un file di backup completo del database e una sequenza di log delle transazioni aggiuntiva se il database viene portato online.

Nota Per portare il database online durante l'operazione di ripristino, usare la sintassi WITH RECOVERY o WITH STANDBY = <> sintassi.
Il ripristino del mirroring del database è lento in un server partner nella coppia di mirroring.
L'avanzamento dell'agente del lettore di log per la replica transazionale è lento per le operazioni seguenti:
- Il log delle transazioni di un database di publisher viene analizzato.
- Viene aggiunto un comando in un database di distribuzione.
- Le transazioni vengono replicate.
Le prestazioni rallentate si verificano quando viene creato uno snapshot del database se vengono registrate molte transazioni o se i log delle transazioni rimangono attivi per un lungo periodo di tempo. Questo problema si verifica perché lo snapshot deve eseguire il ripristino e il rollback dei log delle transazioni.
Le prestazioni rallentate si verificano quando si usa la sintassi DBCC CHECKDB per creare uno snapshot nascosto del database che deve recuperare completamente lo snapshot del database per eseguire i controlli di coerenza.

Causa

Questi problemi si verificano perché i file di log virtuali in un file di log delle transazioni potrebbero rallentare le prestazioni quando SQL Server analizza questi file multilicenza durante le operazioni seguenti:

Ripristino del database
Mirroring del database
Snapshot del database
Attività del lettore di log di replica transazionale di database

Quando un file di log delle transazioni per un database viene aumentato di piccoli incrementi durante condizioni di crescita estreme, migliaia e centinaia di migliaia di file Multilicenza sono presenti all'interno di un singolo set di file di log delle transazioni (con estensione ldf). Tuttavia, una crescita elevata del log con grandi incrementi crea solo diversi contratti multilicenza per confronto. Ad esempio, molti potrebbero essere minori di 100 FVL.

Nota I contratti multilicenza sono le divisioni o i segmenti usati internamente da SQL Server nel file di log delle transazioni.

Risoluzione

Informazioni sugli aggiornamenti cumulativi

SQL Server 2008 R2

La correzione di questo problema è stata rilasciata per la prima volta nell'aggiornamento cumulativo 6. Per altre informazioni su come ottenere questo pacchetto di aggiornamento cumulativo per SQL Server 2008 R2, fare clic sul numero dell'articolo seguente per visualizzare l'articolo della Microsoft Knowledge Base:

2489376 Pacchetto di aggiornamento cumulativo 6 per SQL Server 2008 R2 Nota Poiché le build sono cumulative, ogni nuova versione di correzione contiene tutti gli aggiornamenti rapidi e tutte le correzioni per la sicurezza inclusi nella versione di correzione precedente SQL Server 2008 R2. È consigliabile applicare la versione più recente di correzione che contiene questo hotfix. Per ulteriori informazioni, fare clic sul numero dell'articolo seguente per visualizzare l'articolo nella Microsoft Knowledge Base:

981356 Le build SQL Server 2008 R2 rilasciate dopo SQL Server 2008 R2

SQL Server 2008 Service Pack 1

La correzione di questo problema è stata rilasciata per la prima volta nell'aggiornamento cumulativo 12 per SQL Server 2008 Service Pack 1.

Nota Poiché le build sono cumulative, ogni nuova versione di correzione contiene tutti gli aggiornamenti rapidi e tutte le correzioni per la sicurezza inclusi nella versione di correzione precedente SQL Server 2008. Microsoft consiglia di prendere in considerazione l'applicazione della versione più recente di correzione che contiene questo hotfix. Per ulteriori informazioni, fare clic sul numero dell'articolo seguente per visualizzare l'articolo nella Microsoft Knowledge Base:

970365 Le build SQL Server 2008 rilasciate dopo il rilascio del Service Pack 1 di SQL Server 2008 microsoft SQL Server 2008 vengono create per service Pack SQL Server specifici. È necessario applicare un hotfix di SQL Server 2008 Service Pack 1 a un'installazione di SQL Server 2008 Service Pack 1. Per impostazione predefinita, qualsiasi aggiornamento rapido fornito in un Service Pack di SQL Server è incluso nel service pack di SQL Server successivo.

SQL Server 2008 Service Pack 2

La correzione di questo problema è stata rilasciata per la prima volta nell'aggiornamento cumulativo 2 per SQL Server 2008 Service Pack 2.

Nota Poiché le build sono cumulative, ogni nuova versione di correzione contiene tutti gli aggiornamenti rapidi e tutte le correzioni per la sicurezza inclusi nella versione di correzione precedente SQL Server 2008. Microsoft consiglia di prendere in considerazione l'applicazione della versione più recente di correzione che contiene questo hotfix. Per ulteriori informazioni, fare clic sul numero dell'articolo seguente per visualizzare l'articolo nella Microsoft Knowledge Base:

2402659 Le build SQL Server 2008 rilasciate dopo il rilascio del service pack 2 di SQL Server 2008 Microsoft SQL Server 2008 vengono create per Service Pack SQL Server specifici. È necessario applicare un hotfix di SQL Server 2008 Service Pack 2 a un'installazione di SQL Server 2008 Service Pack 2. Per impostazione predefinita, qualsiasi aggiornamento rapido fornito in un Service Pack di SQL Server è incluso nel service pack di SQL Server successivo.

SQL Server 2005 Service Pack 3

La correzione di questo problema è stata rilasciata per la prima volta nell'aggiornamento cumulativo 13 per SQL Server 2005 Service Pack 3.

Nota Poiché le build sono cumulative, ogni nuova versione delle correzioni contiene tutti gli aggiornamenti rapidi e tutte le correzioni per la sicurezza inclusi nella versione precedente delle correzioni di SQL Server 2005. Microsoft consiglia di prendere in considerazione l'applicazione della versione più recente di correzione che contiene questo hotfix. Per ulteriori informazioni, fare clic sul numero dell'articolo seguente per visualizzare l'articolo nella Microsoft Knowledge Base:

960598 Le build SQL Server 2005 rilasciate dopo il rilascio del Service Pack 3 di SQL Server 2005 microsoft SQL Server 2005 vengono create per specifici Service Pack SQL Server. È necessario applicare un hotfix di SQL Server 2005 Service Pack 3 a un'installazione di SQL Server 2005 Service Pack 3. Per impostazione predefinita, qualsiasi aggiornamento rapido fornito in un Service Pack di SQL Server è incluso nel service pack di SQL Server successivo.

SQL Server 2005 Service Pack 4

La correzione di questo problema è stata rilasciata per la prima volta nell'aggiornamento cumulativo 1 per SQL Server 2005 Service Pack 4.

Nota Poiché le build sono cumulative, ogni nuova versione delle correzioni contiene tutti gli aggiornamenti rapidi e tutte le correzioni per la sicurezza inclusi nella versione precedente delle correzioni di SQL Server 2005. Microsoft consiglia di prendere in considerazione l'applicazione della versione più recente di correzione che contiene questo hotfix.

È necessario applicare un hotfix di SQL Server 2005 Service Pack 4 a un'installazione di SQL Server 2005 Service Pack 4. Per impostazione predefinita, qualsiasi aggiornamento rapido fornito in un Service Pack di SQL Server è incluso nel service pack di SQL Server successivo.

Stato

Microsoft ha confermato che questo problema si verifica nei prodotti elencati nella sezione "Si applica a".

Soluzione alternativa

Installare questo hotfix e riavviare SQL Server potrebbe migliorare le prestazioni di ripristino. Ad esempio, le prestazioni potrebbero essere migliorate per le operazioni seguenti dopo l'installazione di questo hotfix e il riavvio SQL Server:

Ripristino del database
Mirroring del database
Snapshot del database
Attività del lettore di log di replica transazionale di database

Se non è possibile installare questo hotfix, è possibile eseguire le soluzioni alternative seguenti per attenuare un problema esistente e prevenire occorrenze future.

Mitigare un problema esistente

Attendi il completamento

dell'operazione di ripristino o ripristino Se si ha un database non recuperato con prestazioni lente quando si ripristina o si ripristina il database, potrebbe essere necessario attendere il completamento dell'operazione di ripristino o ripristino. Ad esempio, è possibile visualizzare lo stato offline o di ripristino in SQL Server Management Studio (SSMS) per un database non recuperato. L'arresto di SQL Server in genere non offre alcun sollievo per un ripristino lento e può richiedere più tempo per ripetere la stessa fase di analisi del ripristino, fase di ripristino o fase di annullamento.
Evitare di ripristinare la sequenza del log delle transazioni che contiene migliaia di file multilicenza

Se si verificano rallentamenti durante il ripristino e il ripristino di un database usando un file di backup, è possibile evitare di ripristinare le sequenze del log delle transazioni che contengono migliaia di file multilicenza. Per identificare il file di backup con la maggior parte dei file di log virtuali registrati, usare l'istruzione seguente per visualizzare le colonne FirstLSN e LastLSN nei file di backup del log:
RESTORE HEADERONLY FROM DISK='C:\folder\file.trn'

È possibile decidere di evitare di ripristinare i file di backup del log. In alternativa, è possibile usare l'istruzione STOP AT nei comandi RESTORE per evitare le parti altamente frammentate dei log delle transazioni. Se non si ripristinano completamente le sequenze di log fino all'ultimo momento durante uno scenario di ripristino di errore, la perdita di dati si verifica nel database SQL Server. Questa perdita di dati si verifica perché non tutte le transazioni vengono conservate. Di conseguenza, c'è una decisione di compromesso tra le aziende. È possibile ripristinare completamente un log delle transazioni altamente frammentato. Tuttavia, questa operazione può richiedere molte ore. In alternativa, è possibile usare l'istruzione STOP AT nel ripristino per interrompere il ripristino prima della parte altamente frammentata del log. Tuttavia, eventuali transazioni mancanti omettete vengono perse.

Nota: Senza installare questo hotfix, in genere non esiste alcuna alternativa sicura per il ripristino rapido dopo il riavvio SQL Server. SQL Server deve individuare l'elenco dei file multilicenza per analizzare i file di log, ripetere le transazioni completate e quindi annullare le transazioni incomplete per completare il ripristino per portare il database online in modo sicuro. Non è possibile ignorare in modo sicuro le transazioni durante il ripristino.

Impedire un'occorrenza futura

Impostare l'incremento della crescita automatica del database su dimensioni

appropriate Se le dimensioni dell'incremento dell'autogrow sono troppo piccole, saranno presenti molti file di log virtuali (VLF) e potrebbero verificarsi rallentamenti delle prestazioni in SQL Server. Se le dimensioni dell'incremento dell'autogrow sono troppo grandi, le query che fanno crescere automaticamente i log delle transazioni potrebbero dover attendere molto tempo per completare una crescita. Pertanto, in SQL Server potrebbe verificarsi un errore di timeout. Per risolvere questi problemi, è possibile impostare le dimensioni dell'incremento della crescita automatica per il database su una dimensione appropriata.
Eliminare il gran numero di contratti multilicenza e usare una crescita

manuale Se nel log delle transazioni sono presenti molti contratti multilicenza, ridurre le dimensioni del log delle transazioni e aumentarlo prima dell'attività di punta per soddisfare la domanda usando una crescita manuale. Ad esempio, il log delle transazioni torna a una dimensione media ragionevole con un grande incremento o con una singola crescita manuale. Di conseguenza, le dimensioni del log delle transazioni raggiungono una capacità massima e i file di backup del log vengono pianificati frequentemente e periodicamente. Inoltre, il log delle transazioni potrebbe essere troncato e i file multilicenza per il log delle transazioni possono essere riutilizzati in un ciclo.
Come ridurre e aumentare manualmente

il log delle transazioni Per correggere un log con troppi file multilicenza, seguire questa procedura per ridurre il log e aumentarlo di nuovo manualmente:
1. Se il database è in un modello di ripristino completo o registrato in blocco, è necessario eseguire il backup del log delle transazioni per consentire il troncamento e il riutilizzo dei contratti multilicenza attivi. BACKUP LOG nomedatabase TO DISK='C:\cartella\log_backupfile.trn'
  Per ulteriori informazioni su come eseguire il backup del file di log delle transazioni tramite SSMS, visitare il seguente sito Web Microsoft Developer Network (MSDN):
  
  Come eseguire il backup del file di log delle transazioni tramite SSMSPer ulteriori informazioni su come eseguire il backup del file di log delle transazioni tramite istruzioni Transact-SQL, visitare il seguente sito Web MSDN:
  
  Come eseguire il backup del file di log delle transazioni tramite istruzioni Transact-SQL
2. Per determinare il nome logico del file di log delle transazioni, eseguire una delle istruzioni seguenti.
  Dichiarazione 1
  Dichiarazione exec sp_helpfile 2
  select * from sys.sysfiles Per ridurre le dimensioni del file di log delle transazioni alle dimensioni desiderate, utilizzare il codice seguente:DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
3. È possibile aumentare le dimensioni del file di log delle transazioni a una dimensione appropriata. È consigliabile consentire l'aumento delle dimensioni del file di log delle transazioni fino a raggiungere il picco delle dimensioni normali. Di conseguenza, viene evitato l'incremento della crescita automatica. Per impostare le dimensioni del log delle transazioni, usare la pagina Proprietà database in SSMS oppure utilizzare la sintassi ALTER DATABASE seguente:MODIFY FILE ( NAME = transactionloglogicalfilenae, SIZE=newtotalsize MB)
  Per ulteriori informazioni su come aumentare le dimensioni di un database in SSMS, visitare il seguente sito Web MSDN:
  
  Come aumentare le dimensioni di un database in SSMSPer ulteriori informazioni sulla sintassi ALTER DATABASE MODIFY FILE, visitare il seguente sito Web MSDN:
  
  Informazioni generali sulla sintassi ALTER DATABASE MODIFY FILE

Ulteriori informazioni

È possibile controllare il numero di segmenti VLF esaminando il file di log degli errori SQL e quindi individuando il numero di sequenza di log (LSN) in ogni file di backup del log delle transazioni. Le prime cifre prima del simbolo dei due punti nell'LSN corrispondono al numero del LSN.

Ad esempio, il primo numero nel primo messaggio informativo per l'LSN è 1. Tuttavia, il primo numero nel secondo messaggio informativo per l'LSN è 100001. In questo scenario, ci sono 100.000 VLFs usati tra il momento del primo messaggio informativo e del secondo messaggio informativo. Di conseguenza, il log delle transazioni frammentato registrato con molti file di log virtuali (VLFs) è simile al seguente:

{Log è stato eseguito il backup. Database: nomedb, data di creazione(ora): 07/07/2010/08(12:36:46), primo LSN: 1:5068:70, ultimo LSN: 1:5108:1, numero di dispositivi dump: 1, informazioni sul dispositivo: (FILE=1, TYPE=DISK: {'C:\folder\logbackup1.trn'}). Si tratta solo di un messaggio informativo. Non è richiesta alcuna azione utente.

È stato eseguito il backup del log. Database: nomedb, data di creazione(ora): 07/00/2010/08(15:36:46), primo LSN: 100001:5108:1, ultimo LSN: 100002:5108:1, numero di dispositivi dump: 1, informazioni sul dispositivo: (FILE=2, TYPE=DISK: {'C:\folder\logbackup2.trn'}). Si tratta solo di un messaggio informativo. Non è richiesta alcuna azione utente.}

La tabella seguente fornisce altre informazioni sui prodotti o gli strumenti che verificano automaticamente la condizione descritta nella sezione "Sintomi" dell'istanza di SQL Server e nelle versioni di SQL Server in base a cui viene valutata la regola.

Software per regole	Titolo della regola	Descrizione della regola	Versioni del prodotto in base alle quali viene valutata la regola
System Center Advisor	SQL Server con replica transazionale, le prestazioni dell'agente di lettura log potrebbero essere interessate dalle dimensioni del log delle transazioni o dal numero di VLF	In questo SQL Server instance advisor ha rilevato una replica transazionale presenza con un numero notevolmente maggiore di VLFs o TLOG. Le prestazioni dell'agente di lettura log sono influenzate negativamente dalle dimensioni del log delle transazioni o dal numero di VLF. Ridurre le dimensioni del log delle transazioni e il numero di VLF per migliorare le prestazioni dell'agente di lettura log.	SQL Server 2008 SQL Server 2008 R2 SQL Server 2012

Riferimenti

Per ulteriori informazioni sull'architettura fisica del log delle transazioni, visitare il seguente sito Web MSDN:

Informazioni generali sull'architettura fisica del log

delle transazioni Per ulteriori informazioni sui numeri di sequenza di log (LSN), visitare il seguente sito Web MSDN:

Informazioni generali sui numeri

di sequenza di log Per ulteriori informazioni sull'errore 1413 all'avvio del mirroring del database, visitare il seguente sito Web MSDN:

Informazioni generali sull'errore 1413 all'avvio

del mirroring del database Per ulteriori informazioni su come una struttura di file di log può influire sui tempi di recupero del database, visitare il seguente sito Web MSDN:

Impatto della struttura di un file di log sui tempi

di recupero del database Per ulteriori informazioni sui contratti multilicenza del log delle transazioni, visitare il seguente sito Web MSDN:

Informazioni generali sul file

di log delle transazioni Per ulteriori informazioni su come creare uno snapshot del database, visitare il seguente sito Web MSDN:

Come creare uno snapshot del databasePer altre informazioni sul modello di manutenzione incrementale per SQL Server, fare clic sul numero dell'articolo seguente per visualizzare l'articolo della Microsoft Knowledge Base:

935897 Un modello di manutenzione incrementale è disponibile dal team SQL Server per fornire aggiornamenti rapidi per i problemi segnalatiPer altre informazioni sullo schema di denominazione per gli aggiornamenti SQL Server, fare clic sul numero dell'articolo seguente per visualizzare l'articolo della Microsoft Knowledge Base:

822499Nuovo schema di denominazione per Microsoft SQL Server pacchetti di aggiornamenti softwarePer altre informazioni sulla terminologia di aggiornamento software, fare clic sul numero dell'articolo seguente per visualizzare l'articolo della Microsoft Knowledge Base:

824684 Descrizione della terminologia standard utilizzata per descrivere gli aggiornamenti software Microsoft