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

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

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

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

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

Ответ 1

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

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

Обновление

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

Ответ 2

Вам не хватает аргумента в dbcc shrinkfile:

dbcc shrinkfile (MyDatabase_log, 20000, TRUNCATEONLY)

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

Ответ 3

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

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

Ответ 4

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

Ответ 5

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

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

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

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

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

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