Как почистить log файл ms sql
Обновлено: 30.06.2024
Я не эксперт по SQL, и мне напоминают об этом каждый раз, когда мне нужно сделать что-то помимо основ. У меня есть тестовая база данных небольшого размера, но журнал транзакций определенно есть. Как очистить журнал транзакций?
В Managment Studio должна быть команда: «Нажмите, чтобы сжать журнал», и все готово.Уменьшение размера файла журнала должно быть действительно зарезервировано для сценариев, в которых произошел неожиданный рост, который, как вы ожидаете, не произойдет снова. Если размер файла журнала снова увеличится до того же размера, то временное его сжатие достигается не очень сильно. Теперь, в зависимости от целей восстановления вашей базы данных, это те действия, которые вы должны предпринять.
Никогда не вносите никаких изменений в вашу базу данных, не убедившись, что вы можете восстановить ее, если что-то пойдет не так.
(И под восстановлением на момент времени я имею в виду, что вы заботитесь о возможности восстановления чего-либо, кроме полной или дифференциальной резервной копии.)
Предположительно ваша база данных находится в FULL режиме восстановления. Если нет, то убедитесь, что это:
Даже если вы регулярно выполняете полное резервное копирование, файл журнала будет увеличиваться и увеличиваться до тех пор, пока вы не выполните резервное копирование журнала - это для вашей защиты, а не для ненужного расходования места на диске. Вы должны выполнять эти резервные копии журнала довольно часто, в соответствии с вашими целями восстановления. Например, если у вас есть бизнес-правило, которое гласит, что вы можете позволить себе потерять не более 15 минут данных в случае аварии, у вас должно быть задание, которое будет резервировать журнал каждые 15 минут. Вот скрипт, который будет генерировать имена файлов с метками времени на основе текущего времени (но вы также можете делать это с планами обслуживания и т. Д., Только не выбирайте какие-либо параметры сжатия в планах обслуживания, они ужасны).
Обратите внимание, что это \\backup_share\ должно быть на другом компьютере, который представляет другое устройство хранения данных. Резервное копирование на один и тот же компьютер (или на другой компьютер, использующий те же базовые диски, или другую виртуальную машину, расположенную на том же физическом хосте) на самом деле не поможет вам, так как, если компьютер взорвется, вы потеряете базу данных. и его резервные копии. В зависимости от вашей сетевой инфраструктуры может иметь больше смысла делать резервные копии локально, а затем переносить их в другое место за кулисами; в любом случае вы хотите как можно быстрее удалить их с основного компьютера базы данных.
Теперь, когда вы выполняете регулярное резервное копирование журналов, разумно будет сжать файл журнала до чего-то более разумного, чем то, что было создано до сих пор. Это не означает SHRINKFILE повторный запуск до тех пор, пока файл журнала не станет размером 1 МБ - даже если вы часто выполняете резервное копирование журнала, ему все равно необходимо учитывать сумму любых одновременных транзакций, которые могут произойти. События автоматического увеличения файла журнала являются дорогостоящими, поскольку SQL Server должен обнулять файлы (в отличие от файлов данных, когда включена мгновенная инициализация файла), и пользовательские транзакции должны ждать, пока это произойдет. Вы хотите выполнять эту процедуру как можно меньше, и вы, конечно, не хотите, чтобы ваши пользователи платили за нее.
Обратите внимание, что вам может потребоваться выполнить резервное копирование журнала дважды, прежде чем станет возможным сокращение (спасибо Роберту).
Итак, вам нужно найти практичный размер для вашего файла журнала. Никто здесь не может сказать вам, что это такое, не зная намного больше о вашей системе, но если вы часто сжимали файл журнала, и он снова рос, хороший водяной знак, вероятно, на 10-50% выше, чем самый большой, на котором он был , Допустим, это составляет 200 МБ, и вы хотите, чтобы все последующие события автоматического увеличения составляли 50 МБ, затем вы можете настроить размер файла журнала следующим образом:
Обратите внимание, что если размер файла журнала> 200 МБ, вам может понадобиться сначала выполнить это:
Если это тестовая база данных, и вы не заботитесь о восстановлении на определенный момент времени, то вам следует убедиться, что ваша база данных находится в SIMPLE режиме восстановления.
Перевод базы данных в SIMPLE режим восстановления гарантирует, что SQL Server повторно использует части файла журнала (по существу, постепенно исключая неактивные транзакции), вместо того, чтобы расти, чтобы вести учет всех транзакций (как FULL восстановление делает до тех пор, пока вы не создадите резервную копию журнала). CHECKPOINT события помогут контролировать журнал и убедиться, что он не должен расти, если вы не генерируете большую активность t-log между CHECKPOINT s.
Затем вы должны быть абсолютно уверены в том, что этот рост журнала действительно произошел из-за ненормального события (скажем, ежегодной весенней уборки или восстановления ваших самых больших показателей), а не из-за обычного ежедневного использования. Если вы сократите файл журнала до смехотворно небольшого размера, а SQL Server просто придется снова увеличить его, чтобы он соответствовал вашей обычной деятельности, что вы получили? Удалось ли вам использовать то дисковое пространство, которое вы освободили, только временно? Если вам нужно немедленное исправление, вы можете выполнить следующее:
В противном случае установите соответствующий размер и скорость роста. Согласно примеру в случае восстановления на момент времени, вы можете использовать тот же код и логику, чтобы определить, какой размер файла является подходящим, и установить приемлемые параметры автоматического роста.
Сделайте резервную копию журнала с помощью TRUNCATE_ONLY опции и затем SHRINKFILE . С одной стороны, эта TRUNCATE_ONLY опция устарела и больше не доступна в текущих версиях SQL Server. Во-вторых, если вы используете FULL модель восстановления, это разрушит цепочку журналов и потребует новой полной резервной копии.
Отключите базу данных, удалите файл журнала и снова присоедините . Я не могу подчеркнуть, насколько это может быть опасно. Ваша база данных может не восстановиться, она может быть подозрительной, вам, возможно, придется вернуться к резервной копии (если она у вас есть) и т. Д. И т. Д.
Используйте опцию «сжать базу данных» . DBCC SHRINKDATABASE и вариант плана обслуживания делать то же самое - плохие идеи, особенно если вам действительно нужно только решить проблему журнала. Выберите файл, который хотите настроить, и отрегулируйте его независимо, используя DBCC SHRINKFILE или ALTER DATABASE . MODIFY FILE (примеры выше).
Сократите файл журнала до 1 МБ . Это выглядит заманчиво, потому что, эй, SQL Server позволит мне делать это в определенных сценариях и смотреть на все свободное место! Если ваша база данных не предназначена только для чтения (и вам следует пометить ее как таковую, используя ее ALTER DATABASE ), это просто приведет к множеству ненужных событий роста, поскольку журнал должен учитывать текущие транзакции независимо от модели восстановления. Какой смысл временно освобождать это пространство, просто чтобы SQL Server мог вернуть его медленно и мучительно?
Создайте второй файл журнала . Это даст временное облегчение накопителю, который заполнил ваш диск, но это все равно, что пытаться починить проколотое легкое лейкопластырем. Вы должны работать с проблемным файлом журнала напрямую, а не просто добавлять еще одну потенциальную проблему. Помимо перенаправления некоторых действий журнала транзакций на другой диск, второй файл журнала действительно ничего не делает для вас (в отличие от второго файла данных), поскольку одновременно может использоваться только один из этих файлов. Пол Рэндал также объясняет, почему несколько файлов журнала могут укусить вас позже .
Вместо того, чтобы сжимать ваш файл журнала до некоторого небольшого количества и позволить ему постоянно расти автоматически с небольшой скоростью, установите его на некоторый достаточно большой размер (тот, который будет соответствовать сумме вашего наибольшего набора параллельных транзакций) и установите разумный автоматический рост задание в качестве запасного варианта, чтобы он не увеличивался в несколько раз для удовлетворения отдельных транзакций, и чтобы он был относительно редким для роста в обычных бизнес-операциях.
Наихудшие возможные настройки - 1 МБ или 10%. Забавно, но это настройки по умолчанию для SQL Server (на которые я жаловался и просил внести изменения безрезультатно ) - 1 МБ для файлов данных и 10% для файлов журналов. Первый слишком мал в наше время, а второй каждый раз приводит к более длинным и продолжительным событиям (скажем, размер файла журнала составляет 500 МБ, первый рост - 50 МБ, следующий - 55 МБ, следующий - 60,5 МБ и т. д. и т. д. - и при медленном вводе / выводе, поверьте мне, вы действительно заметите эту кривую).
Пожалуйста, не останавливайтесь здесь; в то время как большая часть рекомендаций, которые вы видите в отношении сокращения файлов журналов, по своей сути плоха и даже потенциально губительна, есть люди, которые больше заботятся о целостности данных, чем освобождают дисковое пространство.
Транзакционные логи в SQL Server 2012 с течением времени неизбежно растут, и в какой-то момент времени могут занять все доступное место на диске. Чтобы избежать такой ситуации, в SQL Server есть средства для урезания (Truncate) транзакционных логов, позволяющие высвободить место для повторного использования. Логи урезаются автоматически в зависимости от используемой модели восстановления:
Однако есть ситуации, когда автоматическое урезание логов не отрабатывает как надо, и файлы логов занимают все место на диске. Как правило, это происходит неожиданно, и администратору приходится срочно решать вопрос с освобождением места на диске.
В этом случае при подключении к БД 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.databaseHRESULT=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
Проблема
Рост файла журнала транзакций. С помощью команды DBCC SHRINKFILE не удается
уменьшить размер файла журнала транзакций до нужного размера .
Решение
Последовательность команд, которую нужно исполнить в Query Analyzer, выглядит
следующим образом:
BACKUP LOG Имя_Базы_Данных WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE(Имя_Файла_Журнала_Транзакций)
go
Более подробное описание и рекомендации по использованию этих команд можно найти в документации по Microsoft SQL Server.
backup log your_database with truncate_only
Может для начала спросить : А как ты шринк делаешь.
А то, к примеру, сверху в QA база Мастер торчит, а он удивляется, что лог не уменьшается.
Сделай сначала бекап LDF, а потом он уже шринканется
Удаление работает.
Отключи базу от сервака, удали журнал транзакций, подключи. Будет создан новый пустой журнал транзакций.
(21) с дуба рухнул?
---
дуб с маленькой буквы - типа дерево такое )))
Ответил на поставленный вопрос.
Ещё раз скажу: журнал транзакций можно удалить, при присоединении будет создан новый.
В чём проблема, если в данном случае человеку текущий ldf человеку не нужен?
(25) А при отсоединении базы все незавершенные транзакции будут автоматически "откачены" назад?
(25) хм, я думал в мдф начальные данные + в лдф все изменения
и пока фулл бэкап не сделаешь данные в мдф не переносятся при модели фулл
---
и продолжаю так думать )))
(27) Думай дальше.
(25) Нет, ни в коем случае. После того, как транзакция зафиксирована, она назад не откатится.
(29) Я немного не то имел в виду. Попробую переформулировать. Данные записываются в mdf файл по чекпойнту, либо лэйзи райтером. При отсоединении БД - данные уже измененные в памяти будут записаны в файл?
(31) Если не дадите пользователю завершить транзакцию (т.е. прервав его сессию), тогда изменения записаны не будут.
(27) и зря Вы так продолжаете думать) В жунале транзакций содержится информация о транзакциях. Активная часть - не зафикисрованные транзакции. Модель восстановления simple не используется для восстановления на определенный момент времени, т.е. данные о зафиксированных транзакциях, условно говоря, удаляются. В модели восстановления full/bulk-logged, информация о зафиксированных транзакциях перестает быть нужной после создания бэкапа журнала транзакций.
(32) Еще раз. Я записал документ и корректно завершил работу с системой. Все изменения были произведены в памяти. Данные будут записаны только по чекпойнту, либо lazy writer'ом. Вы уверены, что при отсоединении БД, будет вызван чекпойнт или запустится lazy writer?
"При перезапуске, служба SQL Server использует журнал транзакций для обнаружения завершенных транзакций, которые внесли изменения в данные, но не были записаны на диск и незавершенных транзакций" (http://www.sqlservercentral.com/articles/64582/ - пруфлинк, четвертый абзац раздела "How does SQL use the log?")
(32) Просто метод аттача без журнала какой-то "варварский" :). Я понимаю его необходимость при переносе базы и повреждении ldf-файла, но для очистки журнала.
(34) Чтобы устранить все сомнения, лучше, конечно же, сначала попробовать на тестовой базе.
(23) >журнал транзакций можно удалить, при присоединении будет создан новый.
Ну не совсем так, определённые шаманские действия все же нужно произвести:
Данный метод работает только для версии SQL2000
sp_configure 'allow updates', 1
reconfigure with override
go
select status from sysdatabases where name = '<db_name>'
и запоминаем/записываем значение на случай неудачи ребилда лога
update sysdatabases set status= 32768 where name = '<db_name>'
SQL Server скажет - Warning: The log for database '<db_name>' has been rebuilt.
9. Если все нормально, то там же выполняем
sp_dboption '<db_name>', 'single user', 'true'
go
9a.
Если Вам не удалось перевести базу в single user mode, то для проверки целостности данных можно попробовать dbo only mode
sp_dboption '<db_name>', 'dbo use only', 'true'
10. Если все в порядке, то
go
(37) Линуксоид? Любишь в гамаке в ластах в противогазе и стоя?
Нормальные люди для этого исползуют sp_attach_single_file_db.
(38) Ты FAQ читал?
У меня как правило ldf терялись не спроста, или диск наворачивался или еще что.
А в этом случае база суспект и никакой sp_attach_single_file_db не поможет.
Вывалит ошибку.
А самому ldf удалять, чтобы лог уменьшить это конечно придумать надо.
(40) Слишком много времени займет.
А можно ли из QA принудительно записать все накопленные изменения (транзакции), а потом грохнуть ldf?
Журнал транзакций во всех версиях Microsoft SQL Server имеет тенденцию расти со временем. Иногда он может заполнить все свободное место на сервере. Чтобы избежать этого, в SQL Server есть операция усечения журнала транзакций.
Журналы транзакций SQL Server и модель восстановления базы данных
Журналы транзакций используются для записи всех транзакций перед передачей данных в файл базы данных. Файлы журнала транзакций необходимы для отката базы данных до предыдущего состояния. Как правило, в журнале хранится порядковый номер, тип изменения, выполненные операции и т. д. Скорее всего, вы можете использовать SQL Server и без журнала транзакций, но тогда в в случае сбоя возможна потеря базы данных.
Журнал транзакций состоит из небольших логических элементов, называемых VLF (Virtual Log File). Вы можете узнать их количество, выполнив следующий запрос в контексте базы данных SQL Server:
Количество возвращаемых строк указывает на то, сколько виртуальных файлов сегментировано в журнале. В поле «Status» отображается текущее состояние сегмента. Значение 0 означает, что сегмент в настоящее время не занят и может использоваться. 2 означает, что сегмент используется. Если свободных сегментов нет и в настройках базы данных SQL Server разрешено увеличение журнала транзакций, он будет увеличен и будут созданы новые VLF. Если размер журнала транзакций фиксирован или на диске недостаточно места, все операции по изменению структуры базы данных или ее содержимого станут недоступными. Скорее всего, вы получите ошибку: Журнал транзакций для базы данных переполнен.
Файлы журнала усекаются автоматически, в зависимости от модели восстановления, используемой в настройках SQL Server:Когда используется модель полного восстановления базы данных, необходимо регулярно выполнять резервное копирование журнала транзакций, в противном случае он будет чрезмерно увеличиваться до тех пор, пока не займет все место на диске, и SQL Server не начнет сообщать об ошибке.
Как обрезать журналы транзакций на MS SQL Server?
Это означает, что на диске, где хранится журнал транзакций SQL, недостаточно места, и SQL не может записать новые данные транзакции. В этом случае вы можете обрезать файлы журналов SQL вручную (с помощью графического интерфейса Management Studio).
Чтобы обрезать журналы транзакций SQL, запустите SQL Server Management Studio (SSMS), выберите нужную базу данных, щелкните ее правой кнопкой мыши и выберите «Свойства» в контекстном меню. Перейдите в Параметры и переключите модель восстановления базы .
Меняем модель восстановления с Полной на Простую
Затем в главном меню перейдите в раздел «Задачи» -> «Сжать» -> «Файлы» .
В поле Тип файла выберите Журнал, в поле Имя файла укажите имя файла журнала. В поле «Операции сжатия» выберите « Реорганизовать страницы, перед тем как освободить неиспользуемое пространство» , установите нужный размер файла и нажмите «OK».
Нажимаем «OK». После завершения операции обязательно измените режим восстановления базы данных обратно на Полный.
Это только один из способов быстрого уменьшения размера логов. Не самый лучший, но очень простой и эффективный.
Кроме того, имейте в виду, что при усечении журналов транзакций SQL с помощью этого руководства все предыдущие резервные копии, сделанные в модели полного восстановления, могут быть отброшены. Вот почему этот метод следует использовать только в критических случаях, когда невозможно быстро очистить дисковое пространство другими способами.
Читайте также: