Огромный журнал транзакций с базой данных SQL Server в режиме простого восстановления

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

по какой-то причине журнал транзакций для этой базы данных является массивным (410 ГБ) с 99% свободного места.

Я попытался сжать файл с помощью (DBCC SHRINKFILE (MyDatabase_log, 20000)), но это, кажется, не работает.

кто-нибудь есть какие-либо советы о том, почему простой режим восстановления базы данных будет иметь такой огромный файл? Мне бы очень хотелось, чтобы он уменьшился.

5 ответов:

Это означает, что у вас когда-то была одна транзакция, которая длилась так долго, что она заставила журнал расти 410GB. Журнал не может быть повторно использован при наличии активной транзакции, так как данные отката не могут быть удалены. Таким примером может быть, если кто-то откроет запрос SSMS, запустит транзакцию, обновит запись, а затем отправится в отпуск. Транзакция будет активной и заставит журнал расти до тех пор, пока в конечном итоге не будет зафиксирован или откат. Когда транзакция в конечном итоге заканчивается пространство можно, наконец, освободить, оставив огромный пустой файл журнала.

другой сценарий, если у вас было около 200 ГБ данных, обновленных в одной транзакции. Журнал будет хранить до и после изображений изменений, таким образом, потребляя в два раза больше места, и он не может быть использован повторно, снова, потому что это все одна транзакция.

обновление

Я забыл упомянуть о репликации, что также является фактором, который может предотвратить усечение. И так зеркальное отражение, распределенная транзакция (технически это то же самое, что и "активная транзакция", но импликация DTC делает ее отдельным случаем). Полный список и пояснения по адресу Факторы, Которые Могут Задержать Усечение Журнала.

вы пропускаете аргумент в dbcc shrinkfile:

dbcc shrinkfile (MyDatabase_log, 20000, TRUNCATEONLY)

NOTRUNCATE по умолчанию перемещает выделенные блоки в начало нераспределенного пространства. TRUNCATEONLY удаляет нераспределенное пространство. Так что если вы делаете NOTRUNCATE затем TRUNCATEONLY, вы получаете один тонкий журнал.

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

Это не будет работать, если у вас есть несколько файлов данных.

Издателя Репликации? Может ли это быть причиной огромного журнала транзакций?

как сказано в других ответах, Активные Операции и Повторностях типичные причины этой проблемы.
Другой, менее заметный,Изменить Сбор Данных (CDC).

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

  • отключить CDC:EXEC sys.sp_cdc_disable_db
  • создать публикацию для произвольной таблицы в базе данных вопрос
  • удалить эту / Все публикации. EXEC sp_removedbreplication 'my_db' это удобный способ сделать это.
  • сжать журнал по желанию

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

еще одна полезная диагностическая идиома-проверить log_reuse_wait_desc в sys.базы данных для базы данных нарушителей. Это поле читается REPLICATION пока я не закончил процедуру.

SELECT log_reuse_wait_desc, * FROM sys.databases where name = 'my_db'