Устранение ошибок журнала транзакций с помощью Azure SQL Database

Применимо к:Azure SQL Database

При заполнении журнала транзакций могут появиться ошибки 9002 или 40552 и не могут принимать новые транзакции. Эти ошибки возникают, когда журнал транзакций базы данных, управляемый Базой данных SQL Azure, превышает пороговые значения для пространства и не может продолжать принимать транзакции. Эти ошибки похожи на проблемы с полным журналом транзакций в SQL Server, но имеют различные разрешения в SQL Server, Azure SQL Database и Azure SQL Managed Instance.

Автоматическое резервное копирование и журнал транзакций

В Azure SQL Database резервные копии журналов транзакций выполняются автоматически. Сведения о частоте, хранении и других сведениях см. в статье "Автоматические резервные копии".

Свободное место на диске, рост файлов базы данных и расположение файлов также управляются, поэтому типичные причины и разрешения проблем журнала транзакций отличаются от SQL Server.

Аналогично SQL Server, журнал транзакций для каждой базы данных усечен после успешного завершения резервного копирования журнала. Процесс усечения оставляет пустое пространство в файле журнала, которое может быть использовано для новых транзакций. Если файл журнала не может быть усечен резервными копиями журналов, файл журнала растет для размещения новых транзакций. Если файл журнала увеличивается до максимального предела в Azure SQL Database, новые транзакции записи завершаются ошибкой.

Сведения о размерах журнала транзакций см. в:

• Ограничения ресурсов виртуальных ядер для одной базы данных см. в разделе "Ограничения ресурсов" для отдельных баз данных с помощью модели приобретения виртуальных ядер.
• Ограничения ресурсов виртуальных ядер для эластичных пулов см. в разделе об ограничениях ресурсов для эластичных пулов с помощью модели приобретения виртуальных ядер.
• Сведения об ограничениях ресурсов DTU для одной базы данных см. в разделе об ограничениях ресурсов для отдельных баз данных с помощью модели приобретения DTU.
• Чтобы узнать об ограничениях ресурсов DTU для эластичных пулов, см. в разделе ограничения ресурсов для эластичных пулов, используя модель приобретения DTU.

Предотвращено усечение журнала транзакций

Чтобы узнать, что предотвращает усечение журнала в данном случае, смотрите log_reuse_wait_desc в sys.databases. В сообщении об ожидании повторного использования журнала указаны условия или причины, которые препятствуют усечению журнала транзакций при обычном резервном копировании журнала. Дополнительные сведения см. в разделе sys.databases (Transact-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Для базы данных SQL Azure рекомендуется подключаться к определенной пользовательской базе данных, а не к базе данных master, чтобы выполнить этот запрос.

Следующие значения log_reuse_wait_desc в sys.databases могут указывать причину, по которой предотвращается усечение журнала транзакций базы данных:

Обрезке журнала препятствует активная транзакция

Наиболее распространенный сценарий для журнала транзакций, который не может принимать новые транзакции, является длительной или заблокированной транзакцией.

Выполните этот пример запроса, чтобы найти незафиксированные или активные транзакции и их свойства.

• Возвращает сведения о свойствах транзакций из sys.dm_tran_active_transactions.
• Возвращает сведения о подключении к сеансу из sys.dm_exec_sessions.
• Возвращает сведения о запросе (для активных запросов) из sys.dm_exec_requests. Этот запрос также можно использовать для обнаружения заблокированных сеансов. Для этого найдите request_blocked_by. Дополнительные сведения см. в разделе "Сбор информации о блокировке".
• Возвращает текст текущего запроса или текст из буфера входных данных с помощью представлений DMV sys.dm_exec_sql_text или sys.dm_exec_input_buffer. Если данные, возвращаемые полем textsys.dm_exec_sql_text, равны NULL, запрос не активен, но имеет незавершённую транзакцию. В этом случае event_info поле sys.dm_exec_input_buffer содержит последнюю инструкцию, переданную в движок базы данных.

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;

Управление файлами для освобождения пространства

Если журнал транзакций не усекается в эластичных пулах Azure SQL Database, освобождение места для эластичного пула может быть частью решения. Однако большое значение имеет устранение первопричины блокировки усечения файла журнала транзакций. В некоторых случаях временное создание дополнительного места на диске позволяет выполнять длительные транзакции, удалив условие, блокирующее усечение файла журнала транзакций с помощью обычной резервной копии журнала транзакций. Однако освобождение места может обеспечить только временное облегчение до тех пор, пока журнал транзакций снова не будет расти.

Для получения дополнительной информации об управлении пространством файлов баз данных и эластичных пулов см. раздел Управление файловым пространством для баз данных в Azure SQL Database.

Ошибка 40552: сеанс был завершен в связи с чрезмерным использованием объема журнала транзакций

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

Устранить эту проблему можно с помощью следующих методов.

1. Проблема может возникать в любой операции DML, например вставке, обновлении или удалении. Проверьте транзакцию, чтобы избежать ненужных операций записи. Попробуйте сократить количество строк, которые обрабатываются непосредственно, выполнив пакетную обработку или разделение на несколько меньших транзакций. Дополнительные сведения см. в статье Как повысить производительность приложений базы данных SQL с помощью пакетной обработки.
2. Эта проблема может возникать из-за операций перестроения индекса. Чтобы избежать этой проблемы, убедитесь, что следующая формула верна: (число затрагиваемых строк в таблице), умноженное на (средний размер обновляемого поля в байтах + 80) < 2 гигабайта (ГБ). Для больших таблиц можно создать разделы и выполнить обслуживание индекса только в некоторых разделах таблицы. Дополнительные сведения см. в статье Создание секционированных таблиц и индексов.
3. Если вы выполняете bcp.exe массовую вставку с помощью служебной программы или класса SqlBulkCopy (доступными как в Microsoft.Data.SqlClient, так и в System.Data.SqlClient), попробуйте использовать параметры -b batchsize или BatchSize, чтобы ограничить количество строк, скопированных на сервер в каждой транзакции. Дополнительные сведения см. в разделе bcp Utility.
4. Если вы перестраиваете индекс с помощью оператора ALTER INDEX, используйте параметры SORT_IN_TEMPDB = ON, ONLINE = ON и RESUMABLE=ON. С возобновляемыми индексами усечение журнала происходит чаще. Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).

Связанный контент

• Понимание и разрешение проблем блокировки в Azure SQL Database
• Устранение проблем с подключением и других ошибок в Azure SQL Database и Azure SQL Managed Instance
• Устранение временных ошибок подключения в Azure SQL Database и SQL Managed Instance
• Устранение неполадок задержки повторной георепликации
• Устранение ошибок из-за нехватки памяти
• Video: рекомендации по загрузке данных на Azure SQL Database