SQL журнал транзакций переполнен

При обновлении бухгалтерии, на этапе сохранения, получил следующую ошибку:

по причине: Каталог не обнаружен ‘ConfigSave\e0666db2-45d6-49b4-a200-061c6ba7d569.6b9d6525-ee94-4e13-b73d-82d3e8e8441d’

по причине: Ошибка СУБД: Microsoft SQL Server Native Client 11.0: Журнал транзакций для базы данных «acc_main» переполнен. Причина: «LOG_BACKUP». HRESULT=80040E14, SQLSrvr: SQLSTATE=42000, state=2, Severity=11, native=9002, line=1

Идем на сервер и первым делом проверяем место на дисках,

А оно закончилось ) нужно потом почистить хард или увеличивать объем, а пока порежем лог

Открываем SQL Server Management Studio

Это ошибка Microsoft SQL Server — переполняется лог транзакций и не очищается. Урезать его возможно различными способами, в том числе и с помощью стандартной оснастки, но не всегда данная операция получается, и размер файла лога остается прежним. Как вариант предлагаю следующее решение из двух строчек( где acc_main — название базы Бух)

Код SQL

 USE acc_main
ALTER DATABASE acc_main SET RECOVERY SIMPLE
DBCC SHRINKFILE (acc_main, 50);
ALTER DATABASE acc_main SET RECOVERY FULL

Результат выполнения:

Тоже самое можно сделать вручную:

Шаг 1. Установить модель восстановления Простая (Simple). Правой кнопкой на базе — Свойства(Properties) — Параметры(Options) — 4-й сверху пункт Модель восстановления(Recovery model) — Простая(Simple) — OK.

Шаг 2. Выполнить шринк (сжатие) лога транзакций. Правой кнопкой на базе — Задачи(Tasks) — Сжать(Shrink) — Файлы(Files) — установить Тип файла(File type) — Журнал(Log) — в Операция сжатия(Shrink action) — выбрать Реорганизовать страницы, перед тем осводить неиспользуемое место(Reorganize pages before releseasing unused space) — Сжать файл (Shrink file to) — указать приемлемый размер лога.

Шаг 3. Установить модель восстановления Полная(Full). Правой кнопкой на базе — Свойства(Properties) — Параметры(Options) — 4-й сверху пункт Модель восстановления(Recovery model) — Полная(Full) — OK.

В дополнении скажу, что можно сохранить лог в файл и выполнить шринк так(BaseDB — имя базы данных):

Код SQL

Журнал транзакций во всех версиях Microsoft SQL Server имеет тенденцию расти со временем. Иногда он может заполнить все свободное место на сервере. Чтобы избежать этого, в SQL Server есть операция усечения журнала транзакций.

Журналы транзакций SQL Server и модель восстановления базы данных

Журналы транзакций используются для записи всех транзакций перед передачей данных в файл базы данных. Файлы журнала транзакций необходимы для отката базы данных до предыдущего состояния. Как правило, в журнале хранится порядковый номер, тип изменения, выполненные операции и т. д. Скорее всего, вы можете использовать SQL Server и без журнала транзакций, но тогда в в случае сбоя возможна потеря базы данных.

Журнал транзакций состоит из небольших логических элементов, называемых VLF (Virtual Log File). Вы можете узнать их количество, выполнив следующий запрос в контексте базы данных SQL Server:

DBCC LOGINFO

Количество возвращаемых строк указывает на то, сколько виртуальных файлов сегментировано в журнале. В поле «Status» отображается текущее состояние сегмента. Значение 0 означает, что сегмент в настоящее время не занят и может использоваться. 2 означает, что сегмент используется. Если свободных сегментов нет и в настройках базы данных SQL Server разрешено увеличение журнала транзакций, он будет увеличен и будут созданы новые VLF. Если размер журнала транзакций фиксирован или на диске недостаточно места, все операции по изменению структуры базы данных или ее содержимого станут недоступными. Скорее всего, вы получите ошибку: Журнал транзакций для базы данных переполнен.

Файлы журнала усекаются автоматически, в зависимости от модели восстановления, используемой в настройках SQL Server:
  • Простая модель восстановления – файлы журнала автоматически обрезаются после достижения контрольной точки (самый простой вариант, требующий администрирования базы данных). При использовании модели простого восстановления журнал транзакций очищается сразу после завершения транзакции. В этом режиме вы можете откатить вашу базу данных только до времени полного резервного копирования базы данных.
  • Модель полного восстановления – журнал транзакций не будет очищен, пока не будет завершено резервное копирование журнала транзакций. Этот режим обеспечивает наилучшую возможность восстановления данных после сбоя. В полном режиме журнал транзакций (LDF) может увеличиваться (поскольку изменения базы данных накапливаются в этом журнале). В модели полного восстановления все транзакции SQL записываются в файлы журнала на диске и сохраняются там до создания резервной копии. Хранение журналов позволяет вам при необходимости вернуться к более ранней копии базы данных, и вы можете выполнить восстановление для каждой транзакции.
  • С неполным протоколированием – этот режим позволяет сократить занимаемое пространство используя минимальные настройки ведения журнала. Если журнал был поврежден или с момента создания последней резервной копии журналов выполнялись операции с неполным протоколированием, все изменения после этого резервного копирования необходимо внести повторно. Иначе результаты работы потеряны не будут.

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

Как обрезать журналы транзакций на MS SQL Server?

Мы получили вот такое сообщение:

Microsoft OLE DB Provider for SQL Server: Журнал транзакций для базы данных «buh” заполнен. Чтобы обнаружить причину, по которой место в журнале не может быть повторно использовано, обратитесь к столбцу log_reuse_wait_desc таблицы
sys. databases HRESULT=80040E14, SQLStvr: Error state=2, Severity=11,native=9002, line=1

Это означает, что на диске, где хранится журнал транзакций SQL, недостаточно места, и SQL не может записать новые данные транзакции. В этом случае вы можете обрезать файлы журналов SQL вручную (с помощью графического интерфейса Management Studio).

Чтобы обрезать журналы транзакций SQL, запустите SQL Server Management Studio (SSMS), выберите нужную базу данных, щелкните ее правой кнопкой мыши и выберите «Свойства» в контекстном меню. Перейдите в Параметры и переключите модель восстановления базы .

Меняем модель восстановления с Полной на Простую

Затем в главном меню перейдите в раздел «Задачи» -> «Сжать» -> «Файлы» .

В поле Тип файла выберите Журнал, в поле Имя файла укажите имя файла журнала. В поле «Операции сжатия» выберите » Реорганизовать страницы, перед тем как освободить неиспользуемое пространство» , установите нужный размер файла и нажмите «OK».

Нажимаем «OK». После завершения операции обязательно измените режим восстановления базы данных обратно на Полный.

Это только один из способов быстрого уменьшения размера логов. Не самый лучший, но очень простой и эффективный.

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

Как усечь журнал транзакций MSSQL Server?

В MS SQL очистка журнала транзакций необходима в том случае, если настроена полная модель восстановления базы данных. Если журнал транзакций переполнился, то ваша база данных откажется работать и будет выдавать ошибку: «журнал транзакций для базы данных заполнен». Почему такое происходит и как этого избежать? Рассмотрим два решения, которые помогут быстро устранить ошибку и продолжить работу с базой.

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

Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт .

img 2018 02 22 14 45 12

Для типа файла «Журнал» увеличиваем максимальный размера файла для авторасширения.

Сжимаем файл журнала транзакций.

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

Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт . Модель восстановления выбираем «Простая» и нажимаем ОК.

img 2018 03 07 17 15 27

Далее правой клавишей мышки по базе и выбираем из контекстного меню — —

img 2018 03 07 17 18 26

Тип сжатия: Журнал
Операция сжатия: Реорганизовать файлы, перед тем как освободить неиспользуемое место
И указываем размер до которого необходимо сжать, например 0.

img 2018 03 07 17 28 26

Теперь нужно вернуться в свойства базы к пункту и переключить модель восстановления на «Полная».

http://www.sql.ru/articles/mssql/01072302TransactionLogStillFull.shtml
Борьба с размером журнала транзакций: причины роста и методы уменьшения

По материалам рассылки comp.soft.winsoft.sqlhelpyouself на subscribe.ru

Часть 1) Сокращение размера Transaction Log в SQL Server 2000 с помощью DBCC (По материалам Q272318)

Команда DBCC SHRINKFILE для урезания log в SQL Server 2000 больше не является отложенной операцией. DBCC SHRINKFILE пытается сокращать файл немедленно. Однако, в некоторых случаях, необходимы дополнительные действия для того, что бы журнал был сокращён до ужного размера. При выполнении DBCC SHRINKFILE, SQL Server 2000 удаляет виртуальные журналы, чтобы в целом получить заданный размер журнала. Если заданный размер не достигнут, SQL Server размещает фиктивные входы журнала в последний виртуальный журнал, пока виртуальный журнал не будет заполнен, а потом перемещает заголовок журнала в начало файла. В таком случае, чтобы завершить сокращение размера журнала:

1. Выполнить инструкцию BACKUP LOG, чтобы удалить неактивную часть журнала.
2. Выполнить DBCC SHRINKFILE снова, задав желательный размер.

Например:

DBCC SHRINKFILE(pubs_log, 2)
(Если нужный размер не достигнут)
BACKUP LOG pubs WITH TRUNCATE_ONLY
DBCC SHRINKFILE(pubs_log,2)

Часть 3) Метод быстрого усечения журнала транзакций и перевода БД в offline По материалам статьи Krishnan М. Kaniappan на swynk.com «Quickly Shrinking the Transaction Log»

В этой статье Кришнан обсуждает два следующих аспекта:
— Усечение журнала транзакций.
— Метод перевода базы данных в автономный режим (offline).

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

Кришнан предлагает следующую уловку для быстрого усечения журнала транзакций, который у Вас может стать слишком большим. Вы можете сократить журнал транзакций, используя системные хранимые процедуры sp_detach_db и sp_attach_db. Для получения дополнительной информации об отсоединении и присоединении баз данных обратитесь к BOL. Когда Вы отсоединяете базу данных, используя sp_detach_db, SQL сервер будет знать, что работа с базой данных была корректно завершена, и для последующего прикрепления базы данных, журнал транзакций не нужен и может быть не доступен. Его можно удалить. Когда Вы прикрепляете базу данных, SQL сервер создаёт новый журнал, который будут иметь минимальный размер. Для этого необходимо выполнить следующие шаги:

— Отсоедините базу данных, используя процедуру sp_detach_db (предварительно убедитесь, что никакие процессы не используют файлы базы данных);
— Удалите журнал;
— Прикрепите базу данных заново, используя процедур sp_attach_db у.

Так как Вы отсоединяете и прикрепляете базу данных к тому же самому серверу, Вы не будете иметь проблем с нарушением логинов. Для получения дополнительной информации по усечению файлов журналов транзакций, состоящих из одного или более журналов, Вы можете обратиться к статьям Базы Знаний Микрософт: Q256650 (для SQL 7.0) и Q272318 (для SQL 2000).

Часть 3) Как усекается журнал транзакций SQL Server 7.0 По материалам статьи из Microsoft Knowledge Base

Есть несколько причин, из-за которых журнал транзакций не усекается при использовании DBCC SHRINKFILE или DBCC SHRINKDATABASE. В Books Online DBCC SHRINKFILE и DBCC SHRINKDATABASE описаны достаточно хорошо, но слишком кратко. В Микрософте SQL Server 7.0, команды SHRINKFILE и SHRINKDATABASE устанавливают желаемый размер, до которого необходимо усекать журнал. Эти команды могут быть применены для каждого журнала, но это, фактически, только заявка, которую сервер попытается выполнить. Поэтому, после выполнения команды SHRINKFILE или SHRINKDATABASE, Вам придётся дополнительно выполнить команду, которая усекает журнал и у Вас есть шанс, что это произойдёт. Вы не можете уменьшить журнал транзакций до размера меньше установленного критериями, которые представлены ниже:

— Чтобы усечь журнал транзакций до размера меньшее первоначального, Вы должны уменьшить соответствующие файлы с помощью DBCC SHRINKFILE. Вы не можете использовать DBCC SHRINKDATABASE, чтобы усечь журнал транзакций до размера меньшего его оригинала или явно заданного размера. Первоначальный размер определяется, как размер файла журнала в команде CREATE DATABASE плюс любые использованные после этого команды ALTER DATABASE. Первоначальный размер определяется без учёта автоматического роста файла журнала.

— Физический размер журнала никогда не может быть меньше используемой сервером в момент усечения его части. Вы можете использовать команду DBCC SQLPERF (LOGSPACE), чтобы увидеть количество используемого в журнале места.

— Минимально возможный размер журнала транзакций для любой базы данных сервера, это текущий размер журнала базы данных model. По умолчанию, журнал транзакций базы данных model меньше одного мегабайта.

— Поскольку журнал транзакций может быть сокращен только до границы виртуального журнала virtual log file (VLF), не возможно сократить журнал к размеру меньше чем VLF, даже если это место реально не используется. Аналогично, если часть VLF используется, Вы не можете сократить занимаемое этим VLF место. Для получения дополнительной информации, см. разделы «Virtual Log Files» и «Transaction Log Physical Architecture» в Books Online.

Журнал транзакций, это wrap-around (с запашком) журнал. Это означает, что в любое время могут существовать свободные (free) или многократно используемые (reusable) VLF, которые могут находиться в начале, в середине, и/или в конце журнала транзакций. Сократить можно только свободное место в конце журнала транзакций, а не всё свободное пространство журнала. Также, Вы можете сократить только целые VLF. Сокращаемые в конце журнала VLF должны быть неактивны. Для более детальной информации обратитесь к разделу Truncating the Transaction Log в Books Online.

Обратите внимание на следующее:

— Всегда резервируйте системные и пользовательские базы данных до и после того, как Вы вносите изменения, затрагивающие систему. DBCC SHRINKFILE и DBCC SHRINKDATABASE — не регистрируемые операции, и исполнение их нарушает последовательность отписывания транзакций из журнала в резервную копию. После выполнения этих команд, Вы должны будете сделать полное резервное копирование базы данных.

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

— Удостоверьтесь, что отсутствуют старые, долго выполняющиеся или не копируемые транзакции. Чтобы сделать это, используйте:

DBCC OPENTRAN (database_name)

— Выполните DBCC SHRINKFILE или DBCC SHRINKDATABASE, чтобы определить возможную границу усечения (shrinkpoint). DBCC SHRINKFILE и DBCC SHRINKDATABASE, по умолчанию, разрешены членам серверной роли sysadmin или роли базы данных db_owner. Для информации о различиях между этими командами, обратитесь к Books Online (обратите внимание на отличие в параметрах):

DBCC SHRINKFILE (file_name, target_size)
DBCC SHRINKDATABASE (database_name, target_percent)

— Создайте несколько фиктивных транзакции, чтобы имитировать журналирование транзакций и затем, выполните команду BACKUP, чтобы осуществить усечение журнала транзакций. Инструкция BACKUP фактически попытается усечь журнал транзакций к заданному целевому размеру (target_size или target_percent).

Ниже следует пример того, как сгенерировать фиктивные транзакции для одного журнала с последующим усечением:

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes int,
@NewSize int
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
-- This is the name of the database for which the log will be shrunk.
USE Your_Database_Name
SELECT @LogicalFileName = 'Your_log' -- Use sp_helpfile to identify the logical file name
that you want TO shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE NAME = @LogicalFileName
SELECT 'Original Size of ' + DB_NAME() + ' LOG is ' +
CONVERT(varchar(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(varchar(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE NAME = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) NOT NULL)
-- Wrap log and truncate it.
DECLARE @Counter int,
@StartTime datetime,
@TruncLog varchar(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + DB_NAME() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not
expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE NAME =
@LogicalFileName) -- the log has not shrunk
-- The value passed in for new size is smaller than the current size.
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
-- Because it is a char field it inserts 8000 bytes.
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + DB_NAME() + ' LOG is ' +
CONVERT(varchar(30),size) + ' 8K pages or ' +
CONVERT(varchar(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE NAME = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF

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

После того, когда журнал транзакций усечён:

1. Выполните полное резервное копирование базы данных master.
2. Выполните полное резервное копирование пользовательской базы данных. Это необходимо, потому что команда SHRINK не регистрируется в журнале и нарушается порядок отписывания транзакций.

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

Дополнительная информация в Microsoft Knowledge Base:

Разделы Books Online: Transaction Log Physical Architecture; Optimizing Transaction Log Performance.

Часть 4) Причины заполнения журнала транзакций SQL серверов 4.2x, 6.0, 6.5, 7.0 По материалам статьи Микрософт: Q110139 — INF Causes of SQL Transaction Log Filling Up

Журнал транзакций может полностью заполнится, что сделает невозможным операции UPDATE, DELETE или INSERT, включая CHECKPOINT. Обычно это проявляется, как ошибка 1105, имеющая следующее содержание:

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

Фундаментальной характеристикой реляционных баз данных, таких как Microsoft SQL Server, является поддержка целостности. Любая транзакция должна быть абсолютно неделима и все вносимые ей изменения должны быть применены полностью или не применены вообще, даже в случае отказа системы. В определяемой пользователем транзакции, транзакционным блоком считаются все инструкции между BEGIN TRANSACTION и COMMIT TRANSACTION. В неявной транзакции, каждая отдельная инструкция SQL рассматривается неделимым модулем. Это дает возможность серверу баз данных выдерживать сбои питания, аварийные отказы операционной системы, и т.д., когда, после перезапуска, происходит автоматическое (без участия обслуживающего персонала) восстановление баз данных к непротиворечивому состоянию. Эту возможность обеспечивает механизм журнала транзакций. Так как целостность данных является фундаментальным свойством SQL сервера, регистрация транзакций не может быть заблокирована. Некоторые утилиты или операции, типа BCP или SELECT INTO, имеют минимальную регистрацию в журнале, но даже этого достаточно, чтобы обратный откат таких операций был возможен. Требования к количеству дискового пространства для поддержки регистрации транзакций могут быть весьма высокими. Например, в большинстве случаев, модификация каждой строки данных должна быть зарегистрирована, а также регистрируются и все модификации затронутых индексов. Так как запись в журнале может содержать фиксированную часть данных изменяемой строки, количество занимаемого журналом места зависит от ширины строки. Для коротких строк, количество занимаемого в журнале места, при операциях UPDATE, DELETE или INSERT, может в десятки раз увеличивать занимаемое базой место на диске. При использовании достаточно широких строк, размер базы данных будет пропорционально больше величины журнала транзакций. Поэтому, старайтесь тщательно отслеживать потребление журналом транзакций дискового пространства, что поможет Вам гарантировать целостность данных. Обеспечение нормальной работы механизма регистрации транзакций является одной из первейших обязанностей DBA.

Количество занимаемого журналом места может зависеть от многих факторов и его очень трудно предсказать заранее. Стандартное руководство администратора предлагает, в качестве отправной точки, использовать величины порядка 15 — 30 процентов от размера базы данных. В действительности этого может оказаться недостаточно или наоборот, такие значения будут не оправдано избыточны. Как правило, более точные результаты даёт эмпирическое испытание, дающие грубую оценку размера журнала для реальных данных и прикладных программ. Попытки вычислить размер журнала транзакций, без подобного моделирования реальных условий, достаточно трудны и дают очень не точеные результаты.

Несколько факторов могут влиять на размер журнала транзакций. Первый из таких факторов — оптимизатор запросов (query optimizer). Для одного и того же SQL запроса, модифицирующего данные, через какое-то время план выполнения может измениться в зависимости от статистики распределения данных. Разные планы выполнения могут по разному использовать место в журнале. Следующий фактор — неизбежная внутренняя фрагментация базы данных, которая может привести к разному количеству имеющихся разбитых страницы. Не существует стандартных методов отслеживания подобных процессов, поскольку SQL сервер управляет пользовательскими данными автоматически. Самым простым методом такой оценки является выполнение команды DBCC CHECKTABLE (syslogs), которая возвращает число 2048-байтовых страниц данных в журнале, до и после выполнения тестовых или реальных, модифицирующих данные запросов. Это может дать приблизительную оценку требующегося места в журнале для таких запросов. Обычно, лучше допустить ошибку в сторону избыточности размера журнала, при определении его размера или дискового пространства для него.

У SQL Server 7.0, журнал транзакций имеет возможность расшириться автоматически. Дискретность прироста может задаваться пользователем или может быть разрешено использование всего доступного дискового пространства. Журнал состоит из виртуальных журналов (Virtual Log files, далее VLF). Количество и размер этих виртуальных журналов определяет SQL сервер и это не может быть изменено конфигурационными параметрами. После создания новой базы данных, каждый её физический журнал имеет не менее двух VLF. Иногда администраторы базы данных включают опцию базы данных «truncate log on checkpoint», чтобы избежать переполнения журнала. Назначение этой опции состоит в том, чтобы обеспечить автоматическое усечения журнала транзакций, главным образом для разрабатываемых или тестовых баз данных, которым не обязательна регистрация транзакций для последующего их резервирования. Эта опция не отключает регистрацию транзакций или поддержку целостности, а только заставляет обработчик контрольной точки делать попытку усечения журнала приблизительно каждые 60 секунд. Обратите внимание, что журнал транзакций не будет усекаться при ручной установке команды «truncate log on checkpoint», которая автоматически запускает контрольную точку в базе данных. Эта опция всегда включена для базы данных tempdb, хотя Вы не увидите этого с помощью хранимой процедуры sp_help.Но даже при включённой опцией «truncate log on checkpoint», не исключены случаи, когда журнал транзакций может переполниться. Ниже эти случаи рассмотрены подробно:

1. При исполнении большой, неделимой транзакции, особенно при массовых операциях UPDATE, DELETE или INSERT: Каждый отдельная SQL инструкция будет рассматриваться как самостоятельный, неделимый модуль, который должен применяться или не применяться целиком. По этой причине, все изменения строк должны быть зарегистрированы, и транзакция не может быть усечена на всём протяжении её исполнения. Например, если происходит массовая операция с INSERT, которая продолжается пять минут, записи журнала транзакций, относящиеся к этой транзакции, не могут быть усечены в течении этого времени. Администратор базы данных должен обеспечить достаточно места в журнале для самой большой, массовой операции или должен разбить массовую операцию на меньшие блоки.

2. При наличии в журнале незавершённых транзакций: журнал транзакций может быть усечен только до самой старой незавершённой транзакции. Есть несколько возможных причин появления незавершённых транзакций, большинство из которых относятся к ошибкам прикладного программного обеспечения. Они включают:

— Массовые транзакции. Как показано выше, для больших и продолжительных, массовых транзакций соответствующие регистрационные записи в журнале не могут быть усечены в течение всего периода исполнения. Однако, такая транзакция будет препятствовать усечению и других более коротких транзакций, которые исполняются в то же самое время.

— Не продуманная разработка прикладного программного обеспечения, когда в пределах определяемой пользователем транзакции возможен ввод или корректирование данных пользователем или другие, продолжительные действия пользователя. Например, после BEGIN TRANSACTION, приложение может запросить у пользователя ввод данных, который может занять долгое время, в зависимости от поведения пользователя. Пока пользователь соответствующим образом не отреагирует, приложение не сможет исполнить COMMIT, а усечение журнала не будет возможным.

— Ошибки прикладного программного обеспечения, вследствие которых транзакция остаётся не завершённой: обычная причина этого — неправильная обработка вызова dbcancel() DB-Library в пределах определяемой пользователем транзакции. Это наблюдается, когда запрос отменен с помощью dbcancel(), причём, исполняющаяся в это время SQL инструкция прерывается и откатывается назад, а внешняя транзакция не отменяется. Приложение должно гарантировать выполнение ROLLBACK TRANSACTION или инструкции COMMIT TRANSACTION, чтобы полностью отработать и закрыть транзакцию. Не выполнение этого требования, часто приводит к появлению ошибки 3902: The commit transaction has no corresponding BEGIN TRANSACTION. Это можно использовать в приложении, чтобы в ответ выполнить SELECT @@TRANCOUNT, и определить, какая вложенная транзакция существует на этом уровне. Однако, приложение не должно делать это вслепую, что бы всё-таки выполнить COMMIT/ROLLBACK и получить @@TRANCOUNT=0. Это не желательно потому, что @@TRANCOUNT может указывать не на ту транзакцию, которую ожидалось определить. Т.е. приложение может не увидеть запись о транзакции вложенного уровня, относительно той транзакции, которая стала причиной ошибки приложения. И тогда, инициация COMMIT/ROLLBACK может привести к завершению или откату не завершённой транзакций, так как приложение не может знать, какие транзакции завершены на вложенном уровне. Для разрешения таких проблем, программист должен так отладить приложение и возможные хранимые процедуры, что бы полностью исключить возможностьљ непреднамеренного прерывания вложенных транзакций.

— Сетевые ошибки, из-за которых SQL сервер не получает уведомление о потере сетевого подключения. Если клиентская рабочая станция зависает, перезагружается, или будет выключена во время исполнения определяемой пользователем транзакции, средства сетевого уровня должны сообщить об этом SQL серверу. Если сеть не отреагирует на это должным образом, SQL сервер будет считать, что клиент продолжает работу, и выполняющаяся клиентская транзакция будет по-прежнему активна. Эта сетевая проблема должна контролироваться администратором, который может с помощью sp_who, sp_lock, или сетевых утилит определить такие оборванные сетевые соединения и уничтожать их вручную.

— Транзакции, не завершенные из-за блокировок. В многопользовательской среде исполняющаяся транзакция может обратиться к блокированным другим процессом данным. В этом случае, транзакция останется открытой и не позволит выполнить усечение журнала. Чтобы это обнаружить, программист или администратор базы данных должен использовать sp_who, sp_lock, или другие инструментальные средства, для соответствующего анализа. В большинстве случаев, проблемы блокировок могут быть снижены или устранены через изменение запроса, индекса, и изменение дизайна базы данных.

— Неудачные попытки отмены запросов на модификацию данных. Если приложение исполняет dbcancel(), но запрос не отменен из-за проблем в сети или SQL сервера, запрос продолжает выполняться, и транзакция останется открытой. Если Вы подозреваете наличие такой проблемы, используете sp_who, чтобы увидеть, отменён ли запрос. При попытке такой отмены для клиента, работающего через TCP/IP сокет, пробуйте проверить подключение клиента по named pipes, или запустите клиентское приложение на сервере, используя local pipes. Это поможет определить, вызвана ли проблема сетью или SQL сервером.

3. Чрезмерная утилизация ресурсов сервера при усечении журнала во время отработки Checkpoint: Хотя журнал транзакций усекается каждые 60 секунд, количество усекаемых за это время транзакций ограничено. Вероятность того, что контрольная точка не успеет пробежать за отведённое ей время весь журнал, чрезвычайно мала, и Вы должны предварительно рассмотреть все другие возможные причины возникновения проблемы. Однако, превысить максимальную норму усечения всё-таки возможно, если большое количествољ клиентов будет одновременно выполнять модификацию большого количества данных. Подобные проблемы решаются путём пересмотра структуры приложения, с целью уменьшения числа модифицируемых клиентом строк, что всегда должно быть одной из первостепенных задач дизайна для любой базы данных. Если это не выполнимо, система может быть масштабирована в целях увеличения пропускной способности дисковой подсистемы (I/O), например striping, дополнительные диски или дисковые контроллеры, и т.д. Обычно, это видно потому, что процесс отработки контрольной точки потребляет больше времени, чем положено при исполнении команды DUMP TRANSACTION, поскольку он пытается не отставать от усечения журнала. Как только порог усечения превышен (см. ниже) Вы увидите, что обработчик контрольной точки перестал делать попытки усечения журнала базы данных, пока не произойдёт очистка журнал транзакций.

4. Превышение порога усечения. Обработчик контрольной точки по существу делает DUMP TRANSACTION WITH TRUNCATE_ONLY. Так же, как это делается вручную. Но контрольная точка не всегда будет успевать появится до того, как журнал будет заполнен до некоторого критического значения. Например, чрезмерный объём модификаций данных, выполненный за очень короткое время, может заполнить журнал транзакций на 95% между двумя очередными контрольными точками. Когда обработчик контрольной точки сделает попытку усечения почти заполненного журнала транзакций, это может привести к невозможности самого усечения. Такое случается потому, что усечение журнала само является регистрируемой операцией. Единственное решение в этом случае состоит в том, чтобы использовать DUMP TRANSACTION WITH NO_LOG, т.е. вручную запустить усечение журнала транзакций. Не рекомендуется использование опции NO_LOG кроме случаев, когда это абсолютно необходимо, поскольку операция не будет регистрироваться в журнале и произошедший во время выполнения такой операцииљ отказ системы, может породить ошибки в базе данных.

5. Комбинации представленных выше четырёх причин. Например, при нормальных условиях в интенсивно модифицируемой среде, норма усечения обработчика контрольной точки может препятствовать заполнению журнала. Если временно открытая транзакция, вызванная любой из вышеупомянутых причин (например, блокировки) приведёт к заполнению журнала, к примеру, до 50 %, останется гораздо меньшее возможностей для обработки других возможных модификаций данных, что делает более вероятным достижение порога усечения, после которого автоматическое усечение журнала будет невозможно. Транзакции в tempdb регистрируются так же, как и в любой другой базе данных. Опция TRUNCATE LOG ON CHECKPOINT, в большинстве случаев,љ остаётся включённой для tempdb. Из-за этого, журнал транзакций постоянно усекается и не переполняется. Однако, любая из вышеупомянутых причин может заставить журнал базы tempdb переполниться. Конфигурация Tempdb обычно подразумевает размещение базы и журнала в одном файле (sysusages.segmap=7), вследствие чего данные и регистрационные операции будут конкурировать за одно и то же доступное дисковое пространство. Некоторые конструкции Transact-SQL, такие, как GROUP BY, ORDER BY DESC и т.д., будут автоматически требовать место в tempdb для своей работы. Это порождает неявные записи BEGIN TRANSACTION в журнале tempdb, отвлекая на это дополнительное место. Такаяљтранзакция по базе tempdb будет продолжаться до завершения породившей её транзакции в пользовательской базе, что может задержать усечение журнала tempdb в течение этого времени. Если транзакция в пользовательской базе приостановлена по какой-либо причине, (включая блокировки или приложение, не обрабатывающее dbnextrow() для завершения), транзакция в tempdb, как и в предыдущем случае, останется открытой, мешая усечению журнала tempdb. Для решения такой проблемы, программист должен соответствующим образом отладить приложение и/или разрешать проблемы параллельного исполнения транзакций, которые порождают эту ситуацию.

Усечение журнала транзакций SQL Server 7.0 происходит путём усечения виртуальных журналов Virtual Log Files (VLF) из которых, как из кирпичиков, состоит журнал транзакций. Если в журнале существует активная транзакция, расположенная резидентно в одном из VLF, этот виртуальный журнал не может быть усечен. Если активные транзакции есть во всех виртуальных журналах, журнал транзакций не может быть усечен. Если включена опция автоматического роста журнала и есть достаточно места на диске, где находится журнал, и максимальный размер файла журнала ещё не достигнут, журнал транзакций будет увеличен на величину, указанную в свойствах журнала.

Нижеследующие замечания рассматривают тот случай, когда происходит усечение журнала при исполнении SQL запроса, в зависимости от того, включена ли опция TRUNCATE LOG ON CHECKPOINT:

— Если опция TRUNCATE LOG ON CHECKPOINT включена, и будет установлено во время запуска сервера, что журнал транзакций переполнен — его содержимое будет автоматически уничтожено

Добавить комментарий