Работа с устранением ошибок журнала транзакций в управляемом экземпляре SQL Azure

Область применения: Управляемый экземпляр SQL Azure

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

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

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

Расположение и имя файлов базы данных нельзя управлять, но администраторы могут управлять файлами базы данных и параметрами автоматического увеличения файлов. Типичные причины и способы устранения проблем с журналом транзакций примерно те же, что и в SQL Server.

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

Для Azure SQL Managed Instance вы можете приобрести дополнительное хранилище независимо от вычислительных ресурсов до определённого предела. Дополнительные сведения см. в разделе "Управление файлами", чтобы освободить больше места.

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

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

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Следующие значения 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. Если данные, возвращаемые полем 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
, 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 Managed Instance, освобождение места может быть частью решения. Однако большое значение имеет устранение первопричины блокировки усечения файла журнала транзакций. В некоторых случаях временное создание дополнительного места на диске позволяет выполнять длительные транзакции, удалив условие, блокирующее усечение файла журнала транзакций с помощью обычной резервной копии журнала транзакций. Однако освобождение места может обеспечить только временное облегчение до тех пор, пока журнал транзакций снова не будет расти.

Для Azure SQL Managed Instance вы можете приобрести дополнительное хранилище независимо от вычислительных ресурсов до определённого предела. Например, в портал Azure перейдите на страницу вычислений и хранилища, чтобы увеличить объем хранилища в ГБ. Сведения об ограничениях размера журнала транзакций см. в разделе об ограничениях ресурсов для Управляемый экземпляр SQL. Дополнительные сведения см. в статье «Управление пространством файлов для баз данных в управляемом экземпляре Azure SQL».

Хранилище резервных копий не вычитается из места хранения управляемого экземпляра SQL. Хранилище резервных копий не зависит от места хранения экземпляра и не ограничивается размером.

Ошибка 9002: журнал транзакций для базы данных заполнен

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.

Ошибка 9002 возникает в SQL Server и в Управляемый экземпляр SQL Azure по тем же причинам.

Предпринимаемые действия при переполнении журнала транзакций зависят от условий, которые вызвали эту ситуацию.

Чтобы устранить ошибку 9002, попробуйте следующие методы:

• Журнал транзакций не сокращается и увеличился, чтобы заполнить все доступное пространство.
  • Так как резервные копии журналов транзакций в Azure SQL Управляемый экземпляр автоматические, что-то ещё мешает усечению активности журнала транзакций. Неполная репликация, CDC или синхронизация группы доступности может препятствовать усечению, см. статью Запрещенное усечение журнала транзакций.
• Размер зарезервированного хранилища управляемого экземпляра SQL заполнен, и журнал транзакций не может увеличиваться.
  • Добавьте пространство до предела ресурсов, см. раздел "Управление файлами", чтобы освободить больше места.
• Размер журнала транзакций имеет фиксированное максимальное значение или автоматический рост отключен и поэтому не может увеличиваться.
  • См. свойства MAXSIZE и FILEGROWTH в ALTER DATABASE File and Filegroups.

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

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

Хотя ошибка 9002 встречается чаще, чем ошибка 40552 в управляемом экземпляре SQL Azure, могут возникать обе ошибки.

Чтобы устранить ошибку 40552, попробуйте выполнить следующие методы:

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

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

• Общие сведения о блокирующих проблемах и их устранении
• Устранение неполадок подключения и других ошибок Базы данных SQL Azure и Управляемого экземпляра SQL Azure
• Устранение временных ошибок подключения в SQL Database Azure и SQL Managed Instance