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

Вот простой и очень неэлегантно & потенциально опасный способ.

1. Резервная БД
2. Отключить БД
3. Переименовать файл журнала
4. Прикрепить БД
5. Новый файл журнала будет создан заново
6. Удалить переименованный файл журнала.

Я предполагаю, что вы не делаете резервные копии журналов. (Которые обрезают журнал). Мой совет - сменить модель восстановления с полный на просто. Это предотвратит раздувание журнала.

Чтобы усечь файл журнала:

• Резервное копирование базы данных
• Отсоедините базу данных, используя Enterprise Manager или выполнив: Sp_DetachDB [DBName]
• Удалите файл журнала транзакций. (или переименуйте файл, на всякий случай)
• Повторно подключите базу данных снова, используя: Sp_AttachDB [DBName]
• Когда база данных присоединяется, создается новый файл журнала транзакций.

Чтобы сжать файл журнала:

• Журнал резервного копирования [DBName] с No_Log

• Уменьшите базу данных одним из следующих способов:

  Использование Enterprise Manager: - Щелкните правой кнопкой мыши базу данных, Все задачи, Сжать базу данных, Файлы, Выбрать файл журнала, ОК.

  Использование T-SQL: - Dbcc Shrinkfile ([Log_Logical_Name])

Вы можете найти логическое имя файла журнала, запустив процедуру sp_helpdb или просмотрев свойства базы данных в Enterprise Manager.

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

Если вы используете SQL 7 или 2000, вы можете включить «усечение журнала на контрольной точке» на вкладке параметров базы данных. Это имеет тот же эффект.

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

По моему опыту, на большинстве серверов SQL не существует резервной копии журнала транзакций. Полное резервное копирование или дифференциальное резервное копирование - обычная практика, но резервное копирование журнала транзакций действительно редко. Таким образом, файл журнала транзакций постоянно увеличивается (до тех пор, пока диск не заполнится). В этом случае модель восстановления должен быть установлен на «просто». Не забудьте также изменить системные базы данных «model» и «tempdb».

Резервное копирование базы данных tempdb не имеет смысла, поэтому модель восстановления этой базы данных всегда должна быть «простой».

ОТКАЗ ОТ ОТВЕТСТВЕННОСТИ: Пожалуйста, внимательно прочтите комментарии ниже, и я предполагаю, что вы уже прочитали принятый ответ. Как я сказал почти 5 лет назад:

if anyone has any comments to add for situations when this is NOT an adequate or optimal solution then please comment below

• Щелкните правой кнопкой мыши имя базы данных.

• Выберите Задачи → Сжать → База данных.

• Затем нажмите OK!

Обычно я открываю каталог Windows Explorer, содержащий файлы базы данных, чтобы сразу увидеть эффект.

Я был очень удивлен, что это сработало! Обычно я использовал DBCC раньше, но я просто попробовал это, и он ничего не сжал, поэтому я попробовал графический интерфейс (2005), и он отлично сработал - освободил 17 GB за 10 секунд.

В режиме полного восстановления это может не сработать, поэтому вам нужно либо сначала создать резервную копию журнала, либо перейти на Простое восстановление, а затем сжать файл. [спасибо за это @onupdatecascade]

-

PS: Я ценю то, что некоторые прокомментировали относительно опасности этого, но в моей среде у меня не было никаких проблем с этим сам, тем более что я всегда сначала делаю полную резервную копию. Поэтому, пожалуйста, примите во внимание, что такое ваша среда и как это влияет на вашу стратегию резервного копирования и безопасность заданий, прежде чем продолжить. Все, что я делал, это указывал людям на функцию, предоставляемую Microsoft!

Этот метод, который рекомендует Джон, не рекомендуется, поскольку нет гарантии, что база данных будет подключаться без файла журнала. Измените базу данных с полной на простую, установите контрольную точку и подождите несколько минут. SQL Server очистит журнал, который затем можно сжать с помощью DBCC SHRINKFILE.

Сначала проверьте модель восстановления базы данных. По умолчанию SQL Server Express Edition создает базу данных для простого восстановления. модель (если не ошибаюсь).

