MS SQL очистка журнала транзакций
В MS SQL очистка журнала транзакций необходима в том случае, если настроена полная модель восстановления базы данных. Если журнал транзакций переполнился, то ваша база данных откажется работать и будет выдавать ошибку: «журнал транзакций для базы данных заполнен». Почему такое происходит и как этого избежать? Рассмотрим два решения, которые помогут быстро устранить ошибку и продолжить работу с базой.
Увеличиваем размер журнала транзакций.
Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт [Файлы].
Для типа файла «Журнал» увеличиваем максимальный размера файла для авторасширения.
Сжимаем файл журнала транзакций.
Для сжатия журнала транзакций необходимо модель восстановления базы смнеить на простую, выполнить сжатие журнала, после чего модель восстановления переключить обратно на полную.
Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт [Параметры]. Модель восстановления выбираем «Простая» и нажимаем ОК.
Тип сжатия: Журнал
Операция сжатия: Реорганизовать файлы, перед тем как освободить неиспользуемое место
И указываем размер до которого необходимо сжать, например 0.
Теперь нужно вернуться в свойства базы к пункту [Параметры] и переключить модель восстановления на «Полная».
2 thoughts on “ MS SQL очистка журнала транзакций ”
А что делать, если файл журнала уже переполнен и свободное место ушло аж в минус (0%)?
Clear the Job History Log
В Управляемом экземпляре Azure SQL в настоящее время поддерживается большинство функций агента SQL Server (но не все). Подробные сведения см. в статье Различия в T-SQL между Управляемым экземпляром SQL Azure и SQL Server.
В этой статье описано, как удалить содержимое журнала заданий агента Microsoft SQL Server в SQL Server с помощью SQL Server Management Studio, Transact-SQL или управляющих объектов SQL Server.
Перед началом
безопасность
Использование среды SQL Server Management Studio
Очистка журнала заданий
В обозревателе объектов подключитесь к экземпляру компонента Компонент SQL Server Database Engineи разверните его.
Раскройте узел Агент SQL Server, а затем узел Задания.
Щелкните правой кнопкой мыши задание и выберите Просмотреть журнал.
В Программе просмотра файла журнала выберите задание, журнал которого нужно очистить, и выполните одно из следующих действий.
Щелкните Удалить.
Использование Transact-SQL
Очистка журнала заданий
В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.
Использование управляющих объектов SQL Server
Очистка журнала заданий
Используйте метод PurgeJobHistory класса JobServer на любом языке программирования, таком как Visual Basic, Visual C# или PowerShell. Дополнительные сведения см. в статье Управляющие объекты SQL Server (SMO).
Задача «Очистка журнала» (план обслуживания)
Список элементов пользовательского интерфейса
Соединение
Выберите соединение с сервером, которое будет использоваться для выполнения этой задачи.
Создать
Создать новое соединение с сервером для его использования при выполнении этой задачи. Диалоговое окно Создать соединение описано ниже в этом разделе.
Журнал резервного копирования и восстановления
Хранение записей о том, когда были созданы последние резервные копии, может помочь SQL Server в создании плана восстановления, когда потребуется восстановить базу данных. Срок хранения должен быть не меньше периода создания полных резервных копий базы данных.
Журнал заданий агента SQL Server
Этот журнал может помочь устранить ошибки, возникшие при выполнении заданий, или определить, почему были выполнены операции с базой данных.
Журнал плана обслуживания
Этот журнал может помочь устранить ошибки, возникшие при выполнении заданий плана обслуживания, или определить, почему были выполнены операции с базой данных.
Удалить из журнала записи старше
Укажите возраст элементов, которые следует удалить.
Если количество затронутых объектов велико, построение этого отображения может занять значительное время.
Диалоговое окно «Создание соединения»
Имя соединения
Введите имя нового соединения.
Выберите или введите имя сервера
Выберите сервер для подключения при выполнении этой задачи.
Обновить
Обновите список доступных серверов.
Введите данные для входа на сервер
Укажите способ проверки подлинности на сервере.
Использовать встроенную безопасность Windows
Подключитесь к экземпляру компонента SQL Server Компонент Database Engine с помощью проверки подлинности Microsoft Windows.
Использовать указанные имя пользователя и пароль
Подключитесь к экземпляру компонента SQL Server Компонент Database Engine с помощью проверки подлинности SQL Server. Этот параметр недоступен.
Пароль
Укажите используемый при проверке подлинности пароль. Этот параметр недоступен.
Как очистить журнал транзакций SQL Server?
Я не эксперт по SQL, и мне напоминают о том, что каждый раз, когда мне нужно сделать что-то за пределами основ. У меня есть тестовая база данных небольшого размера, но журнал транзакций определенно есть. Как очистить журнал транзакций?
20 ответов
уменьшение файла журнала действительно должно быть зарезервировано для сценариев, где он столкнулся с неожиданным ростом,который вы не ожидаете повторения. Если файл журнала снова вырастет до того же размера, не очень много будет достигнуто путем его временного сокращения. Теперь, в зависимости от целей восстановления вашей базы данных, это действия, которые вы должны предпринять.
во-первых, возьмите полную резервную копию
никогда не вносите никаких изменений в свою базу данных, не гарантируя, что вы можете восстановить если что-то пойдет не так.
если вы заботитесь о восстановлении точки во времени
(и под моментальным восстановлением я имею в виду, что вы заботитесь о том, чтобы восстановить что-либо, кроме полной или дифференциальной резервной копии.)
предположительно ваша база данных находится в FULL режим восстановления. Если нет, то убедитесь, что он:
отметим, что \backup_share\ должно быть на другом компьютере, который представляет собой другое базовое устройство хранения. Поддерживает до той же машине (или на другой машине, который использует тот же базовый дисков, или другой ВМ на одном физическом узле) на самом деле не помочь вам, поскольку если машина взрывается, вы потеряли свою базу и резервное копирование. В зависимости от вашей сетевой инфраструктуры может иметь смысл создавать резервные копии локально, а затем переносить их в другое место за кулисами; в любом случае вы хотите как можно быстрее удалить их с основного компьютера базы данных.
обратите внимание, что вам может потребоваться резервное копирование журнала дважды, прежде чем возможно сокращение (спасибо Роберт).
Итак, вам нужно придумать практичный размер для файла журнала. Никто здесь не может сказать вам, что это такое, не зная намного больше о вашей системе, но если вы часто сокращаете файл журнала, и он снова растет, хороший водяной знак, вероятно, на 10-50% выше, чем самый большой. Допустим, это произойдет. до 200 МБ, и вы хотите, чтобы какие-либо последующие события авторасширение на 50 МБ, а затем вы можете настроить размер файла журнала таким образом:
обратите внимание, что если файл журнала в настоящее время > 200 МБ, вам может потребоваться сначала запустить это:
если вы не заботитесь о восстановлении точки во времени
если это тестовая база данных, и вы не заботитесь о восстановлении точки во времени, то вы должны убедиться, что ваша база данных находится в SIMPLE восстановление режим.
ввод базы данных в SIMPLE режим восстановления гарантирует, что SQL Server повторно использует части файла журнала (по существу, отказ от неактивных транзакций) вместо роста, чтобы сохранить запись все операции (например, FULL recovery делает, пока вы не создадите резервную копию журнала). CHECKPOINT события помогут контролировать журнал и убедитесь, что он не должен расти, если вы не создаете много активности t-log между CHECKPOINT s.
Далее, вы должны быть абсолютно уверены, что этот рост журнала был действительно из-за аномального события (скажем, ежегодной весенней уборки или восстановления ваших самых больших индексов), а не из-за обычного повседневного использования. Если вы уменьшите файл журнала до смехотворно малого размера, и SQL Server просто должен снова его вырастить, чтобы приспособить вашу обычную деятельность, что вы получили? Вы смогли использовать то дисковое пространство, которое освободили только временно? Если вам нужно немедленное исправление, то вы можете запустить следующее:
в противном случае, установите соответствующий размер и темпы роста. Как в Примере в момент времени дело восстановления, вы можете использовать тот же код и логику, чтобы определить, какой размер подходит и установить разумные параметры авторасширения.
некоторые вещи, которые вы не хотите делать
отсоедините базу данных, удалите файл журнала и повторно присоедините. Я не могу подчеркнуть, насколько это опасно. Ваша база данных может не вернуться, она может появиться как подозреваемый, вам может потребоваться вернуться к резервной копии (если она у вас есть) и т. д. так далее.
сжать файл журнала до 1 МБ. Это выглядит заманчиво, потому что, эй, SQL Server позволит мне сделать это определенные сценарии, и посмотрите на все пространство, которое он освобождает! Если ваша база данных не только для чтения (и это так, вы должны пометить ее как таковую, используя ALTER DATABASE ), это абсолютно просто приведет ко многим ненужным событиям роста, так как журнал должен учитывать текущие транзакции независимо от модели восстановления. Какой смысл временно освобождать это пространство, чтобы SQL Server мог вернуть его медленно и болезненно?
создайте второй файл журнала. Этот временно облегчит работу диска, который заполнил ваш диск, но это все равно что пытаться исправить проколотое легкое с помощью пластыря. Вы должны иметь дело с проблемным файлом журнала напрямую, а не просто добавлять другую потенциальную проблему. Кроме перенаправления некоторых действий журнала транзакций на другой диск, второй файл журнала действительно ничего не делает для вас (в отличие от второго файла данных), так как только один из файлов может быть использован одновременно. пол Рэндал также объясняет, почему несколько файлы журнала могут укусить вас позже.
опережение
вместо того, чтобы сжимать файл журнала до некоторого небольшого количества и позволять ему постоянно авторасти с небольшой скоростью самостоятельно, установите его в некоторый разумно большой размер (тот, который будет учитывать сумму вашего наибольшего набора параллельных транзакций) и установите разумную настройку авторасти в качестве резервного, так что ему не нужно расти несколько раз, чтобы удовлетворить отдельные транзакции, и так что он будет относительно редко, чтобы он когда-либо должен был расти во время обычных деловых операций.
более дальнеишее чтение
пожалуйста, не останавливайтесь здесь; в то время как большая часть советов, которые вы видите там о сокращении файлов журналов, по своей сути плоха и даже потенциально катастрофична, есть некоторые люди, которые больше заботятся о целостности данных, чем об освобождении дискового пространства.
Безопасное удаление (чистка) логов mysql-bin в MySQL или MariaDB
Файлы mysql-bin.xxxxxx представляют из себя бинарные логи со всеми запросами к базе. Они необходимы для репликации данных или восстановления информации в случае необходимости.
Так данные файлы со временем могут занимать много дискового пространства, необходимо выполнить их чистку или, вовсе, настроить автоматическое удаление.
Данная инструкция не привязана к какой-либо системе — она подойдет как для Windows, так и Linux, например, Ubuntu или CentOS. Так как MariaDB — ответвление от MySQL, инструкция также актуально и для нее.
Прежде чем чистить логи, необходимо убедиться, что для СУБД не настроена репликация данных с другими серверами. Если репликация есть, необходимо убедиться, что все данные корректно скопированы. Удаление лога, для которого еще не прошла синхронизация приведет к необходимости восстанавливать репликацию.
Ручная чистка логов
Запросы выполняются из командной оболочки MySQL.
Для удаления конкретного bin-файла:
> PURGE BINARY LOGS TO ‘mysql-bin.000145’;
* где mysql-bin.000145 — имя файла с логами.
Для удаления логов за определенный период:
> PURGE BINARY LOGS BEFORE ‘2017-05-07 00:00:00’;
* удаляем логи до 5-о мая 2017 года.
* удаляем все, оставляем логи за последние 90 дней.
Автоматическое удаление
Открываем конфигурационный файл СУБД:
или в более ранних версиях:
и в секцию [mysqld] добавляем следующее:
* в данном примере мы настроили срок хранения логов в 90 дней.
Настройки применятся после перезагрузки сервера баз данных:
systemctl restart mysql || systemctl restart mariadb
Также будет выполнена чистка логов, которые старше выставленной даты (90 дней в нашем примере).