Problemen met transactielogboeken met Azure SQL Database oplossen

Van toepassing op:Azure SQL Database

Mogelijk ziet u fouten 9002 of 40552 wanneer het transactielogboek vol is en geen nieuwe transacties kan accepteren. Deze fouten treden op wanneer het databasetransactielogboek, dat wordt beheerd door Azure SQL Database, de drempelwaarden voor ruimte overschrijdt en transacties niet kan blijven accepteren. Deze fouten zijn vergelijkbaar met problemen met een volledig transactielogboek in SQL Server, maar hebben verschillende oplossingen in SQL Server, Azure SQL Database en Azure SQL Managed Instance.

Geautomatiseerde back-ups en het transactielogboek

In Azure SQL Database worden back-ups van transactielogboeken automatisch gemaakt. Zie Automatische back-ups voor frequentie, retentie en meer informatie.

Vrije schijfruimte, groei van databasebestanden en bestandslocatie worden ook beheerd, dus de typische oorzaken en oplossingen van transactielogboekproblemen verschillen van SQL Server.

Net als bij SQL Server wordt het transactielogboek voor elke database afgekapt wanneer een logboekback-up is voltooid. Door afkapping blijft er lege ruimte in het logboekbestand, die vervolgens kan worden gebruikt voor nieuwe transacties. Wanneer het logboekbestand niet kan worden afgekapt door logboekback-ups, groeit het logboekbestand voor nieuwe transacties. Als het logboekbestand toeneemt tot de maximale limiet in Azure SQL Database, mislukken nieuwe schrijftransacties.

Zie voor meer informatie over transactielogboekgrootten:

• Zie voor vCore-resourcelimieten voor één database resourcelimieten voor individuele databases met behulp van het vCore-aankoopmodel.
• Voor resourcelimieten voor elastische pools met betrekking tot vCore, zie resourcelimieten voor elastische pools met behulp van het vCore-aankoopmodel.
• Zie resourcelimieten voor individuele databases met behulp van het DTU-aankoopmodel.
• Zie resourcelimieten voor elastische pools met behulp van het DTU-aankoopmodel voor DTU-resourcelimieten voor elastische pools.

Voorkomen dat transactielogboeken worden ingekort

Om te ontdekken wat het afkappen van logs in een bepaald geval verhindert, raadpleegt u log_reuse_wait_desc in sys.databases. Het wachten op opnieuw gebruiken van logboeken laat u weten welke voorwaarden of oorzaken ervoor zorgen dat het transactielogboek niet wordt afgekapt door een normale logboekback-up. Zie sys.databases (Transact-SQL) voor meer informatie.

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Voor Azure SQL Database is het raadzaam om verbinding te maken met een specifieke gebruikersdatabase in plaats van de master database om deze query uit te voeren.

De volgende waarden van log_reuse_wait_desc in sys.databases kunnen aangeven waarom het transactielogboek van de database niet wordt getruncateerd:

Afkapping van logboeken voorkomen door een actieve transactie

Het meest voorkomende scenario voor een transactielogboek dat geen nieuwe transacties kan accepteren, is een langlopende of geblokkeerde transactie.

Voer deze voorbeeldquery uit om niet-doorgevoerde of actieve transacties en de bijbehorende eigenschappen te vinden.

• Retourneert informatie over transactie-eigenschappen, van sys.dm_tran_active_transactions.
• Retourneert sessieverbindingsgegevens van sys.dm_exec_sessions.
• Retourneert aanvraaggegevens (voor actieve aanvragen) van sys.dm_exec_requests. Deze query kan ook worden gebruikt om sessies te identificeren die worden geblokkeerd, zoek naar de request_blocked_by. Zie Blokkerende informatie verzamelen voor meer informatie.
• Retourneert de tekst of invoerbuffertekst van de huidige aanvraag met behulp van de sys.dm_exec_sql_text of sys.dm_exec_input_buffer DMV's. Als de gegevens die worden geretourneerd door het text veld sys.dm_exec_sql_text NULL zijn, is de aanvraag niet actief, maar heeft deze een openstaande transactie. In dat geval bevat het veld event_info van sys.dm_exec_input_buffer de laatste instructie die aan de database-engine is doorgegeven.

SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id 
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, azure_dtc_state    --Applies to: Azure SQL Database only
             =    CASE tat.dtc_state 
                 WHEN 1 THEN 'ACTIVE'
                 WHEN 2 THEN 'PREPARED'
                 WHEN 3 THEN 'COMMITTED'
                 WHEN 4 THEN 'ABORTED'
                 WHEN 5 THEN 'RECOVERED' END
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN 'Distributed transaction or an enlisted bound session transaction.' END
, transaction_uow    --for distributed transactions. 
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS est;

Bestandsbeheer om meer ruimte vrij te maken

Als het transactielogboek niet kan worden afgekapt in de elastische pool van Azure SQL Database, kan ruimte vrijmaken voor de elastische pool deel van de oplossing zijn. Het is echter essentieel om de oorzaak van de voorwaarde die het inkorten van het transactielogbestand blokkeert, op te lossen. In sommige gevallen kan het tijdelijk creëren van meer schijfruimte langlopende transacties laten voltooien, waardoor de blokkering van het transactielogboekbestand wordt opgeheven met een gebruikelijke back-up van het transactielogboek. Het vrijmaken van ruimte kan echter alleen tijdelijke verlichting bieden totdat het transactielogboek weer groeit.

Zie Bestandsruimte beheren voor databases in Azure SQL Database voor meer informatie over het beheren van de bestandsruimte van databases en elastische pools.

Fout 40552: De sessie is beëindigd vanwege overmatig gebruik van transactielogboeken

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

Probeer de volgende methoden om dit op te lossen:

1. Het probleem kan optreden in elke DML-bewerking, zoals invoegen, bijwerken of verwijderen. Controleer de transactie om onnodige schrijfbewerkingen te voorkomen. Probeer het aantal rijen te verminderen waarop onmiddellijk wordt gewerkt door batchverwerking of splitsing in meerdere kleinere transacties te implementeren. Zie Batchverwerking gebruiken om de prestaties van SQL Database-toepassingen te verbeteren voor meer informatie.
2. Het probleem kan optreden vanwege herbouwbewerkingen voor indexen. Om dit probleem te voorkomen, moet u ervoor zorgen dat de volgende formule waar is: (aantal rijen dat in de tabel wordt beïnvloed) vermenigvuldigd met (de gemiddelde grootte van het veld dat is bijgewerkt in bytes + 80) < 2 gigabyte (GB). Voor grote tabellen kunt u overwegen partities te maken en alleen indexonderhoud uit te voeren op sommige partities van de tabel. Zie Gepartitioneerde tabellen en indexen maken voor meer informatie.
3. Als u bulksgewijze invoegingen uitvoert met behulp van het bcp.exe hulpprogramma of de System.Data.SqlClient.SqlBulkCopy klasse, gebruikt u de -b batchsize of BatchSize opties om het aantal rijen te beperken dat in elke transactie naar de server is gekopieerd. Zie bcp Utilityvoor meer informatie.
4. Als u een index opnieuw bouwt met de ALTER INDEX instructie, gebruikt u de SORT_IN_TEMPDB = ONen ONLINE = ONRESUMABLE=ON opties. Met herstelbare indexen komt het inkorten van logboeken vaker voor. Zie ALTER INDEX (Transact-SQL)voor meer informatie.

Verwante inhoud

• Problemen met blokkeren van Azure SQL Database begrijpen en oplossen
• Verbindingsproblemen en andere fouten met Azure SQL Database en Azure SQL Managed Instance oplossen
• Tijdelijke verbindingsfouten in Azure SQL Database en SQL Managed Instance oplossen
• Video: Aanbevolen procedures voor het laden van gegevens in Azure SQL Database