Risolvere i problemi relativi a un log delle transazioni completo (Errore di SQL Server 9002)

Si applica a: SQL Server (tutte le versioni supportate)

Opzione 1: Eseguire i passaggi direttamente in un notebook eseguibile tramite Azure Data Studio

Nota

Prima di tentare di aprire questo notebook, verificare che Azure Data Studio sia installato nel computer locale. Per eseguire l'installazione, vedere Informazioni su come installare Azure Data Studio.

Opzione 2: seguire il passaggio manualmente

In questo argomento vengono illustrate le risposte possibili a un log delle transazioni pieno e viene spiegato come evitare tale situazione in futuro.

Quando il log delle transazioni diventa pieno, SQL Server motore di database genera un errore 9002. Il log può riempirsi quando il database è online o in stato di recupero. Se il log si riempie quando il database è online, il database rimane online, ma può solo essere letto, non aggiornato. Se il log viene riempito durante il ripristino, il motore di database contrassegna il database come RESOURCE PENDING.If the log fills during recovery, the Database Engine contrassegna il database come RESOURCE PENDING. In entrambi i casi, è richiesto che l'utente intervenga per liberare spazio nel log.

Nota

Questo articolo è incentrato sulle SQL Server. Per informazioni più specifiche su questo errore in Azure SQL Database e Istanza gestita di SQL di Azure, vedere Risoluzione degli errori del log delle transazioni con Azure SQL Database e Istanza gestita di SQL di Azure. Azure SQL Database e Istanza gestita di SQL di Azure si basano sulla versione stabile più recente del motore di database di Microsoft SQL Server, gran parte del contenuto è simile anche se le opzioni e gli strumenti per la risoluzione dei problemi possono differire.

Motivi comuni per un log delle transazioni completo

La risposta appropriata a un log delle transazioni completo dipende dalle condizioni che hanno causato il riempimento del log. Le cause comuni includono:

  • Log non troncato
  • Il volume del disco è pieno
  • La dimensione del log è impostata su un valore massimo fisso o l'aumento automatico è disabilitato
  • Replica o sincronizzazione del gruppo di disponibilità che non è in grado di completare

Come risolvere un log delle transazioni completo

I passaggi specifici seguenti consentono di trovare il motivo di un log delle transazioni completo e di risolvere il problema.

1. Troncare il log

Una soluzione molto comune a questo problema consiste nel garantire che i backup del log delle transazioni vengano eseguiti per il database, in modo da garantire che il log venga troncato. Se non viene indicata alcuna cronologia del log delle transazioni recente per il database con un log delle transazioni completo, la soluzione al problema è semplice: riprendere i backup regolari del log delle transazioni del database.

Spiegazione del troncamento del log

Esiste una differenza tra il troncamento di un log delle transazioni e la compattazione di un log delle transazioni. Il troncamento del log si verifica normalmente durante un backup del log delle transazioni ed è un'operazione logica che rimuove i record di cui è stato eseguito il commit all'interno del log, mentre la compattazione del log recupera lo spazio fisico nel file system riducendo le dimensioni del file. Il troncamento del log si verifica in un limite VLF (Virtual-Log-File) e un file di log può contenere molte VVLF. Un file di log può essere ridotto solo se è presente spazio vuoto all'interno del file di log da recuperare. La compattazione di un file di log da solo non consente di risolvere il problema di un file di log completo, ma è necessario individuare il motivo per cui il file di log è pieno e non può essere troncato.

Avviso

I dati spostati per ridurre un file possono essere dispersi in qualsiasi percorso disponibile nel file, Ciò causa la frammentazione dell'indice e potrebbe rallentare le prestazioni delle query che eseguono la ricerca in un intervallo dell'indice. Per eliminare la frammentazione, valutare la possibilità di ricompilare gli indici sul file dopo la compattazione. Per altre informazioni, vedere Compattare un database.

Che cos'è la prevenzione del troncamento del log?

Per individuare gli elementi che impediscono il troncamento del log in un determinato caso, usare le log_reuse_wait colonne e log_reuse_wait_desc della vista del sys.databases catalogo. Per altre informazioni, vedere sys.databases (Transact-SQL). Per le descrizioni dei fattori che possono ritardare il troncamento del log, vedere Log delle transazioni (SQL Server).