Журнал резервного копирования DatabaseName с Truncate_Only:

DBCC ShrinkFile(yourLogical_LogFileName, 50)

SP_helpfile предоставит вам имя логического файла журнала.

Ссылаться на:

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

Если ваша база данных находится в модели полного восстановления и вы не делаете резервную копию TL, измените ее на SIMPLE.

Используйте команду DBCC ShrinkFile ({logicalLogName}, TRUNCATEONLY). Если это тестовая база данных, и вы пытаетесь сэкономить / освободить место, это поможет.

Однако помните, что журналы передачи имеют своего рода минимальный / устойчивый размер, до которого они будут расти. В зависимости от вашей модели восстановления, возможно, вы не сможете сжать журнал - если он находится в режиме FULL и вы не создаете резервные копии журнала TX, журнал не может быть сжат - он будет расти вечно. Если вам не нужны резервные копии журнала TX, переключите модель восстановления на Простой.

И помните, никогда и ни при каких обстоятельствах не удаляйте файл журнала (LDF)! У вас будет практически мгновенное повреждение базы данных. Приготовлено! Выполнено! Потерянные данные! Если не исправить основной файл MDF, он может навсегда испортиться.

Никогда не удаляйте журнал транзакций - вы потеряете данные! Часть ваших данных находится в журнале TX (независимо от модели восстановления) ... если вы отсоедините и «переименуете» файл журнала TX, который фактически является частью вашей базы данных удаляет.

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

Посмотрите сообщения в блоге Пола Рэндала по этой самой теме, плохой совет.

Также вообще не используйте shrinkfile для файлов MDF, так как это может серьезно фрагментировать ваши данные. Ознакомьтесь с его разделом «Плохие советы» для получения дополнительной информации («Почему не следует сжимать файлы данных»).

Посетите веб-сайт Пола - он отвечает именно на эти вопросы. В прошлом месяце он рассмотрел многие из этих проблем в своей серии статей Миф в день.

1. Резервная БД
2. Отключить БД
3. Переименовать файл журнала
4. Прикрепите БД (при подключении удалите переименованный .ldf (файл журнала). Выберите его и удалите, нажав кнопку «Удалить»)
5. Новый файл журнала будет создан заново
6. Удалить переименованный файл журнала.

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

Попробуй это:

USE DatabaseName

GO

DBCC SHRINKFILE( TransactionLogName, 1)

BACKUP LOG DatabaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE( TransactionLogName, 1)

GO 

Журнал транзакций БД Уменьшить до минимального размера:

1. Резервное копирование: журнал транзакций
2. Сжать файлы: журнал транзакций
3. Резервное копирование: журнал транзакций
4. Сжать файлы: журнал транзакций

Провел тесты на нескольких БД: эта последовательность работает.

Обычно это сжимается до 2 МБ.

ИЛИ скриптом:

DECLARE @DB_Name nvarchar(255);
DECLARE @DB_LogFileName nvarchar(255);
SET @DB_Name = '<Database Name>';               --Input Variable
SET @DB_LogFileName = '<LogFileEntryName>';         --Input Variable
EXEC 
(
'USE ['+@DB_Name+']; '+
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2) ' +
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2)'
)
GO

1. Сделайте резервную копию файла MDB.
2. Остановить службы SQL
3. Переименуйте файл журнала
4. Запустить сервис

(Система создаст новый файл журнала.)

Удалите или переместите переименованный файл журнала.

-- DON'T FORGET TO BACKUP THE DB :D (Check [here][1]) 


USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

От: DBCC SHRINKFILE (Transact-SQL)

Вы можете сначала сделать резервную копию.

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

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

От Статья Microsoft о BACKUP

We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately.

Чтобы этого избежать, сделайте резервную копию файла журнала на диск перед его сжатием. Синтаксис будет выглядеть примерно так:

BACKUP LOG MyDatabaseName 
TO DISK='C:\DatabaseBackups\MyDatabaseName_backup_2013_01_31_095212_8797154.trn'

DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)

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

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

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

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

• Как просматривать журналы транзакций в SQL Server 2008

• Прочтите файл журнала (* .LDF) в SQL Server 2008

USE DATABASE_NAME;
GO

ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO
--First parameter is log file name and second is size in MB
DBCC SHRINKFILE (DATABASE_NAME_Log, 1);

ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO

Вы можете получить подобное сообщение об ошибке, когда выполняемые выше команды

“Cannot shrink log file (log file name) because the logical log file located at the end of the file is in use“

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

База данных → щелкните правой кнопкой мыши Характеристики → файл → добавьте еще один файл журнала с другим именем и установите путь, такой же, как у старого файла журнала, с другим именем файла.

База данных автоматически выбирает вновь созданный файл журнала.

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

Помимо ответов на этот вопрос, я рекомендую прочитать и разобраться в распространенных мифах о журналах транзакций. Эти показания могут помочь понять журнал транзакций и решить, какие методы использовать для его «очистки»:

От 10 самых важных мифов о журналах транзакций SQL Server:

Myth: My SQL Server is too busy. I don’t want to make SQL Server transaction log backups

One of the biggest performance intensive operations in SQL Server is an auto-grow event of the online transaction log file. By not making transaction log backups often enough, the online transaction log will become full and will have to grow. The default growth size is 10%. The busier the database is, the quicker the online transaction log will grow if transaction log backups are not created Creating a SQL Server transaction log backup doesn’t block the online transaction log, but an auto-growth event does. It can block all activity in the online transaction log

От Мифы о журнале транзакций:

Myth: Regular log shrinking is a good maintenance practice

FALSE. Log growth is very expensive because the new chunk must be zeroed-out. All write activity stops on that database until zeroing is finished, and if your disk write is slow or autogrowth size is big, that pause can be huge and users will notice. That’s one reason why you want to avoid growth. If you shrink the log, it will grow again and you are just wasting disk operation on needless shrink-and-grow-again game

Это случилось со мной, когда файл журнала базы данных был размером 28 ГБ.

Что вы можете сделать, чтобы это уменьшить? Фактически, файлы журнала - это те данные файла, которые SQL-сервер сохраняет, когда транзакция произошла. Для обработки транзакции SQL-сервер выделяет для нее страницы. Но после завершения транзакции они не освобождаются внезапно в надежде, что транзакция может быть похожа на ту же самую. Это удерживает пространство.

Шаг 1: Сначала запустите эту команду в исследуемом запросе к базе данных. пропускной пункт

Шаг 2: Щелкните правой кнопкой мыши базу данных Задача> Резервное копирование Выберите тип резервного копирования как журнал транзакций Добавьте адрес назначения и имя файла для хранения данных резервной копии (.bak)

Повторите этот шаг еще раз, а теперь укажите другое имя файла.

Шаг 3: Теперь заходим в базу данных Щелкните правой кнопкой мыши базу данных

Задачи> Сжатие> Файлы Выберите тип файла как журнал Действие сжатия как освобождение неиспользуемого пространства

Шаг 4:

Проверьте свой файл журнала обычно в SQL 2014 это можно найти по адресу

C: \ Program Files \ Microsoft SQL Server \ MSSQL12.MSSQL2014EXPRESS \ MSSQL \ DATA

В моем случае он уменьшился с 28 ГБ до 1 МБ.

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

alter database <database_name> set emergency;
use <database_name>;
checkpoint;
checkpoint;
alter database <database_name> set online;
dbcc shrinkfile(<database_name>_log, 200);

Слегка обновленный ответ для MSSQL 2017 и с использованием студии управления SQL-сервером. Я пошел в основном из этой инструкции https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/

У меня была недавняя резервная копия базы данных, поэтому я сделал резервную копию журнала транзакций. Затем я снова сделал резервную копию для хорошей меры. Наконец, я уменьшил размер файла журнала и увеличил его с 20 до 7 МБ, что намного больше соответствует размеру моих данных. Я не думаю, что журналы транзакций когда-либо копировались с тех пор, как это было установлено 2 года назад ... так что поместите эту задачу в календарь обслуживания.