Как очистить файл журнала MS SQL

Обновлено: 21.11.2024

Последнее обновление: 18.06.2020

Автор: Кэролайн Миллс

В этой статье описывается разница между сжатием и усечением журнала транзакций в Microsoft® SQL Server®. В нем описываются различные сценарии использования и рассказывается, как выполнять эти задачи.

Обрезка журнала

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

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

Сократить журнал транзакций

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

Примечание. Для усечения журнала требуется фиксированная роль сервера sysadmin или фиксированная роль базы данных db_owner.

Щелкните правой кнопкой мыши базу данных и выберите "Свойства" -> "Параметры".

Установите модель восстановления "Простая" и выйдите из меню.

Снова щелкните базу данных правой кнопкой мыши и выберите Задачи -> Сжать -> Файлы.

Измените тип на Журнал .

В разделе "Сжать" выберите "Реорганизовать страницы перед освобождением неиспользуемого пространства" и нажмите "ОК".

Когда процесс завершится, переключите модель восстановления обратно на Full или Bulk-Logged и создайте полную резервную копию базы данных.

Сжатие журнала

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

Уменьшить журнал транзакций

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

Примечание. Для сжатия журнала требуется фиксированная роль сервера sysadmin или фиксированная роль базы данных db_owner.

Щелкните правой кнопкой мыши базу данных и выберите Задачи -> Сжать -> Файлы.

Измените тип на Журнал .

В разделе "Сжать" выберите "Освободить неиспользуемое пространство" и нажмите "ОК".

Статьи по теме:

Поделитесь этой информацией:

Если не указано иное, содержимое этого сайта находится под лицензией Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License

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

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

Резервная копия журнала транзакций

При настройке базы данных с использованием простой модели восстановления журнал транзакций SQL Server будет помечен как неактивный и автоматически усечен после фиксации активной транзакции. Это не относится к моделям восстановления базы данных Full и Bulk-Logged. Если для базы данных настроена модель полного восстановления, журнал транзакций SQL Server в файле журнала транзакций будет помечен как неактивный после фиксации транзакции без автоматического усечения, поскольку он будет ожидать выполнения резервной копии журнала транзакций. Напомним, что только резервная копия журнала транзакций, но НЕ полная резервная копия базы данных, усекает журналы транзакций из файла журнала транзакций и делает его доступным для повторного использования.Если из базы данных не создается резервная копия журнала транзакций, файл журнала транзакций будет непрерывно увеличиваться без усечения, пока не закончится свободное место.

Резервная копия журнала транзакций SQL Server может быть получена только из базы данных, если модель восстановления этой базы данных — Полная или Массовая. Модель восстановления базы данных можно проверить на вкладке «Параметры» окна «Свойства базы данных», как показано ниже:

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

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

Давайте сделаем полную резервную копию базы данных, чтобы можно было сделать резервную копию журнала транзакций для этой базы данных. Мы будем использовать команду T-SQL BACKUP DATABASE для выполнения операции полного резервного копирования базы данных в нашем примере. Дополнительные сведения о различных способах и параметрах резервного копирования базы данных в SQL Server см. в серии статей о резервном копировании и восстановлении SQL Server. Полную резервную копию базы данных можно сделать с помощью приведенного ниже сценария T-SQL:

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

Следующие представленные способы будут применяться к SQL Server 2014/2012 для удаления файла журнала с помощью SQL Server Management Studio или Transact — SQL.

Способ 1: удалить файл журнала SQL Server в SQL Server Management Studio

Шаг 1. Уменьшите файл журнала сервера

<р>1. Войдите в SQL Server Management Studio. В обозревателе объектов разверните экземпляр, подключенный к SQL Server.

<р>2. Разверните базу данных и щелкните правой кнопкой мыши базу данных, которую вы хотите сжать.

<р>3. Перейдите в Задачи – Уменьшить, а затем щелкните Файлы.

<р>4. Выберите тип файла и имя файла.

Как правило, файл журнала будет уменьшен после того, как вы нажмете "ОК". Но если вы хотите настроить его более подробно, можно выбрать параметры ниже.

Вариант 1. Установите флажок Освободить неиспользуемое пространство.

Вариант 2. Установите флажок Реорганизовать файлы перед освобождением неиспользуемого пространства.

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

Вариант 3. Введите максимальный процент свободного места, который должен остаться в файле базы данных после сокращения базы данных. Значение может быть от 0 до 99. Этот параметр доступен, только если включен параметр Реорганизовать файлы перед освобождением неиспользуемого пространства.

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

Вариант 4. Установите флажок Пустой файл путем переноса данных в другие файлы в той же файловой группе.