Il set di comandi T-SQL seguente consente di identificare se un log delle transazioni del database non viene troncato e il motivo. Lo script seguente consiglia anche i passaggi per risolvere il problema:

SET NOCOUNT ON
DECLARE @SQL VARCHAR (8000), @log_reuse_wait tinyint, @log_reuse_wait_desc nvarchar(120), @dbname sysname, @database_id int, @recovery_model_desc varchar (24)


IF ( OBJECT_id (N'tempdb..#CannotTruncateLog_Db') is not null)
BEGIN
    DROP TABLE #CannotTruncateLog_Db
END


--get info about transaction logs in each db. Use a DMV which supports all supported versions

IF ( OBJECT_id (N'tempdb..#dm_db_log_space_usage') is not null)
BEGIN
    DROP TABLE #dm_db_log_space_usage 
END
SELECT * INTO #dm_db_log_space_usage FROM sys.dm_db_log_space_usage where 1=0

DECLARE log_space CURSOR FOR SELECT NAME FROM sys.databases
OPEN log_space 

FETCH NEXT FROM log_space into @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

	set @SQL = '
	insert into #dm_db_log_space_usage (
	database_id, 
	total_log_size_in_bytes, 
	used_log_space_in_bytes, 
	used_log_space_in_percent, 
	log_space_in_bytes_since_last_backup
	)
	select
	database_id, 
	total_log_size_in_bytes, 
	used_log_space_in_bytes, 
	used_log_space_in_percent, 
	log_space_in_bytes_since_last_backup
	from ' + @dbname +'.sys.dm_db_log_space_usage'

	
	BEGIN TRY  
		exec (@SQL)
	END TRY  

	BEGIN CATCH  
        SELECT ERROR_MESSAGE() AS ErrorMessage;  
	END CATCH;

	FETCH NEXT FROM log_space into @dbname
END

CLOSE log_space 
DEALLOCATE log_space 

--select the affected databases 
SELECT 
    sdb.name as DbName, 
    sdb.log_reuse_wait, sdb.log_reuse_wait_desc, 
    log_reuse_wait_explanation = CASE

        WHEN log_reuse_wait = 1 THEN 'No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond'
        WHEN log_reuse_wait = 2 THEN 'A log backup is required before the transaction log can be truncated.'
        WHEN log_reuse_wait = 3 THEN 'A data backup or a restore is in progress (all recovery models). Please wait or cancel backup'
        WHEN log_reuse_wait = 4 THEN 'A long-running active transaction or a defferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
        WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'        
        WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'        

        WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
        WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
        WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
        WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
        WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
    ELSE 'None' END,

    sdb.database_id,
    sdb.recovery_model_desc,
    lsu.used_log_space_in_bytes/1024 as Used_log_size_MB,
	lsu.total_log_size_in_bytes /1024 as Total_log_size_MB,
    100 - lsu.used_log_space_in_percent as Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb INNER JOIN #dm_db_log_space_usage lsu ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0

SELECT * FROM #CannotTruncateLog_Db 


DECLARE no_truncate_db CURSOR FOR
    SELECT log_reuse_wait, log_reuse_wait_desc, DbName, database_id, recovery_model_desc FROM #CannotTruncateLog_Db;


OPEN no_truncate_db

FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

WHILE @@FETCH_STATUS = 0
BEGIN
    if (@log_reuse_wait > 0)
        select '-- ''' + @dbname +  ''' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --'  as 'Individual Database Report'


    if (@log_reuse_wait = 1)
    BEGIN
        select 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' as Recommendation
        select 'USE ''' + @dbname+ '''; CHECKPOINT' as CheckpointCommand
        select 'select * from sys.dm_db_log_info(' + CONVERT(varchar,@database_id)+ ')' as VLF_LogInfo
    END
    else if (@log_reuse_wait = 2)
    BEGIN
        select 'Is '+ @recovery_model_desc +' recovery model the intended choice for ''' + @dbname+ ''' database? Review recovery models and determine if you need to change it. https://learn.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server' as RecoveryModelChoice
        select 'To truncate the log consider performing a transaction log backup on database ''' + @dbname+ ''' which is in ' + @recovery_model_desc +' recovery model. Be mindful of any existing log backup chains that could be broken' as Recommendation
        select 'BACKUP LOG [' + @dbname + '] TO DISK = ''some_volume:\some_folder\' + @dbname + '_LOG.trn ''' as BackupLogCommand
    END
    else if (@log_reuse_wait = 3)
    BEGIN
        select 'Either wait for or cancel any active backups currently running for database ''' +@dbname+ '''. To check for backups, run this command:' as Recommendation
        select 'select * from sys.dm_exec_requests where command like ''backup%'' or command like ''restore%''' as FindBackupOrRestore
    END
    else if (@log_reuse_wait = 4)
    BEGIN
        select 'Active transactions currently running  for database ''' +@dbname+ '''. To check for active transactions, run these commands:' as Recommendation
        select 'DBCC OPENTRAN (''' +@dbname+ ''')' as FindOpenTran
        select 'select database_id, db_name(database_id) dbname, database_transaction_begin_time, database_transaction_state, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_begin_lsn, stran.session_id from sys.dm_tran_database_transactions dbtran left outer join sys.dm_tran_session_transactions stran on dbtran.transaction_id = stran.transaction_id where database_id = ' + CONVERT(varchar, @database_id) as FindOpenTransAndSession
    END

    else if (@log_reuse_wait = 5)
    BEGIN
        select 'Database Mirroring for database ''' +@dbname+ ''' is behind on synchronization. To check the state of DBM, run the commands below:' as Recommendation
        select 'select db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc from sys.database_mirroring where mirroring_guid is not null and mirroring_state <> 4 and database_id = ' + convert(sysname, @database_id)  as CheckMirroringStatus
        
        select 'Database Mirroring for database ''' +@dbname+ ''' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' as Recommendation
        select 'exec msdb.sys.sp_dbmmonitoraddmonitoring 1; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ ''', 5, 0; waitfor delay ''00:01:01''; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ '''; exec msdb.sys.sp_dbmmonitordropmonitoring'   as CheckMirroringStatusAnd
    END

    else if (@log_reuse_wait = 6)
    BEGIN
        select 'Replication transactions still undelivered from publisher database ''' +@dbname+ ''' to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encoutered any errors' as Recommendation
        select 'DBCC OPENTRAN  (''' + @dbname + ''')' as CheckOldestNonDistributedTran
        select 'select top 5 * from distribution..MSlogreader_history where runstatus in (6, 5) or error_id <> 0 and agent_id = find_in_mslogreader_agents_table  order by time desc ' as LogReaderAgentState
    END
    
    else if (@log_reuse_wait = 9)
    BEGIN
        select 'Always On transactions still undelivered from primary database ''' +@dbname+ ''' to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries' as Recommendation
        select 'select availability_group=cast(ag.name as varchar(30)), primary_replica=cast(ags.primary_replica as varchar(30)),primary_recovery_health_desc=cast(ags.primary_recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ags.synchronization_health_desc as varchar(30)),ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc=cast(ag.automated_backup_preference_desc as varchar(10))  from sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id' as CheckAGHealth
        select 'SELECT  group_name=cast(arc.group_name as varchar(30)), replica_server_name=cast(arc.replica_server_name as varchar(30)), node_name=cast(arc.node_name as varchar(30)),role_desc=cast(ars.role_desc as varchar(30)), ar.availability_mode_Desc, operational_state_desc=cast(ars.operational_state_desc as varchar(30)), connected_state_desc=cast(ars.connected_state_desc as varchar(30)), recovery_health_desc=cast(ars.recovery_health_desc as varchar(30)), synhcronization_health_desc=cast(ars.synchronization_health_desc as varchar(30)), ars.last_connect_error_number, last_connect_error_description=cast(ars.last_connect_error_description as varchar(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc=cast(ar.primary_role_allow_connections_desc as varchar(30)) from sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY cast(arc.group_name as varchar(30)), cast(ars.role_desc as varchar(30))' as CheckReplicaHealth
        select 'select database_name=cast(drcs.database_name as varchar(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local,drcs.is_failover_ready,drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc=cast(drs.suspend_reason_desc as varchar(30)), synchronization_state_desc=cast(drs.synchronization_state_desc as varchar(30)), synchronization_health_desc=cast(drs.synchronization_health_desc as varchar(30)), database_state_desc=cast(drs.database_state_desc as varchar(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time,drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time from sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id  order by drs.database_id' as LogMovementHealth
        select 'For more information see https://learn.microsoft.com/troubleshoot/sql/availability-groups/error-9002-transaction-log-large' as OnlineDOCResource
    END    
    else if (@log_reuse_wait in (10, 11, 12, 14))
    BEGIN
        select 'This state is not documented and is expected to be rare and short-lived' as Recommendation
    END    
    else if (@log_reuse_wait = 13)
    BEGIN
        select 'The oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation.' as Finding
        select 'This state should be short-lived, but if you find it is taking a long time, you can consider disabling Indirect Checkpoint temporarily' as Recommendation
        select 'ALTER DATABASE [' +@dbname+ '] SET TARGET_RECOVERY_TIME = 0' as DisableIndirectCheckpointTemporarily
    END    
    else if (@log_reuse_wait = 16)
    BEGIN
        select 'For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables)' as Finding
        select 'Review https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/' as ReviewBlog
        select 'use ' +@dbname+ ' CHECKPOINT' as RunCheckpoint
    END    

    FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

END

CLOSE no_truncate_db
DEALLOCATE no_truncate_db

Importante

Se l'errore 9002 si è verificato durante il recupero del database, risolvere il problema, quindi recuperare il database tramite ALTER DATABASE nome_database SET ONLINE.

LOG_BACKUP log_reuse_wait

Le azioni più comuni che è possibile prendere in considerazione sono esaminare il modello di recupero del database e eseguire il backup del log delle transazioni del database.

Prendere in considerazione il modello di recupero del database

Il log delle transazioni potrebbe non riuscire a troncare con LOG_BACKUP log_reuse_wait categoria, perché non è mai stato eseguito il backup. In molti di questi casi, il database usa il modello di recupero FULL o BULK_LOGGED, ma non è stato eseguito il backup dei log delle transazioni. È consigliabile considerare attentamente ogni modello di recupero del database: eseguire backup del log delle transazioni in tutti i database nei modelli di recupero FULL o BULK LOGGED per ridurre al minimo le occorrenze dell'errore 9002. Per altre informazioni, vedere Modelli di recupero.

Eseguire il backup del log

Nel modello di recupero FULL o BULK_LOGGED, se il log delle transazioni non è stato sottoposto a backup di recente, il backup potrebbe impedire il troncamento del log. È necessario eseguire il backup del log delle transazioni per consentire il rilascio dei record di log e il troncamento del log. Se il backup del log non è mai stato eseguito, è necessario creare due backup del log per consentire al motore di database di troncare il log al punto dell'ultimo backup. Il troncamento del log libera spazio logico per i nuovi record di log. Per evitare che il log si riempia di nuovo, eseguire backup del log regolarmente e più frequentemente. Per altre informazioni, vedere Modelli di recupero.

Una cronologia completa di tutte le operazioni di backup e ripristino di SQL Server in un'istanza del msdb server viene archiviata nel database di sistema. Per esaminare la cronologia di backup completa di un database, usare lo script di esempio seguente:

SELECT bs.database_name
, backuptype = CASE 
	WHEN bs.type = 'D' and bs.is_copy_only = 0 THEN 'Full Database'
	WHEN bs.type = 'D' and bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
	WHEN bs.type = 'I' THEN 'Differential database backup'
	WHEN bs.type = 'L' THEN 'Transaction Log'
	WHEN bs.type = 'F' THEN 'File or filegroup'
	WHEN bs.type = 'G' THEN 'Differential file'
	WHEN bs.type = 'P' THEN 'Partial'
	WHEN bs.type = 'Q' THEN 'Differential partial' END + ' Backup'
, bs.recovery_model
, BackupStartDate = bs.Backup_Start_Date
, BackupFinishDate = bs.Backup_Finish_Date
, LatestBackupLocation = bf.physical_device_name
, backup_size_mb = bs.backup_size/1024./1024.
, compressed_backup_size_mb = bs.compressed_backup_size/1024./1024.
, database_backup_lsn -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on. 
, checkpoint_lsn
, begins_log_chain
FROM msdb.dbo.backupset bs	
LEFT OUTER JOIN msdb.dbo.backupmediafamily bf ON bs.[media_set_id] = bf.[media_set_id]
WHERE recovery_model in ('FULL', 'BULK-LOGGED')
AND bs.backup_start_date > DATEADD(month, -2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name asc, bs.Backup_Start_Date desc;

Una cronologia completa di tutte le operazioni di backup e ripristino di SQL Server in un'istanza del msdb server viene archiviata nel database di sistema. Per altre informazioni sulla cronologia dei backup, vedere Informazioni sulla cronologia dei backup e sull'intestazione (SQL Server).

Creare un backup del log delle transazioni

Esempio di come eseguire il backup del log:

BACKUP LOG [dbname] TO DISK = 'some_volume:\some_folder\dbname_LOG.trn'

Importante

Se il database è danneggiato, vedere Backup della parte finale del log (SQL Server).

ACTIVE_TRANSACTION log_reuse_wait

I passaggi per risolvere i problemi ACTIVE_TRANSACTION motivo includono l'individuazione della transazione a esecuzione prolungata e la relativa risoluzione (in alcuni casi usando il comando KILL per farlo).

Individuare le transazioni a esecuzione prolungata

Una transazione a esecuzione molto prolungata può comportare il riempimento del log delle transazioni. Per individuare le transazioni con esecuzione prolungata, utilizzare una delle alternative seguenti:

Questa vista a gestione dinamica restituisce informazioni sulle transazioni a livello di database. Per una transazione a esecuzione prolungata, le colonne particolarmente rilevanti sono quelle che indicano l'ora del primo record di log (database_transaction_begin_time), lo stato corrente della transazione (database_transaction_state)e il numero di sequenza del file di log (LSN) del record iniziale nel log delle transazioni (database_transaction_begin_lsn).

  • DBCC OPENTRAN. Questa istruzione consente di identificare l'ID utente del proprietario della transazione, in modo da risalire, se lo si desidera, all'origine della transazione per terminarla in modo più appropriato, ovvero tramite il commit invece del rollback.
Terminare una transazione

A volte è sufficiente terminare la transazione; potrebbe essere necessario usare l'istruzione KILL . Usare l'istruzione con cautela, soprattutto quando sono in esecuzione processi importanti che non vanno terminati. Per altre informazioni, vedere KILL (Transact-SQL)

AVAILABILITY_REPLICA log_reuse_wait

Quando le modifiche delle transazioni nella replica di disponibilità primaria non sono ancora state elaborate nella replica secondaria, il log delle transazioni nella replica primaria non può essere troncato. Ciò può causare l'aumento del log e può verificarsi se la replica secondaria è impostata per la modalità commit sincrono o asincrona. Per informazioni su come risolvere questo tipo di problema, vedere Errore 9002. Il log delle transazioni per il database è pieno a causa di AVAILABILITY_REPLICA errore

CHECKPOINT log_reuse_wait

Non si è verificato alcun checkpoint dall'ultimo troncamento del log oppure l'inizio del log non è stato ancora spostato oltre un file di log virtuale (VLF). (Tutti i modelli di recupero)

Si tratta di una motivazione comune per il posticipo del troncamento del log. Se ritardato, prendere in considerazione l'esecuzione del CHECKPOINT comando nel database o l'analisi delle VVLF del log.

USE dbname; CHECKPOINT
select * from sys.dm_db_log_info(db_id('dbname'))

Per altre informazioni sui fattori di log_reuse_wait

Per altri dettagli, vedere Fattori che possono ritardare il troncamento del log

2. Risolvere il volume del disco completo

In alcune situazioni il volume del disco che ospita il file di log delle transazioni può riempire. È possibile eseguire una delle azioni seguenti per risolvere lo scenario log-full risultante da un disco completo:

Spazio libero su disco

Potrebbe essere possibile liberare spazio sull'unità disco contenente il file del log delle transazioni per il database, eliminando o spostando altri file. L'aumento dello spazio disponibile su disco consente al sistema di recupero di ingrandire automaticamente il file di log.

Spostare il file di log in un altro disco

Se non é possibile liberare spazio su disco sufficiente nell'unità che attualmente contiene il file di log, prendere in considerazione lo spostamento del file in un'altra unità con spazio adeguato.

Importante

È consigliabile non memorizzare mai file di log in file system compressi.

Per informazioni su come modificare il percorso di un file di log, vedere Spostare file di database .

Aggiungere un file di log a un altro disco

Aggiungere un nuovo file di log al database in un disco diverso con spazio sufficiente usando ALTER DATABASE <database_name> ADD LOG FILE. Più file di log per un singolo database devono essere considerati una condizione temporanea per risolvere un problema di spazio, non una condizione a lungo termine. La maggior parte dei database deve avere un solo file di log delle transazioni. Continuare a esaminare il motivo per cui il log delle transazioni è pieno e non può essere troncato. È consigliabile aggiungere file di log delle transazioni aggiuntivi temporanei come passaggio avanzato per la risoluzione dei problemi.

Per altre informazioni, vedere Aggiungere dati o file di log a un database.

Questi passaggi possono essere parzialmente automatizzati eseguendo questo script T-SQL che identificherà i file di log che usano una grande percentuale di spazio su disco e suggerire azioni:

DECLARE @log_reached_disk_size BIT = 0

SELECT 
    name LogName, 
    physical_name, 
    CONVERT(bigint, size)*8/1024 LogFile_Size_MB, 
    volume_mount_point, 
    available_bytes/1024/1024 Available_Disk_space_MB,
    (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 file_size_as_percentage_of_disk_space,
    db_name(mf.database_id) DbName
FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats (mf.database_id, file_id)
WHERE mf.[type_desc] = 'LOG'
    AND (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 > 90 --log is 90% of disk drive
ORDER BY size DESC

if @@ROWCOUNT > 0
BEGIN

    set @log_reached_disk_size = 1

    -- Discover if any logs have are close to or completely filled disk volume they reside on.
    -- Either Add A New File To A New Drive, Or Shrink Existing File
    -- If Cannot Shrink, Go To Cannot Truncate Section

    DECLARE @db_name_filled_disk sysname, @log_name_filled_disk sysname, @go_beyond_size bigint 
    
    DECLARE log_filled_disk CURSOR FOR
        SELECT 
            db_name(mf.database_id),
            name
        FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats (mf.database_id, file_id)
        WHERE mf.[type_desc] = 'LOG'
            AND (convert(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 > 90 --log is 90% of disk drive
        ORDER BY size desc

    OPEN log_filled_disk

    FETCH NEXT FROM log_filled_disk into @db_name_filled_disk , @log_name_filled_disk

    WHILE @@FETCH_STATUS = 0
    BEGIN
        
        SELECT 'Transaction log for database "' + @db_name_filled_disk + '" has nearly or completely filled disk volume it resides on!' AS Finding
        SELECT 'Consider using one of the below commands to shrink the "' + @log_name_filled_disk +'" transaction log file size or add a new file to a NEW volume' AS Recommendation
        SELECT 'DBCC SHRINKFILE(''' + @log_name_filled_disk + ''')' AS Shrinkfile_Command
        SELECT 'ALTER DATABASE ' + @db_name_filled_disk + ' ADD LOG FILE ( NAME = N''' + @log_name_filled_disk + '_new'', FILENAME = N''NEW_VOLUME_AND_FOLDER_LOCATION\' + @log_name_filled_disk + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile
        SELECT 'If shrink does not reduce the file size, likely it is because it has not been truncated. Please review next section below. See https://learn.microsoft.com/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql' AS TruncateFirst
        SELECT 'Can you free some disk space on this volume? If so, do this to allow for the log to continue growing when needed.' AS FreeDiskSpace




         FETCH NEXT FROM log_filled_disk into @db_name_filled_disk , @log_name_filled_disk

    END

    CLOSE log_filled_disk
    DEALLOCATE log_filled_disk

END

3. Modificare il limite delle dimensioni del log o abilitare l'aumento automatico

L'errore 9002 può essere generato se le dimensioni del log delle transazioni sono state impostate su un limite superiore o Autogrow non è consentito. In questo caso, l'abilitazione della crescita automatica o l'aumento manuale delle dimensioni del log possono aiutare a risolvere il problema. Usare questo comando T-SQL per trovare tali file di log e seguire le raccomandazioni fornite:

SELECT DB_NAME(database_id) DbName,
       name LogName,
       physical_name,
       type_desc ,
       CONVERT(bigint, SIZE)*8/1024 LogFile_Size_MB ,
       CONVERT(bigint,max_size)*8/1024 LogFile_MaxSize_MB ,
       (SIZE*8.0/1024)/(max_size*8.0/1024)*100 percent_full_of_max_size,
       CASE WHEN growth = 0 THEN 'AUTOGROW_DISABLED' ELSE 'Autogrow_Enabled' END as AutoGrow
FROM sys.master_files
WHERE file_id = 2
    AND (SIZE*8.0/1024)/(max_size*8.0/1024)*100 > 90
    AND max_size not in (-1, 268435456)
    OR growth = 0

if @@ROWCOUNT > 0
BEGIN
    DECLARE @db_name_max_size sysname, @log_name_max_size sysname, @configured_max_log_boundary bigint, @auto_grow int
    
    DECLARE reached_max_size CURSOR FOR
        SELECT db_name(database_id),
               name,
               CONVERT(bigint, SIZE)*8/1024,
               growth
        FROM sys.master_files
        WHERE file_id = 2
            AND ( (SIZE*8.0/1024)/(max_size*8.0/1024)*100 > 90
            AND max_size not in (-1, 268435456)
            OR growth = 0 )


    OPEN reached_max_size

    FETCH NEXT FROM reached_max_size into @db_name_max_size , @log_name_max_size, @configured_max_log_boundary, @auto_grow 

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @auto_grow = 0
          BEGIN
            SELECT 'The database "' + @db_name_max_size+'" contains a log file "' + @log_name_max_size + '" whose autogrow has been DISABLED' as Finding
            SELECT 'Consider enabling autogrow or increasing file size via these ALTER DATABASE commands' as Recommendation
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', FILEGROWTH = 65536KB)' as AutoGrowth
          END
        ELSE
          BEGIN
            SELECT 'The database "' + @db_name_max_size+'" contains a log file "' + @log_name_max_size + '" whose max limit is set to ' + convert(varchar(24), @configured_max_log_boundary) + ' MB and this limit has been reached!' as Finding
            SELECT 'Consider using one of the below ALTER DATABASE commands to either change the log file size or add a new file' as Recommendation
          END
        
        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = UNLIMITED)' as UnlimitedSize
        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = something_larger_than_' + CONVERT(varchar(24), @configured_max_log_boundary) +'MB )' as IncreasedSize
        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' ADD LOG FILE ( NAME = N''' + @log_name_max_size + '_new'', FILENAME = N''SOME_FOLDER_LOCATION\' + @log_name_max_size + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' as AddNewFile

        FETCH NEXT FROM reached_max_size into @db_name_max_size , @log_name_max_size, @configured_max_log_boundary, @auto_grow

    END

    CLOSE reached_max_size
    DEALLOCATE reached_max_size
END
ELSE
    SELECT 'Found no files that have reached max log file size' as Findings

Aumentare le dimensioni del file di log o abilitare l'aumento automatico

Se nel disco del log è disponibile spazio, è possibile aumentare le dimensioni del file di log. La dimensione massima per i file di log è due terabyte per file di log.

Se l'aumento automatico è disabilitato, il database è online e lo spazio sufficiente è disponibile sul disco, eseguire una delle operazioni seguenti:

Nota

In entrambi i casi, se sono state raggiunte le dimensioni massime consentite correnti, aumentare il valore MAXSIZE.

Vedi anche

ALTER DATABASE (Transact-SQL)
Gestione delle dimensioni del file di log delle transazioni
Backup di log delle transazioni (SQL Server)
sp_add_log_file_recover_suspect_db (Transact-SQL)
MSSQLSERVER_9002
Come una struttura di file di log può influire sul tempo di ripristino del database - Microsoft Tech Community