Felsöka transaktionsloggfel med Azure SQL Database

Artikel
10/20/2023

Du kan se felen 9002 eller 40552 när transaktionsloggen är full och inte kan acceptera nya transaktioner. Dessa fel uppstår när databastransaktionsloggen, som hanteras av Azure SQL Database, överskrider tröskelvärdena för utrymme och inte kan fortsätta att acceptera transaktioner. Dessa fel liknar problem med en fullständig transaktionslogg i SQL Server, men har olika lösningar i SQL Server, Azure SQL Database och Azure SQL Managed Instance.

Kommentar

Den här artikeln fokuserar på Azure SQL Database. Azure SQL Database baseras på den senaste stabila versionen av Microsoft SQL Server-databasmotorn, så mycket av innehållet är liknande, även om felsökningsalternativ och verktyg kan skilja sig från SQL Server.

Mer information om hur du felsöker en transaktionslogg i Azure SQL Managed Instance finns i Felsöka transaktionsloggfel med Azure SQL Managed Instance.

Mer information om hur du felsöker en transaktionslogg i SQL Server finns i Felsöka en fullständig transaktionslogg (SQL Server-fel 9002).

Automatiserade säkerhetskopieringar och transaktionsloggen

I Azure SQL Database görs säkerhetskopieringar av transaktionsloggar automatiskt. För frekvens, kvarhållning och mer information, se Automatiserade säkerhetskopior.

Ledigt diskutrymme, databasfiltillväxt och filplats hanteras också, så de vanligaste orsakerna till och lösningarna på transaktionsloggproblem skiljer sig från SQL Server.

På samma sätt som SQL Server trunkeras transaktionsloggen för varje databas när en loggsäkerhetskopia har slutförts. Trunkering lämnar tomt utrymme i loggfilen, som sedan kan användas för nya transaktioner. När loggfilen inte kan trunkeras av loggsäkerhetskopior växer loggfilen för att hantera nya transaktioner. Om loggfilen växer till sin maximala gräns i Azure SQL Database misslyckas nya skrivtransaktioner.

Information om transaktionsloggstorlekar finns i:

Resursgränser för virtuella kärnor för en enskild databas finns i resursgränser för enskilda databaser med köpmodellen för virtuella kärnor.
Resursgränser för virtuella kärnor för elastiska pooler finns i resursgränser för elastiska pooler med köpmodellen för virtuella kärnor.
För DTU-resursgränser för en enskild databas, se resursgränser för enskilda databaser med hjälp av DTU-inköpsmodellen.
För DTU-resursgränser för elastiska pooler, se resursgränser för elastiska pooler med hjälp av DTU-inköpsmodellen.

Förhindrad trunkering av transaktionslogg

Information om vad som förhindrar loggtrunkering i ett visst fall log_reuse_wait_desc finns i i sys.databases. Loggens återanvändningsvänteinformation informerar dig om vilka villkor eller orsaker som hindrar transaktionsloggen från att trunkeras av en vanlig loggsäkerhetskopia. Mer information finns i sys.databases (Transact-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

För Azure SQL Database rekommenderar vi att du ansluter till en specifik användardatabas i stället master för databasen för att köra den här frågan.

Följande värden log_reuse_wait_desc i i sys.databases kan tyda på orsaken till att databasens transaktionsloggtrunkering förhindras:

log_reuse_wait_desc	Diagnos	Svar krävs
INGENTING	Typiskt tillstånd. Det finns inget som hindrar loggen från att trunkera.	Nej.
CHECKPOINT	En kontrollpunkt krävs för loggtrunkering. Sällsynta.	Inget svar krävs om det inte upprätthålls. Om detta är ihållande skickar du en supportbegäran till Azure Support.
LOGGSÄKERHETSKOPIERING	En loggsäkerhetskopia krävs.	Inget svar krävs om det inte upprätthålls. Om detta är ihållande skickar du en supportbegäran till Azure Support.
AKTIV SÄKERHETSKOPIERING ELLER ÅTERSTÄLLNING	En databassäkerhetskopia pågår.	Inget svar krävs om det inte upprätthålls. Om detta är ihållande skickar du en supportbegäran till Azure Support.
AKTIV TRANSAKTION	En pågående transaktion förhindrar loggtrunkering.	Loggfilen kan inte trunkeras på grund av aktiva och/eller ej genererade transaktioner. Se nästa avsnitt.
REPLIKERING	I Azure SQL Database kan detta inträffa om CDC (Change Data Capture) är aktiverat.	Fråga sys.dm_cdc_errors och lös fel. Om det inte går att lösa kan du skicka en supportbegäran till Azure Support.
AVAILABILITY_REPLICA	Synkronisering till den sekundära repliken pågår.	Inget svar krävs om det inte upprätthålls. Om detta är ihållande skickar du en supportbegäran till Azure Support.

Loggtrunkering förhindras av en aktiv transaktion

Det vanligaste scenariot för en transaktionslogg som inte kan acceptera nya transaktioner är en tidskrävande eller blockerad transaktion.

Kör den här exempelfrågan för att hitta ogenomförda eller aktiva transaktioner och deras egenskaper.

Returnerar information om transaktionsegenskaper från sys.dm_tran_active_transactions.
Returnerar sessionsanslutningsinformation från sys.dm_exec_sessions.
Returnerar information om begäran (för aktiva begäranden) från sys.dm_exec_requests. Den här frågan kan också användas för att identifiera sessioner som blockeras, leta request_blocked_byefter . Mer information finns i Samla in blockeringsinformation.
Returnerar den aktuella begärans text eller indatabufferttext med hjälp av sys.dm_exec_sql_text eller sys.dm_exec_input_buffer DMV:er. Om de data som returneras av text fältet sys.dm_exec_sql_text i är NULL är begäran inte aktiv men har en utestående transaktion. I så fall event_info innehåller fältet sys.dm_exec_input_buffer i den sista instruktionen som skickades till databasmotorn.

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;

Filhantering för att frigöra mer utrymme

Om transaktionsloggen hindras från att trunkera i elastiska Azure SQL Database-pooler kan det vara en del av lösningen att frigöra utrymme för den elastiska poolen. Men att lösa roten som villkoret som blockerar trunkering av transaktionsloggfilen är nyckeln. I vissa fall kan du tillfälligt skapa mer diskutrymme så att långvariga transaktioner kan slutföras, vilket tar bort villkoret som blockerar transaktionsloggfilen från att trunkera med en normal säkerhetskopiering av transaktionsloggen. Att frigöra utrymme kan dock bara ge tillfällig lättnad tills transaktionsloggen växer igen.

Mer information om hur du hanterar filutrymmet för databaser och elastiska pooler finns i Hantera filutrymme för databaser i Azure SQL Database.

Fel 40552: Sessionen har avslutats på grund av överdriven användning av transaktionsloggutrymme

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

Prova att lösa problemet med hjälp av följande metoder:

Problemet kan inträffa i alla DML-åtgärder, till exempel infoga, uppdatera eller ta bort. Granska transaktionen för att undvika onödiga skrivningar. Försök att minska antalet rader som körs direkt genom att implementera batchbearbetning eller dela upp i flera mindre transaktioner. Mer information finns i Använda batchbearbetning för att förbättra prestanda för SQL Database-program.
Problemet kan uppstå på grund av åtgärder för att återskapa index. Undvik det här problemet genom att se till att följande formel är sann: (antal rader som påverkas i tabellen) multiplicerat med (den genomsnittliga storleken på fältet som uppdateras i byte + 80) < 2 GIGABYTE (GB). För stora tabeller bör du överväga att skapa partitioner och endast utföra indexunderhåll på vissa partitioner i tabellen. Mer information finns i Skapa partitionerade tabeller och index.
Om du utför massinfogningar med hjälp av bcp.exe verktyget eller System.Data.SqlClient.SqlBulkCopy klassen kan du försöka använda -b batchsize alternativen eller BatchSize för att begränsa antalet rader som kopieras till servern i varje transaktion. Mer information finns i bcp-verktyget.
Om du återskapar ett index med -instruktionen ALTER INDEX använder du SORT_IN_TEMPDB = ONalternativen , ONLINE = ONoch RESUMABLE=ON . Med återupptabara index är loggtrunkering vanligare. Mer information finns i ALTER INDEX (Transact-SQL).

Kommentar

Mer information om andra resursstyrningsfel finns i Resursstyrningsfel.

Share via