Поделиться через

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

  • Статья

Применимо к: База данных SQL Azure

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

Примечание.

Сведения в этой статье относятся к Базе данных SQL Azure. База данных SQL Azure основан на последней стабильной версии ядра СУБД Microsoft SQL Server, поэтому большая часть содержимого аналогична, хотя параметры устранения неполадок и средства могут отличаться от SQL Server.

Дополнительные сведения об устранении неполадок журнала транзакций в Управляемый экземпляр SQL Azure см. в статье "Устранение ошибок журнала транзакций с помощью Управляемый экземпляр SQL Azure".

Дополнительные сведения об устранении неполадок журнала транзакций в SQL Server см. в разделе Устранение неполадок, связанных с переполнением журнала транзакций (SQL Server ошибка 9002).

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

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

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

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

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

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

Чтобы узнать, что препятствует усечению журнала в конкретном случае, см 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 могут указывать на причину, препятствующую усечению журнала транзакций базы данных:

log_reuse_wait_desc Diagnosis Требуется отклик
NOTHING Типичное состояние. Ничто не препятствует усечению журнала. №
CHECKPOINT Для усечения журнала требуется контрольная точка. Редко. Отклик не требуется, если это состояние не сохраняется. Если это состояние сохраняется, создайте обращение в Службу поддержки Azure.
LOG BACKUP Требуется резервное копирование журнала. Отклик не требуется, если это состояние не сохраняется. Если это состояние сохраняется, создайте обращение в Службу поддержки Azure.
ACTIVE BACKUP OR RESTORE Выполняется резервное копирование базы данных. Отклик не требуется, если это состояние не сохраняется. Если это состояние сохраняется, создайте обращение в Службу поддержки Azure.
ACTIVE TRANSACTION Выполнение транзакции блокирует усечение журнала. Файл журнала не может быть усечен из-за активных и (или) незафиксированных транзакций. Ознакомьтесь со следующим разделом.
REPLICATION В База данных SQL Azure это может произойти, если включена запись измененных данных (CDC). Запрос sys.dm_cdc_errors и устранение ошибок. Если ошибку не удается устранить, создайте обращение в Службу поддержки Azure.
РЕПЛИКА ДОСТУПНОСТИ Выполняется синхронизация с вторичной репликой. Отклик не требуется, если это состояние не сохраняется. Если это состояние сохраняется, создайте обращение в Службу поддержки Azure.

Усечению журнала препятствует активная транзакция

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

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

  • Возвращает сведения о свойствах транзакций из 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. Если данные, возвращаемые полем text со значением sys.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;

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

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

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

Ошибка 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 или класса System.Data.SqlClient.SqlBulkCopy попробуйте ограничить количество строк, копируемых на сервер при каждой транзакции, с помощью параметра -b batchsize или BatchSize. Дополнительные сведения см. в разделе bcp Utility.
  4. Если вы перестроите индекс с ALTER INDEX помощью инструкции, используйте SORT_IN_TEMPDB = ONпараметр и ONLINE = ONRESUMABLE=ON параметры. При возобновлении индексов усечение журнала чаще всего. Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).

Примечание.

Дополнительные сведения об ошибках управления ресурсами см. в статье об ошибках управления ресурсами.

Следующие шаги