Как урезать транзакционные логи в 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
Как через SQL 2016 в планировщике сжать и почистить log.ldf (лог транзакций) файл от базы данных?
Всем привет.
Из за большого размера файла log.ldf, который стал примерно 150Gb и является историей транзакция базы данных SQL, возникла необходимость его уменьшить
Файл позволяет вернуться к любой точке времени и восстановить базу на указанное время.
Т.к. настроены полные бэкапы в планировщике и достаточно часто делаются, то необходимости в этом журнале особо нет, хотя было бы не плохо иметь всегда месячную версию.
Подскажите как через планировщик настроить сжатие и очистку этого файла после успешно сделанного бэкапа?
- Вопрос задан более трёх лет назад
- 3177 просмотров
- Вконтакте
- Вконтакте
Этот файл не позволяет этого сделать. Правильно настроенное резервное копирование, включающее в себя резервные копии журнала транзакций позволяет восстановить БД на любой (с оговорками) момент времени.
Полный бэкап не влияет усечение журнала транзакций. Если ваша база в полной модели восстановления (что, видимо, так), и вы делаете только полные резервные копии — ваш журнал транзакций будет только расти, сколько бы шринков (DBCC SHRINKDATABASE/SHRINKFILE) вы не делали.
Грамотный подход для того, чтобы не страдать от разрастания журнала транзакций: почитать про модели восстановления; виды резервных копий (включая резервные копии журнала транзакций); настроить резервное копирование журнала транзакций с такой частотой, которая обеспечит оптимальные для вас: размер файла журнала транзакций и объём допустимой потери данных; разово обрезать журнал транзакций с помощью DBCC SHRINKFILE.
Быстрый подход: перевести бд в простую модель восстановления (alter database set recovery simple), выполнить инструкцию из первого ответа и забыть про рост журнала транзакций и восстановление на момент времени.
MS SQL очистка журнала транзакций
В MS SQL очистка журнала транзакций необходима в том случае, если настроена полная модель восстановления базы данных. Если журнал транзакций переполнился, то ваша база данных откажется работать и будет выдавать ошибку: «журнал транзакций для базы данных заполнен». Почему такое происходит и как этого избежать? Рассмотрим два решения, которые помогут быстро устранить ошибку и продолжить работу с базой.
Увеличиваем размер журнала транзакций.
Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт [Файлы].
Для типа файла «Журнал» увеличиваем максимальный размера файла для авторасширения.
Сжимаем файл журнала транзакций.
Для сжатия журнала транзакций необходимо модель восстановления базы смнеить на простую, выполнить сжатие журнала, после чего модель восстановления переключить обратно на полную.
Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт [Параметры]. Модель восстановления выбираем «Простая» и нажимаем ОК.
Далее правой клавишей мышки по базе и выбираем из контекстного меню [Задачи] — [Сжать] — [Файлы]
Тип сжатия: Журнал
Операция сжатия: Реорганизовать файлы, перед тем как освободить неиспользуемое место
И указываем размер до которого необходимо сжать, например 0.
Теперь нужно вернуться в свойства базы к пункту [Параметры] и переключить модель восстановления на «Полная».
2 thoughts on “ MS SQL очистка журнала транзакций ”
А что делать, если файл журнала уже переполнен и свободное место ушло аж в минус (0%)?
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.