Problemen met transactielogboeken met Azure SQL Managed Instance oplossen

  • Artikel

Van toepassing op: Azure SQL Managed Instance

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, beheerd door Azure SQL Managed Instance, 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 Managed Instance. Azure SQL Managed Instance 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 Database oplossen voor meer informatie over het oplossen van problemen met een transactielogboek in Azure SQL Database.

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 Managed Instance worden back-ups van transactielogboeken automatisch gemaakt. Zie Automatische back-ups voor frequentie, retentie en meer informatie. Als u wilt bijhouden wanneer geautomatiseerde back-ups zijn uitgevoerd op een met SQL beheerd exemplaar, controleert u de back-upactiviteit bewaken.

De locatie en naam van databasebestanden kunnen niet worden beheerd, maar beheerders kunnen databasebestanden en instellingen voor automatische groei van bestanden beheren. De typische oorzaken en oplossingen van problemen met transactielogboeken zijn vergelijkbaar met SQL Server.

Net als bij SQL Server wordt het transactielogboek voor elke database afgekapt wanneer een logboekback-up is voltooid. Logboekafkapping verwijdert inactieve virtuele logboekbestanden (VLF's) uit het transactielogboek, waardoor ruimte in het bestand vrijkomt, maar niet de grootte van het bestand op schijf wordt gewijzigd. De lege ruimte in het logboekbestand kan vervolgens 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 Managed Instance, mislukken nieuwe schrijftransacties.

In Azure SQL Managed Instance kunt u invoegtoepassingsopslag aanschaffen, onafhankelijk van rekenkracht, tot een limiet. Zie Bestandbeheer voor meer informatie om meer ruimte vrij te maken.

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;

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 Managed Instance kan zich voordoen als replicatie of CDC is ingeschakeld. Als dit wordt ondersteund, onderzoekt u agents die betrokken zijn bij CDC of replicatie. Voor het oplossen van problemen met CDC voert u query's uit op taken in msdb.dbo.cdc_jobs. Als deze niet aanwezig is, voegt u deze toe via sys.sp_cdc_add_job. Zie Problemen met transactionele replicatie oplossen voor replicatie. 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 van is NULL, 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
, 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 Azure SQL Managed Instance, kan ruimte vrijmaken 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.

In Azure SQL Managed Instance kunt u invoegtoepassingsopslag aanschaffen, onafhankelijk van rekenkracht, tot een limiet. Open bijvoorbeeld in Azure Portal de pagina Compute en opslag om de opslag in GB te verhogen. Zie resourcelimieten voor SQL Managed Instance voor informatie over limieten voor transactielogboekgrootten. Zie Bestandsruimte beheren voor databases in Azure SQL Managed Instance voor meer informatie.

Back-upopslag wordt niet afgetrokken van de opslagruimte van uw met SQL beheerde exemplaar. De back-upopslag is onafhankelijk van de opslagruimte van het exemplaar en is niet beperkt in grootte.

Fout 9002: Het transactielogboek voor de database is vol

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Fout 9002 treedt op in SQL Server en in Azure SQL Managed Instance om dezelfde redenen.

Het juiste antwoord op een volledig transactielogboek is afhankelijk van de omstandigheden die ervoor hebben gezorgd dat het logboek vol raakt.

Probeer de volgende methoden om fout 9002 op te lossen:

  • Transactielogboek wordt niet afgekapt en is gegroeid om alle beschikbare ruimte te vullen.
    • Omdat back-ups van transactielogboeken in Azure SQL Managed Instance automatisch zijn, moet de transactielogboekactiviteit anders worden afgekapt. Onvolledige replicatie, CDC of synchronisatie van beschikbaarheidsgroepen verhinderen mogelijk afkapping, zie Voorkomen dat transactielogboeken worden afgekapt.
  • De gereserveerde opslaggrootte van het beheerde SQL-exemplaar is vol en het transactielogboek kan niet worden uitgebreid.
  • De grootte van het transactielogboek is ingesteld op een vaste maximumwaarde of automatische groei is uitgeschakeld, en kan dus niet groeien.

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.

Hoewel fout 9002 vaker voorkomt dan fout 40552 in Azure SQL Managed Instance, kunnen beide optreden.

Probeer de volgende methoden om fout 40552 op te lossen:

  • 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 toepassingen te verbeteren voor meer informatie.
  • 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.
  • 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.
  • 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.

Volgende stappen