Problemen met transactielogboeken met Azure SQL Database oplossen

  • Artikel

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.

Notitie

Dit artikel is gericht op Azure SQL Database. Azure SQL Database is gebaseerd op de nieuwste stabiele versie van de Microsoft SQL Server-database-engine, dus veel van de inhoud is vergelijkbaar, hoewel de opties en hulpprogramma's voor probleemoplossing kunnen verschillen van SQL Server.

Zie Problemen met transactielogboeken in Azure SQL Managed Instance oplossen voor meer informatie over het oplossen van problemen met een transactielogboek in Azure SQL Managed Instance.

Zie Problemen met een volledig transactielogboek oplossen (SQL Server-fout 9002) voor meer informatie over het oplossen van problemen met een transactielogboek in SQL Server.

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:

Voorkomen dat transactielogboeken worden afgekapt

Als u wilt ontdekken wat het afkappen van logboeken 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 in sys.databases kunnen de reden aangeven waarom het afkappen van log_reuse_wait_desc het transactielogboek van de database wordt voorkomen:

log_reuse_wait_desc Diagnose Antwoord vereist
NIETS Typische status. Er wordt niets geblokkeerd dat het logboek wordt afgekapt. Nee
CHECKPOINT Er is een controlepunt nodig voor het afkappen van logboeken. Zeldzame. Er is geen reactie vereist, tenzij deze wordt voortgezet. Als dit wordt ondersteund, dient u een ondersteuningsaanvraag in bij De ondersteuning van Azure.
LOGBOEKBACK-UP Er is een logboekback-up vereist. Er is geen reactie vereist, tenzij deze wordt voortgezet. Als dit wordt ondersteund, dient u een ondersteuningsaanvraag in bij De ondersteuning van Azure.
ACTIEVE BACK-UP OF HERSTEL Er wordt een databaseback-up uitgevoerd. Er is geen reactie vereist, tenzij deze wordt voortgezet. Als dit wordt ondersteund, dient u een ondersteuningsaanvraag in bij De ondersteuning van Azure.
ACTIEVE TRANSACTIE Een lopende transactie verhindert afkapping van logboeken. Het logboekbestand kan niet worden afgekapt vanwege actieve en/of niet-doorgevoerde transacties. Zie de volgende sectie.
REPLICATIE In Azure SQL Database kan dit gebeuren als wijzigingsgegevensopname (CDC) is ingeschakeld. Query's uitvoeren sys.dm_cdc_errors en fouten oplossen. Als dit niet mogelijk is, dient u een ondersteuningsaanvraag in bij De ondersteuning van Azure.
AVAILABILITY_REPLICA Synchronisatie met de secundaire replica wordt uitgevoerd. Er is geen reactie vereist, tenzij deze wordt voortgezet. Als dit wordt ondersteund, dient u een ondersteuningsaanvraag in bij De ondersteuning van Azure.

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 uit 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 de event_infosys.dm_exec_input_buffer 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 elastische Pools van Azure SQL Database, kan ruimte vrijmaken voor de elastische pool deel uitmaken van de oplossing. Het oplossen van de hoofdmap van de voorwaarde die het afkappen van het transactielogboekbestand blokkeert, is echter essentieel. In sommige gevallen kan het tijdelijk maken van meer schijfruimte langlopende transacties voltooien, waardoor de voorwaarde voor het blokkeren van het transactielogboekbestand wordt afgekapt met een normale back-up van het transactielogboek. Het vrijmaken van ruimte kan echter slechts 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 Utility voor 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 hervatbare indexen is afkapping van logboeken vaker voorkomt. Zie ALTER INDEX (Transact-SQL) voor meer informatie.

Notitie

Zie Resourcebeheerfouten voor meer informatie over andere resourcebeheerfouten.

Volgende stappen