Как урезать транзакционные логи в SQL Server 2012
21.11.2016
itpro
SQL Server
комментария 4
Транзакционные логи в SQL Server 2012 с течением времени неизбежно растут, и в какой-то момент времени могут занять все доступное место на диске. Чтобы избежать такой ситуации, в SQL Server есть средства для урезания (Truncate) транзакционных логов, позволяющие высвободить место для повторного использования. Логи урезаются автоматически в зависимости от используемой модели восстановления:
- Simple (простая модель) — журналы урезаются после достижения контрольной точки
- Full (модель полного восстановления) – после выполнения резервной копии логов, если после последнего бэкапа было достигнута контрольная точка
Однако есть ситуации, когда автоматическое урезание логов не отрабатывает как надо, и файлы логов занимают все место на диске. Как правило, это происходит неожиданно, и администратору приходится срочно решать вопрос с освобождением места на диске.
В этом случае при подключении к БД MS SQL появляется такая ошибка:
Это означает, что транзакционные логи заняли все свободное место и 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
Как почистить лог транзакций ms sql
Добрый вечер коллеги!
Перечитал кучу статей, но так и не нашел ответа на свой вопрос.
Есть 5 баз 1С в MSQL 2012, Настроен Maintance plan. В конце Задачи"Очистка после обслуживания", и "Очистка журнала". Но логи не удаляются приходиться в ручную очищать после предельного переполнения. Метод восстановления баз стоит полный.
1.Что будет если установлю метод восстановления простой
2. На что влияет если я выполняю сначала переход с полного режима на простой, после очищаю лог и потом снова высталяю метод восстановления полный
3. Возможно есть какая то фишка по очистке логов.
Ответы
На сколько я знаю, переход с полного на простой метод восстановления безопасно выполнять, только после выполнения полной резервной копии баз-данных, как сообстно и очистку transactionLog.
Avis de non-responsabilité:
Mon opinion ne peut pas coïncider avec la position officielle de Microsoft.
Bien cordialement, Andrei .
MCP
- Помечено в качестве ответа Иван Проданов Microsoft contingent staff, Moderator 10 октября 2018 г. 5:47
Вы принципиально не понимаете необходимость и методы управления журналом транзакций. У меня журнал транзакций занимает объем порядка 40GB в нормальном режиме работы базы. И я его не урезаю, т.к. он все равно у вас вырастет до такого объема, если под нагрузкой имеет такой объем.
Идея иметь большой файл — сокращение операций ввода/вывода, которые потребуются сделать сиквелу при приращении файла логов. Операции ввода/вывода априори медленные.
поэтому, если вы используете модель восстановления Full, выполняйте регулярное резервное копирование журнала транзакций, смиритесь с его объемом и не делайте shrink. В противовес к этому, вы получаете возможность восстановления на максимально близкий к падению момент времени.
Если вы все таки хотите урезать логи, то уйдите на Simple и откатывайтесь на последний полный бэкап.
Innovation distinguishes between a leader and a follower — Steve Jobs
- Помечено в качестве ответа Razor1212007 20 ноября 2018 г. 11:41
Все ответы
На сколько я знаю, переход с полного на простой метод восстановления безопасно выполнять, только после выполнения полной резервной копии баз-данных, как сообстно и очистку transactionLog.
Avis de non-responsabilité:
Mon opinion ne peut pas coïncider avec la position officielle de Microsoft.
Bien cordialement, Andrei .
MCP
- Помечено в качестве ответа Иван Проданов Microsoft contingent staff, Moderator 10 октября 2018 г. 5:47
На сколько я знаю, переход с полного на простой метод восстановления безопасно выполнять, только после выполнения полной резервной копии баз-данных, как сообстно и очистку transactionLog.
Avis de non-responsabilité:
Mon opinion ne peut pas coïncider avec la position officielle de Microsoft.
Bien cordialement, Andrei .
MCP
Innovation distinguishes between a leader and a follower — Steve Jobs
Модель восстановления стоит Full. Создан план обслуживания, настроена очистка логов. Но задача не выполняется. Либо я что то не то делаю.
У вас нет шага по резервному копированию журнала транзакций базы данных.
И очистки логов нет. Очистка журнала делает очистку журналов заданий в базе, но никак не логов.
Поэтому еще раз: или меняйте модель восстановления, как вам уже говорили, или выполняйте резервное копирование журнала транзакций дополнительным шагом.
Innovation distinguishes between a leader and a follower — Steve Jobs
У меня стоит полная модель восстановления, я добавил шаг по резервному копированию журнала транзакций базы данных. Но как теперь очистить лог?
- Изменено Razor1212007 7 ноября 2018 г. 12:02
Microsoft recommends that instead of truncating logs, you switch to simple recovery mode instead. That way you don’t generate logs you won’t be using, and you won’t incur performance impacts from repeatedly filling and truncating the logs. You also remove the need to regularly back up the transaction log.
Innovation distinguishes between a leader and a follower — Steve Jobs
How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008, R2, 2012
Microsoft recommends that instead of truncating logs, you switch to simple recovery mode instead. That way you don’t generate logs you won’t be using, and you won’t incur performance impacts from repeatedly filling and truncating the logs. You also remove the need to regularly back up the transaction log.
Innovation distinguishes between a leader and a follower — Steve Jobs
Если речь идет о команде Shrink, то скорее всего это сжатие.
Сжатие файлов данных позволяет освободить неиспользуемое пространство путем перемещения страниц данных с конца файла в незанятое пространство ближе к началу файла. Когда в конце файла образуется достаточно свободного места, страницы данных в конце файла могут быть освобождены и возвращены в файловую систему.
Avis de non-responsabilité:
Mon opinion ne peut pas coïncider avec la position officielle de Microsoft.
Bien cordialement, Andrei .
MCP
Кто нибудь может написать Русским языком сделай так и так и будет тебе счастье. Как всегда слов много а по делу нифига. Ссылками все кидаются. Как и в безопасности администраторов много, а у кого идеальная система безопасности не понятно.
- Изменено Razor1212007 15 ноября 2018 г. 14:08
А вы сходите к своему руководству и попросите уменьшить вам ЗП в пользу сообщества?
Вам дали ссылки на материалы. Это вопрос обсуждался уже даже не 1000 раз. Но вы упорно не хотите сделать работу, вы хотите, чтобы ее за вас сделали.
А вы сходите к своему руководству и попросите уменьшить вам ЗП в пользу сообщества?
Вам дали ссылки на материалы. Это вопрос обсуждался уже даже не 1000 раз. Но вы упорно не хотите сделать работу, вы хотите, чтобы ее за вас сделали.
И что вы плачьтесь по зарплате, вам чего то не хватает, так наверно работу надо искать лучше. А зачем вы тут сидите Гуру администраторы. Ссылки кидать? Сколько я читал информации так и не нашел решения. Логично если бы разобрался, наверно не задавал вопрос.
- Изменено Razor1212007 16 ноября 2018 г. 12:33
т.к. от вас так и не дождались требований к точке восстановления при отказе (RPO) и времени восстановления (RTO), то точно ответить за вас никто не может.
В общем случае, я думаю, вам поможет следующее:
1. Установите режим восстановления Simple
2. Задайте желаемый размер файла логов в свойствах базы
3. переделайте задание резервного копирования, исключив из него резервное копирование журнала транзакций
В данном решении вы восстанавливаетесь на последний полный бэкап. Если он у вас проходит раз в день, то теряете данные, сделанные после бэкапа, если раз в неделю, то максимум за неделю.
Если вас это устраивает, то план действий выше. Если требования иные, то просьба озвучить их
Innovation distinguishes between a leader and a follower — Steve Jobs
Так я спросил но никто не ответил """"Получается нужно переходить на простой режим восстановления. И если что не так с базой то откатиться на вечерний бэкап и все?"""
может не поняли меня. Интересует только восстановление на дату бэкапа, т.е на каждый предыдущий день вечер. Вам спасибо за четкое объяснение.
А для себя было интересно как восстановить базу на момент ее падения. Я так понимаю использовать метод Full, делать резервную копию транзакции, но как их урезать я не понял. Должен быть SQL запрос типа такого
USE ″DBName″
ALTER DATABASE ″DBName″ SET RECOVERY SIMPLE
DBCC SHRINKFILE (″DBName″, ″Размер до которого урезать лог″);
ALTER DATABASE ″DBName″ SET RECOVERY FULL
Или я чего то не понял и это может только навредить? как у вас в компаниях настроено?
- Изменено Razor1212007 19 ноября 2018 г. 12:48
Вы принципиально не понимаете необходимость и методы управления журналом транзакций. У меня журнал транзакций занимает объем порядка 40GB в нормальном режиме работы базы. И я его не урезаю, т.к. он все равно у вас вырастет до такого объема, если под нагрузкой имеет такой объем.
Идея иметь большой файл — сокращение операций ввода/вывода, которые потребуются сделать сиквелу при приращении файла логов. Операции ввода/вывода априори медленные.
поэтому, если вы используете модель восстановления Full, выполняйте регулярное резервное копирование журнала транзакций, смиритесь с его объемом и не делайте shrink. В противовес к этому, вы получаете возможность восстановления на максимально близкий к падению момент времени.
Если вы все таки хотите урезать логи, то уйдите на Simple и откатывайтесь на последний полный бэкап.
Innovation distinguishes between a leader and a follower — Steve Jobs
- Помечено в качестве ответа Razor1212007 20 ноября 2018 г. 11:41
Вы принципиально не понимаете необходимость и методы управления журналом транзакций. У меня журнал транзакций занимает объем порядка 40GB в нормальном режиме работы базы. И я его не урезаю, т.к. он все равно у вас вырастет до такого объема, если под нагрузкой имеет такой объем.
Идея иметь большой файл — сокращение операций ввода/вывода, которые потребуются сделать сиквелу при приращении файла логов. Операции ввода/вывода априори медленные.
поэтому, если вы используете модель восстановления Full, выполняйте регулярное резервное копирование журнала транзакций, смиритесь с его объемом и не делайте shrink. В противовес к этому, вы получаете возможность восстановления на максимально близкий к падению момент времени.
Если вы все таки хотите урезать логи, то уйдите на Simple и откатывайтесь на последний полный бэкап.
Innovation distinguishes between a leader and a follower — Steve Jobs
Прекрасно я понимаю что журнал транзакции нужен для того что бы откатиться на максимально последний момент времени. Мне непонятно когда у него будет свой предел
У вас он 40 Гб, у кого-то 200 и продолжает расти. Получается из ваших слов вы с ним ничего не делаете, он достиг своего предела, возможно у вас не вноситься столько информации как у нас?
How do you clear the SQL Server transaction log?
I’m not a SQL expert, and I’m reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log?
21 Answers 21
Making a log file smaller should really be reserved for scenarios where it encountered unexpected growth which you do not expect to happen again. If the log file will grow to the same size again, not very much is accomplished by shrinking it temporarily. Now, depending on the recovery goals of your database, these are the actions you should take.
First, take a full backup
Never make any changes to your database without ensuring you can restore it should something go wrong.
If you care about point-in-time recovery
(And by point-in-time recovery, I mean you care about being able to restore to anything other than a full or differential backup.)
Presumably your database is in FULL recovery mode. If not, then make sure it is:
Even if you are taking regular full backups, the log file will grow and grow until you perform a log backup — this is for your protection, not to needlessly eat away at your disk space. You should be performing these log backups quite frequently, according to your recovery objectives. For example, if you have a business rule that states you can afford to lose no more than 15 minutes of data in the event of a disaster, you should have a job that backs up the log every 15 minutes. Here is a script that will generate timestamped file names based on the current time (but you can also do this with maintenance plans etc., just don’t choose any of the shrink options in maintenance plans, they’re awful).
Note that \\backup_share\ should be on a different machine that represents a different underlying storage device. Backing these up to the same machine (or to a different machine that uses the same underlying disks, or a different VM that’s on the same physical host) does not really help you, since if the machine blows up, you’ve lost your database and its backups. Depending on your network infrastructure it may make more sense to backup locally and then transfer them to a different location behind the scenes; in either case, you want to get them off the primary database machine as quickly as possible.
Now, once you have regular log backups running, it should be reasonable to shrink the log file to something more reasonable than whatever it’s blown up to now. This does not mean running SHRINKFILE over and over again until the log file is 1 MB — even if you are backing up the log frequently, it still needs to accommodate the sum of any concurrent transactions that can occur. Log file autogrow events are expensive, since SQL Server has to zero out the files (unlike data files when instant file initialization is enabled), and user transactions have to wait while this happens. You want to do this grow-shrink-grow-shrink routine as little as possible, and you certainly don’t want to make your users pay for it.
Note that you may need to back up the log twice before a shrink is possible (thanks Robert).
So, you need to come up with a practical size for your log file. Nobody here can tell you what that is without knowing a lot more about your system, but if you’ve been frequently shrinking the log file and it has been growing again, a good watermark is probably 10-50% higher than the largest it’s been. Let’s say that comes to 200 MB, and you want any subsequent autogrowth events to be 50 MB, then you can adjust the log file size this way:
Note that if the log file is currently > 200 MB, you may need to run this first:
If you don’t care about point-in-time recovery
If this is a test database, and you don’t care about point-in-time recovery, then you should make sure that your database is in SIMPLE recovery mode.
Putting the database in SIMPLE recovery mode will make sure that SQL Server re-uses portions of the log file (essentially phasing out inactive transactions) instead of growing to keep a record of all transactions (like FULL recovery does until you back up the log). CHECKPOINT events will help control the log and make sure that it doesn’t need to grow unless you generate a lot of t-log activity between CHECKPOINT s.
Next, you should make absolute sure that this log growth was truly due to an abnormal event (say, an annual spring cleaning or rebuilding your biggest indexes), and not due to normal, everyday usage. If you shrink the log file to a ridiculously small size, and SQL Server just has to grow it again to accommodate your normal activity, what did you gain? Were you able to make use of that disk space you freed up only temporarily? If you need an immediate fix, then you can run the following:
Otherwise, set an appropriate size and growth rate. As per the example in the point-in-time recovery case, you can use the same code and logic to determine what file size is appropriate and set reasonable autogrowth parameters.
Some things you don’t want to do
Back up the log with TRUNCATE_ONLY option and then SHRINKFILE . For one, this TRUNCATE_ONLY option has been deprecated and is no longer available in current versions of SQL Server. Second, if you are in FULL recovery model, this will destroy your log chain and require a new, full backup.
Detach the database, delete the log file, and re-attach. I can’t emphasize how dangerous this can be. Your database may not come back up, it may come up as suspect, you may have to revert to a backup (if you have one), etc. etc.
Use the «shrink database» option. DBCC SHRINKDATABASE and the maintenance plan option to do the same are bad ideas, especially if you really only need to resolve a log problem issue. Target the file you want to adjust and adjust it independently, using DBCC SHRINKFILE or ALTER DATABASE . MODIFY FILE (examples above).
Shrink the log file to 1 MB. This looks tempting because, hey, SQL Server will let me do it in certain scenarios, and look at all the space it frees! Unless your database is read only (and it is, you should mark it as such using ALTER DATABASE ), this will absolutely just lead to many unnecessary growth events, as the log has to accommodate current transactions regardless of the recovery model. What is the point of freeing up that space temporarily, just so SQL Server can take it back slowly and painfully?
Create a second log file. This will provide temporarily relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid. You should deal with the problematic log file directly instead of just adding another potential problem. Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you (unlike a second data file), since only one of the files can ever be used at a time. Paul Randal also explains why multiple log files can bite you later.
Be proactive
Instead of shrinking your log file to some small amount and letting it constantly autogrow at a small rate on its own, set it to some reasonably large size (one that will accommodate the sum of your largest set of concurrent transactions) and set a reasonable autogrow setting as a fallback, so that it doesn’t have to grow multiple times to satisfy single transactions and so that it will be relatively rare for it to ever have to grow during normal business operations.
The worst possible settings here are 1 MB growth or 10% growth. Funny enough, these are the defaults for SQL Server (which I’ve complained about and asked for changes to no avail) — 1 MB for data files, and 10% for log files. The former is much too small in this day and age, and the latter leads to longer and longer events every time (say, your log file is 500 MB, first growth is 50 MB, next growth is 55 MB, next growth is 60.5 MB, etc. etc. — and on slow I/O, believe me, you will really notice this curve).
Further reading
Please don’t stop here; while much of the advice you see out there about shrinking log files is inherently bad and even potentially disastrous, there are some people who care more about data integrity than freeing up disk space.
How to Clear Transaction Log in SQL Server Database
In some cases, the Microsoft SQL Server Database Transaction Log (.LDF) file becomes very huge.
It’s wasting a lot of disk space and causing some problems if you want to backup and restore the database.
We can delete the log file and create a new log file with the minimum size.
To delete log files, follow the steps given below:
- Backup the database
- Detach the database
Right-click on the database => Tasks => Detach
3. Delete or rename the big log file (path:C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA)