Что делать, если журнал транзакций не очищается, даже после DUMP TRAN WITH NO_LOG

sql.ru

По материалам статьи Q184499 «INF Transaction Log Still Full After DUMP TRAN WITH NO_LOG».
Содержание этой статьи относится к Microsoft SQL Server 6.0, 6.5

После получения сообщения об ошибке 1105, указывающей на то, что журнал транзакций (transaction log) полностью заполнен, Вы должны выполнить следующую команду, которая усекает transaction log:

dump transaction <db> with no_log

Где <db> - имя базы данных, указанной в сообщении об ошибке 1105.

Эта статья рассматривает шаги, которые Вы должны предпринять, если вышеупомянутая команда не очищает transaction log.
Если, после выполнения этой команды, transaction log всё еще остаётся заполненным, Вам необходимо убедится, что информация о свободном месте в журнале транзакций, полученная с помощью SQL Enterprise Manager (SEM), Windows NT Performance Monitor или sp_spaceused, действительно верна и актуальна. Неправильное отображение информации о величине заполнения журнала вызвано существованием ошибки, которая описана в статье:
Q183100: PRB: Incorrect Log Size Reported in SEM or Performance Monitor
Рассмотрим вопрос обновления информации о заполненности журнала к актуальному состоянию, в качестве маленького отступления.
Суть статьи Q183100 в том, что после усечения переполненного transaction log, информация о количестве свободного места журнала, хранящаяся в системной таблице sysindexes, может не соответствовать реальному состоянию. Вызвано это, во первых, тем, что таблица sysindexes не обновляется непрерывно, поскольку это могло привести к существенному падению эффективности. Во вторых, Обновление информации в этой таблице, как и любой другой таблицы базы данных, является регистрируемой в transaction log операцией. Когда transaction log переполняется, модификация sysindexes становится невозможной, в чём и состоит причина неточности содержащейся в ней информации.
Чтобы разрешить эту проблему, выполните следующую инструкцию после усечения файла регистрации:

DBCC CHECKTABLE (syslogs)

В ответ, Вы получите отчёт, пример которого представлен ниже:

Checking syslogs
The total number of data pages in this table is 1.
The number of data pages in Sysindexes for this table was 4. It has been corrected to 1.
The number of rows in Sysindexes for this table was 128. It has been corrected to 12.
*** NOTICE: Space used on the log segment is 0.00 Mbytes, 0.10.
*** NOTICE: Space free on the log segment is 2.05 Mbytes, 99.90.
Table has 12 data rows.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.

Используемое и свободное место в журнале, после этого, будет показано правильно, потому что DBCC CHECKTABLE проследит все фактические цепочки страницы transaction log. Обратите внимание на эту строку отчёта:

The number of rows in Sysindexes for this table was 128. It has been corrected to 12.

Это говорит о том, что DBCC CHECKTABLE также обновляет и значения таблицы sysindexes. Команда DBCC CHECKTABLE должна исправить информацию о свободном месте в базах и журналах, которая отображается в SQL Enterprise Manager или Performance Monitor. Однако, иногда Вам потребуется для получения правильного результата выполнить ещё одну инструкцию:

DBCC UPDATEUSAGE (<db_name>)

ОБРАТИТЕ ВНИМАНИЕ: DBCC UPDATEUSAGE может работать очень долго. Эта команда обновляет значения dpages в sysindexes для всех таблиц в базе данных, не только для syslogs.

После этой инструкции, отображение занимаемого места должно быть точным. Если это не так, пробуйте запустить команду CHECKPOINT, чтобы зафиксировать те изменения, которые ещё находятся в кэше. Также, можно использовать кнопку Recalculate в SQL Enterprise Manager. Обратите внимание, что эта кнопка запустит автоматическое исполнение DBCC UPDATEUSAGE, что может потребовать продолжительного исполнения. Для получения подробной информации о свободном месте в базах данных и журналах, используйте команду:

DBCC SQLPERF (logspace)

На этом наше маленькое отступление можно считать законченным, и мы вернёмся к первоначальной теме настоящей статьи.

Если после применения вышеперечисленных команд:

USE <databasename>
GO
DBCC CHECKTABLE (syslogs)
GO
DBCC UPDATEUSAGE (<db_name>)
GO
CHECKPOINT
GO
DBCC SQLPERF (logspace)
GO

Вы все-таки видите, что журнал транзакций переполнен (99.99 процентов), проверьте возможные причины, следствием которых могло стать это переполнение. Возможные причины и способы их устранения были представлены в прошлом выпуске рассылки, в статье Причины заполнения журнала транзакций SQL серверов 4.2x, 6.0, 6.5, 7.0

ОБРАТИТЕ ВНИМАНИЕ: Если dump transaction не усекает большую часть Вашего transaction log, причиной этого можете быть открытая транзакция. Определить наличие открытых/активных транзакций можно с помощью:

USE <databasename>
GO
DBCC OPENTRAN (<databasename>)
GO

В ответ, Вы получите отчёт, пример которого представлен ниже:

Transaction Information for database: pubs
No active open transactions.
Replicated Transaction Information:
Oldest Distributed RID_____: (0 , 0)
Time Stamp______________: 0001 0000000E
Oldest Non-Distributed RID_: (589 , 26)
Time Stamp______________: 0001 0000363E
DBCC execution completed. If DBCC printed error messages, see your System Administrator.

В представленном примером отчёте открытых транзакций не зафиксировано, но если таковые будут обнаружены, необходимо дождаться их завершения и повторить попытку очистки журнала транзакций. Заметьте, что отчёт указывает на отсутствие открытых транзакций, но в блоке Replicated Transaction Information есть строки дополнительной информации, которые показывает, что база данных была отмечена для репликации. Если будет выдана информация о репликации, убедитесь, что значение Oldest Distributed Transaction RID близко к Oldest Non-Distributed RID. Их отличие говорит о том, что существует репликация, выполняющийся в настоящее время для этой базы данных. Количественное различие будет основано на ряде переменных, относящихся к репликации, которые выходят за рамки этой статьи. Вам достаточно понять,   что база данных отмечена для репликации и существуют записи в transaction log, которые этой репликацией используются.

ВАЖНО: При обнаружении активной репликации, Вы сначала должны определить, почему транзакции, отмеченные для репликации, не завершены. Продолжить дальнейшую работу по очистке журнала Вы можете только после того, когда обеспечите и убедитесь, что эта база данных не участвует в репликации. Для получения дополнительной информации, см. SQL SERVER BOOKS ONLINE или статью в Microsoft Knowledge Base: Q89937: INF: Getting Started with Microsoft SQL Server Replication.

Для проверки отсутствия активной репликации, выполните следующий сценарий:

USE master
GO
sp_helpserver
GO

В ответ, Вы получите отчёт, пример которого представлен ниже:

name_______network_name_____status________id
-----------------------------------------------------------------
CYGNUS____CYGNUS_________pub,sub,dis___0

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

SELECT name, category FROM master..sysdatabases
WHERE name = '<databasename>'
GO
USE <databasename>
GO
--The query will return all objects that are published (32) or
--replicated (64)
SELECT category FROM sysobjects
WHERE type = 'U' and category & 32 = 32 or category & 64 = 64

В ответ, Вы получите отчёт, пример которого представлен ниже:

name_______category