<р>5. Нажмите "ОК".

Шаг 2. Удалите файл журнала SQL Server

<р>1. В обозревателе объектов подключите экземпляр к SQL Server Database Engine, а затем разверните этот экземпляр.

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

<р>3. Выберите страницу «Файлы». В сетке Файлы базы данных выберите файл, который нужно удалить, и нажмите Удалить.

<р>4. Нажмите "ОК".

Способ 2: удалить файл журнала SQL Server с помощью Transact-SQL

Если вы знакомы с Transact-SQL, следуйте этому способу работы с базой данных SQL Server или удалением файла журнала.

Шаг 1. Очистите файл журнала SQL

<р>1. Подключитесь к ядру базы данных.

<р>2. На стандартной панели нажмите Новый запрос.

<р>3. Скопируйте и вставьте следующий пример в окно запроса и нажмите «Выполнить».

В этом примере команда DBCC SHRINKFILE используется для уменьшения размера файла данных с именем DataFile1 в базе данных UserDB до 8 МБ.

Шаг 2. Удалите файл журнала SQL Server

<р>1. Подключитесь к ядру базы данных.

<р>2. На стандартной панели нажмите Новый запрос.

<р>3. Скопируйте и вставьте следующий пример в окно запроса и нажмите «Выполнить». В этом примере удаляется файл test1dat4.

ИСПОЛЬЗОВАТЬ master;
ПЕРЕХОД
ИЗМЕНИТЬ БАЗУ ДАННЫХ AdventureWorks2012
УДАЛИТЬ ФАЙЛ test1dat4;
ПЕРЕХОД

Сравнивая способ 1 и способ 2, первый способ, несомненно, проще для новых пользователей SQL Server. Но команда может работать быстро. Однако будет легче контролировать частоту сбоев при удалении файла SQL Server с помощью SQL Server Management Studio. Одним словом, они могут сжать, а затем удалить файл базы данных или файл журнала. Разница лишь в реализации.

Статьи по теме:

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

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

Зачем очищать журнал транзакций SQL Server?

Во время работы SQL Server журнал транзакций увеличивается, если происходят какие-либо изменения в базе данных. Регулярное управление размером журнала транзакций необходимо для предотвращения заполнения журнала транзакций. Усечение журнала или очистка журнала транзакций SQL Server требуется, чтобы журнал не заполнялся. Процесс усечения удаляет неактивные файлы виртуального журнала из логического журнала транзакций, освобождая место для повторного использования физическим журналом транзакций. Журнал транзакций в конечном итоге заполнит все дисковое пространство, выделенное для его физических файлов журнала, если он никогда не усекается.

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

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

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

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

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

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

Усечение журнала освобождает место в файле журнала для повторного использования. Поэтому это также называется очисткой журнала. Файл журнала транзакций логически разделен на небольшие фрагменты, называемые виртуальными файлами журнала (VLF). Каждый файл VLF — это единица измерения, которая может быть помечена как доступная для повторного использования (бесплатная) или недоступная для повторного использования (использованная). VLF помечается как «активный», если он используется, и «неактивный», если он свободен. Очистить журнал транзакций SQL Server означает найти и сделать VLF бесплатными.

Примечание. Следует помнить, что усечение журнала не предназначено для уменьшения размера физического файла журнала. Log Shrinking необходим для уменьшения физического файла журнала.

Очистка журнала транзакций SQL Server состоит из двух шагов. Во-первых, нам нужно выполнить резервное копирование журнала с параметром TRUNCATE_ONLY, а следующим шагом будет использование функции DBCC SHRINKFILE для сжатия файла до требуемого размера.

BACKUP LOG WITH TRUNCATE_ONLY — не лучший вариант, так как он очищает все содержимое нашего журнала транзакций без его резервного копирования. Многие люди используют эту команду перед сжатием файла журнала с помощью команды DBCC SHRINKFILE, освобождающей место на диске. TRUNCATE_ONLY недоступен в более поздних версиях SQL Server.

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

Уменьшить файл журнала SQL Server с помощью SQL Server Management Studio

Щелкните правой кнопкой мыши базу данных и выберите "Задачи", затем "Сжать", а затем выберите "Файлы":

Измените тип файла на log.

Уменьшить файл журнала транзакций SQL с помощью T-SQL

Для простого восстановления будет использоваться следующая команда:

DBCC SHRINKFILE (LogFileName, желаемый размер в МБ)

Для полного восстановления (только если мы не возражаем против потери данных в файле журнала) используются следующие команды:

Другой способ уменьшить размер файла журнала транзакций SQL – создать резервную копию журнала базы данных с помощью следующей команды:

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

Читайте также: