Как очистить журнал транзакций в sql 2012

Удаление файлов данных или журнала из базы данных

В этом разделе описывается удаление данных или файлов журналов в SQL Server с помощью SQL Server Management Studio или Transact-SQL.

В этом разделе

• Перед началом: Необходимые условияБезопасность
• Удаление файлов данных или журналов из базы данных с помощью следующих средств:Среда SQL Server Management StudioTransact-SQL

Перед началом

Предварительные условия

• Чтобы можно было удалить файл, он должен быть пустым. Дополнительные сведения см. в разделе Сжатие файла.

Безопасность

Разрешения

Необходимо разрешение ALTER в базе данных.

Использование среды SQL Server Management Studio

Удаление файлов данных или журнала из базы данных

1. В обозревателе объектовподключитесь к экземпляру компонента SQL Server Database Engine и разверните его.
2. Раскройте список Базы данных, щелкните правой кнопкой мыши базу данных, из которой удаляется файл, а затем выберите пункт Свойства.
3. Выберите страницу Файлы .
4. В списке Файлы базы данных выберите файл для удаления и нажмите кнопку Удалить.
5. Щелкните OK.

Использование Transact-SQL

Удаление файлов данных или журнала из базы данных

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

USE master; GO ALTER DATABASE AdventureWorks2022 REMOVE FILE test1dat4; GO 

Задача «Очистка журнала» (план обслуживания)

Используйте диалоговое окно «Задача очистки журнала», чтобы отменить старые исторические сведения из таблиц в msdb базе данных. Эта задача поддерживает удаление журнала резервного копирования и восстановления, журнала заданий агента SQL Server и журнала планов обслуживания.

В этом операторе sp_purge_jobhistory используются инструкции и sp_delete_backuphistory инструкции.

Список элементов пользовательского интерфейса

• Соединение Выберите соединение с сервером, которое будет использоваться для выполнения этой задачи.
• New Создать новое соединение с сервером для его использования при выполнении этой задачи. Диалоговое окно «Новое подключение» описано далее в этой статье.
• Журнал резервного копирования и восстановления Сохранение записей о том, когда были созданы последние резервные копии, может помочь SQL Server создать план восстановления, когда требуется восстановить базу данных. Срок хранения должен быть не меньше периода создания полных резервных копий базы данных.
• Журнал заданий агента SQL Server Этот журнал может помочь устранить ошибки, возникшие при выполнении заданий, или определить, почему были выполнены операции с базой данных.
• Журнал плана обслуживания Этот журнал может помочь устранить ошибки, возникшие при выполнении заданий плана обслуживания, или определить, почему были выполнены операции с базой данных.
• Удалить из журнала записи старше Укажите возраст элементов, которые следует удалить.
• Просмотр T-SQL Просмотрите инструкции Transact-SQL, выполняемые на сервере для этой задачи, на основе выбранных параметров.

Заметка Если количество затронутых объектов велико, построение этого отображения может занять значительное время.

Диалоговое окно «Новое соединение»

• Имя подключения Введите имя нового соединения.
• Выберите или введите имя сервера Выберите сервер для подключения при выполнении этой задачи.
• Обновить Обновите список доступных серверов.
• Введите данные для входа на сервер Укажите способ проверки подлинности на сервере.
• Использовать встроенную безопасность Windows Подключитесь к экземпляру ядра СУБД SQL Server с помощью проверки подлинности Microsoft Windows.
• Использовать указанные имя пользователя и пароль Подключитесь к экземпляру ядра СУБД SQL Server с помощью проверки подлинности SQL Server. Этот параметр недоступен.
• Имя пользователя Укажите имя входа SQL Server, используемое при проверке подлинности. Этот параметр недоступен.
• Пароль Укажите используемый при проверке подлинности пароль. Этот параметр недоступен.

См. также

• sp_purge_jobhistory (Transact-SQL)
• sp_delete_backuphistory (Transact-SQL)

Как урезать транзакционные логи в SQL Server 2012

21.11.2016

itpro

SQL Server

комментариев 5

Транзакционные логи в SQL Server 2012 с течением времени неизбежно растут, и в какой-то момент времени могут занять все доступное место на диске. Чтобы избежать такой ситуации, в SQL Server есть средства для урезания (Truncate) транзакционных логов, позволяющие высвободить место для повторного использования. Логи урезаются автоматически в зависимости от используемой модели восстановления:

• Simple (простая модель) — журналы урезаются после достижения контрольной точки
• Full (модель полного восстановления) – после выполнения резервной копии логов, если после последнего бэкапа было достигнута контрольная точка

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

В этом случае при подключении к БД MS SQL появляется такая ошибка:

Microsoft OLE Provider for SQL Server: The transaction log for database “DBName” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column is sys.database
HRESULT=80040E14, SQLSTATE=4 2000, native=9002

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

Как правило это ситуация может возникнуть при использовании полной модели восстановления (Full). В этой модели файлы журналов не усекаются, пока все транзакционные логи не попадут в бэкап. Это нужно для того, чтобы гарантировать непрерывную последовательность номеров записей (LSN) в журнале. Таким образом, чтобы журналы урезались, нужно выполнить полный бэкап БД, либо (быстрее), на время сменить модель восстановления на Simple.

Итак, чтобы урезать транзакционный лог, запустите консоль SQL Server Management Studio (SSMS), выберите нужную БД, и откройте ее свойства в контекстном меню. Затем перейдите на вкладку Options и измените модель восстановления БД (Recovery model) на Simple.

Затем в контекстном меню БД выберите Tasks -> Shrink -> Files. В поле File type выберите Log, а в поле File name – имя файла логов. В поле Shrink action нужно указать Reorganize pages before releasing unused space, и укажите до какого размера нужно ужать файл и нажмите OK.

После урезания лога, опять переключитесь на полную (Full)модель восстановления БД.

Все рассмотренные выше операции можно выполнить простым скриптом из Query Analizer (скрипт работает в SQL Server, начиная с 2008 версии).
USE ″DBName″
ALTER DATABASE ″DBName″ SET RECOVERY SIMPLE
DBCC SHRINKFILE (″DBName″, ″Размер до которого урезать лог″);
ALTER DATABASE ″DBName″ SET RECOVERY FULL

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

Предыдущая статья Следующая статья

Читайте далее в разделе SQL Server

Получение привилегии SeDebugPrivilege при включенной политике Debug Program

Как сбросить пароль SA в Microsoft SQL Server?

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

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

Журналы транзакций SQL Server и модель восстановления базы данных

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

Журнал транзакций состоит из небольших логических элементов, называемых VLF (Virtual Log File). Вы можете узнать их количество, выполнив следующий запрос в контексте базы данных SQL Server:

DBCC LOGINFO

Количество возвращаемых строк указывает на то, сколько виртуальных файлов сегментировано в журнале. В поле «Status» отображается текущее состояние сегмента. Значение 0 означает, что сегмент в настоящее время не занят и может использоваться. 2 означает, что сегмент используется. Если свободных сегментов нет и в настройках базы данных SQL Server разрешено увеличение журнала транзакций, он будет увеличен и будут созданы новые VLF. Если размер журнала транзакций фиксирован или на диске недостаточно места, все операции по изменению структуры базы данных или ее содержимого станут недоступными. Скорее всего, вы получите ошибку: Журнал транзакций для базы данных переполнен.

Файлы журнала усекаются автоматически, в зависимости от модели восстановления, используемой в настройках SQL Server:

• Простая модель восстановления – файлы журнала автоматически обрезаются после достижения контрольной точки (самый простой вариант, требующий администрирования базы данных). При использовании модели простого восстановления журнал транзакций очищается сразу после завершения транзакции. В этом режиме вы можете откатить вашу базу данных только до времени полного резервного копирования базы данных.
• Модель полного восстановления – журнал транзакций не будет очищен, пока не будет завершено резервное копирование журнала транзакций. Этот режим обеспечивает наилучшую возможность восстановления данных после сбоя. В полном режиме журнал транзакций (LDF) может увеличиваться (поскольку изменения базы данных накапливаются в этом журнале). В модели полного восстановления все транзакции SQL записываются в файлы журнала на диске и сохраняются там до создания резервной копии. Хранение журналов позволяет вам при необходимости вернуться к более ранней копии базы данных, и вы можете выполнить восстановление для каждой транзакции.
• С неполным протоколированием – этот режим позволяет сократить занимаемое пространство используя минимальные настройки ведения журнала. Если журнал был поврежден или с момента создания последней резервной копии журналов выполнялись операции с неполным протоколированием, все изменения после этого резервного копирования необходимо внести повторно. Иначе результаты работы потеряны не будут.

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

Как обрезать журналы транзакций на MS SQL Server?

Мы получили вот такое сообщение:

Microsoft OLE DB Provider for SQL Server: Журнал транзакций для базы данных “buh” заполнен. Чтобы обнаружить причину, по которой место в журнале не может быть повторно использовано, обратитесь к столбцу log_reuse_wait_desc таблицы
sys. databases HRESULT=80040E14, SQLStvr: Error state=2, Severity=11,native=9002, line=1

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

Чтобы обрезать журналы транзакций SQL, запустите SQL Server Management Studio (SSMS), выберите нужную базу данных, щелкните ее правой кнопкой мыши и выберите «Свойства» в контекстном меню. Перейдите в Параметры и переключите модель восстановления базы .

Меняем модель восстановления с Полной на Простую

Затем в главном меню перейдите в раздел «Задачи» -> «Сжать» -> «Файлы» .

В поле Тип файла выберите Журнал, в поле Имя файла укажите имя файла журнала. В поле «Операции сжатия» выберите « Реорганизовать страницы, перед тем как освободить неиспользуемое пространство» , установите нужный размер файла и нажмите «OK».

Нажимаем «OK». После завершения операции обязательно измените режим восстановления базы данных обратно на Полный.

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

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

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

• ← Как запустить программу без прав администратора и обойти подсказку UAC?
• Как исправить проблему с подключением к Интернет для Windows 10? →