Меню Закрыть

Сжать журнал транзакций sql

Содержание

В MS SQL очистка журнала транзакций необходима в том случае, если настроена полная модель восстановления базы данных. Если журнал транзакций переполнился, то ваша база данных откажется работать и будет выдавать ошибку: «журнал транзакций для базы данных заполнен». Почему такое происходит и как этого избежать? Рассмотрим два решения, которые помогут быстро устранить ошибку и продолжить работу с базой.

Увеличиваем размер журнала транзакций.

Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт [Файлы].

Для типа файла «Журнал» увеличиваем максимальный размера файла для авторасширения.

Сжимаем файл журнала транзакций.

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

Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт [Параметры]. Модель восстановления выбираем «Простая» и нажимаем ОК.

Далее правой клавишей мышки по базе и выбираем из контекстного меню [Задачи] — [Сжать] — [Файлы]

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

Теперь нужно вернуться в свойства базы к пункту [Параметры] и переключить модель восстановления на «Полная».

2 thoughts on “ MS SQL очистка журнала транзакций ”

А что делать, если файл журнала уже переполнен и свободное место ушло аж в минус (0%)?

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

В этой статье рассказывается о мониторинге размера журнала транзакций SQL Server SQL Server , сжатии журнала транзакций, добавлении или увеличении файла журнала транзакций, оптимизации скорости роста журнала транзакций tempdb, а также об управлении размером файла журнала транзакций. This topic covers how to monitor SQL Server SQL Server transaction log size, shrink the transaction log, add to or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file.

Мониторинг используемого пространства журнала Monitor log space use

Для мониторинга используемого пространства журнала используйте sys.dm_db_log_space_usage. Monitor log space use by using sys.dm_db_log_space_usage. Это динамическое административное представление возвращает сведения об используемом сейчас журналом объеме пространства и сообщает, когда журнал транзакций требует усечения. This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation.

Для получения сведений о текущем размере файла журнала, его максимальном размере и параметре автоматического увеличения файла вы можете также использовать столбцы size, max_size и growth для данного файла журнала в представлении sys.database_files. For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files.

Избегайте переполнения содержащего журналы диска. Avoid overloading the log disk. Хранилище журналов должно отвечать требованиям к числу операций ввода-вывода в секунду и низкой задержке для транзакционной нагрузки. Ensure the log storage can withstand the IOPS and low latency requirements for your transactional load.

Уменьшение размера файла журнала Shrink log file size

Для уменьшения реального размера физического файла журнала необходимо выполнить его сжатие. To reduce the physical size of a physical log file, you must shrink the log file. Это полезно, если файл журнала транзакций содержит неиспользованное пространство. This is useful when you know that a transaction log file contains unused space. Вы можете сжать файл журнала, только если база данных активна и хотя бы один виртуальный файл журнала (VLF) свободен. You can shrink a log file only while the database is online, and at least one virtual log file (VLF) is free. В ряде случаев сжатие невозможно до тех пор, пока не выполнена следующая операция усечения журнала. In some cases, shrinking the log may not be possible until after the next log truncation.

Такие факторы, как долго выполняемые транзакции, из-за которых виртуальные файлы журналов длительное время остаются в активном состоянии, могут ограничить или вовсе не допустить возможность сжатия журнала. Factors such as a long-running transaction, that keep VLFs active for an extended period, can restrict log shrinkage or even prevent the log from shrinking at all. Дополнительные сведения см. в разделе Факторы, которые могут вызвать задержку усечения журнала. For information, see Factors that can delay log truncation.

Сжатие файла журнала удаляет виртуальные файлы журнала, которые не содержат частей логического журнала (то есть, неактивные виртуальные файлы журнала). Shrinking a log file removes one or more VLFs that hold no part of the logical log (that is, inactive VLFs). При сжатии файла журнала транзакций неактивные виртуальные файлы журнала в конце удаляются, чтобы журнал уменьшился приблизительно до целевого размера. When a transaction log file is shrunk, inactive VLFs are removed from the end of the log file to reduce the log to approximately the target size.

Перед сжатием следует учесть факторы, которые могут вызвать задержку усечения журнала. Before shrinking the transaction log, keep in mind Factors that can delay log truncation. Если после сжатия журнала снова потребуется дисковое пространство, размер журнала транзакций снова будет увеличиваться, что повлияет на производительность во время операций увеличения. If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log growth operations. Дополнительные сведения см. в разделе Рекомендации этой статьи. For more information, see the Recommendations in this topic.

Сжатие файла журнала (без сжатия файлов базы данных) Shrink a log file (without shrinking database files)

Мониторинг событий сжатия файла журнала Monitor log-file shrink events

Мониторинг пространства журнала Monitor log space

sys.database_files (Transact-SQL) (См. столбцы size, max_size и growth файла или файлов журнала.) sys.database_files (Transact-SQL) (See the size, max_size, and growth columns for the log file or files.)

Читайте также:  1993 Интел что создали

Добавление или увеличение размера файла журнала Add or enlarge a log file

Вы можете выделить дополнительное место на диске, увеличив существующий файл журнала (если для этого достаточно места на диске) либо добавив файл журнала в базу данных, как правило, на другом диске. You can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk. До тех пор, пока в журнале и на содержащем его дисковом томе достаточно свободного места, будет достаточного одного файла журнала транзакций. One transaction log file is sufficient unless log space is running out, and disk space is also running out on the volume that holds the log file.

  • Чтобы добавить файл журнала в базу данных, используйте предложение ADD LOG FILE инструкции ALTER DATABASE . To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. Это позволяет увеличить размер файла. Adding a log file allows the log to grow.
  • Чтобы увеличить размер файла журнала, используйте предложение MODIFY FILE инструкции ALTER DATABASE с указанием синтаксиса SIZE и MAXSIZE . To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. Дополнительные сведения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп. For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup options.

Дополнительные сведения см. в разделе Рекомендации этой статьи. For more information, see the Recommendations in this topic.

Оптимизация размера журнала транзакций tempdb Optimize tempdb transaction log size

При перезапуске экземпляра сервера размер журнала транзакций базы данных tempdb изменяется и становится равным исходному размеру, который был до применения параметра автоматического увеличения файла. Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. Это может понизить производительность журнала транзакций базы данных tempdb . This can reduce the performance of the tempdb transaction log.

Этого можно избежать с помощью увеличения размера журнала транзакций базы данных tempdb после запуска или перезапуска экземпляра сервера. You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. Дополнительные сведения см. в статье tempdb Database. For more information, see tempdb Database.

Управление увеличением размера файла журнала транзакций Control transaction log file growth

Для управления увеличением файла журнала транзакций используйте инструкцию ALTER DATABASE (Transact-SQL) с параметрами для файлов и файловых групп. Use the ALTER DATABASE (Transact-SQL) File and Filegroup options statement to manage the growth of a transaction log file. Следует отметить следующее. Note the following:

  • Чтобы изменить текущий размер файла в КБ, МБ, ГБ и ТБ, используйте параметр SIZE . To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
  • Чтобы изменить шаг приращения размера, используйте параметр FILEGROWTH . To change the growth increment, use the FILEGROWTH option. Значение 0 указывает, что автоматическое приращение выключено и дополнительное пространство для файла не разрешено. A value of 0 indicates that automatic growth is set to off and no additional space is permitted.
  • Чтобы установить максимальный размер файла журнала в КБ, МБ, ГБ и ТБ или задать неограниченный размер (UNLIMITED), используйте параметр MAXSIZE . To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option.

Дополнительные сведения см. в разделе Рекомендации этой статьи. For more information, see the Recommendations in this topic.

Рекомендации Recommendations

Далее приведены некоторые общие рекомендации по работе с файлами журналов транзакций. Following are some general recommendations when you are working with transaction log files:

Шаг приращения автоматического увеличения журнала транзакций, задаваемый параметром FILEGROWTH , должен быть достаточно большим, чтобы с запасом соответствовать потребностям транзакций рабочих нагрузок. The automatic growth (autogrow) increment of the transaction log, as set by the FILEGROWTH option, must be large enough to stay ahead of the needs of the workload transactions. Во избежание слишком частых увеличений размера файла журнала следует задать достаточно большое значение шагу роста файла журнала. The file growth increment on a log file should be sufficiently large to avoid frequent expansion. Чтобы подбирать оптимальный размер журнала транзакций, рекомендуем отслеживать объем журнала, занимаемый в следующих случаях. A good pointer to properly size a transaction log is monitoring the amount of log occupied during:

  • Во время, необходимое для выполнения полного резервного копирования, так как резервные копии журнала создаются только после его завершения. The time required to execute a full backup, because log backups cannot occur until it finishes.
  • Во время, необходимое для самых продолжительных операций обслуживания индекса. The time required for the largest index maintenance operations.
  • Во время, необходимое для выполнения наибольшего пакета в базе данных. The time required to execute the largest batch in a database.

При активации autogrow для файлов журналов и данных с помощью параметра FILEGROWTH может быть лучше задать рост журнала через размер (size), а не процент (percentage). Это позволит более эффективно контролировать увеличение, так как процент будет характеризовать постоянно растущую величину. When setting autogrow for data and log files using the FILEGROWTH option, it might be preferred to set it in size instead of percentage, to allow better control on the growth ratio, as percentage is an ever-growing amount.

  • Учитывайте, что журналы транзакций не могут использовать мгновенную инициализацию файлов, поэтому особо продолжительное время их роста имеет критическую важность. Keep in mind that transaction logs cannot leverage Instant File Initialization, so extended log growth times are especially critical.
  • Рекомендуется не устанавливать для журналов транзакций значение параметра FILEGROWTH выше 1024 МБ. As a best practice, do not set the FILEGROWTH option value above 1,024 MB for transaction logs. Значения для параметра FILEGROWTH по умолчанию. The default values for FILEGROWTH option are:
Читайте также:  Как подключить bluetooth клавиатуру к компьютеру
Версия Version Значения по умолчанию Default values
Начиная с SQL Server 2016 (13.x) SQL Server 2016 (13.x) Starting with SQL Server 2016 (13.x) SQL Server 2016 (13.x) Данные — 64 МБ. Data 64 MB. Файлы журналов — 64 МБ. Log files 64 MB.
Начиная с SQL Server 2005 (9.x) SQL Server 2005 (9.x) Starting with SQL Server 2005 (9.x) SQL Server 2005 (9.x) Данные — 1 МБ. Data 1 MB. Файлы журналов — 10 %. Log files 10%.
До SQL Server 2005 (9.x) SQL Server 2005 (9.x) Prior to SQL Server 2005 (9.x) SQL Server 2005 (9.x) Данные — 10 %. Data 10%. Файлы журналов — 10 %. Log files 10%.

При небольшом шаге приращения может формироваться слишком много виртуальных файлов журнала малого размера и снижаться производительность. A small growth increment can generate too many small VLFs and can reduce performance. Чтобы определить оптимальное распределение виртуальных файлов журнала для текущего размера журнала транзакций всех баз данных в определенном экземпляре, а также требуемые приращения для достижения нужного размера, см. следующий скрипт. To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

При большом шаге приращения может формироваться слишком мало крупных виртуальных файлов журнала, что также повлияет на производительность. A large growth increment can generate too few and large VLFs and can also affect performance. Чтобы определить оптимальное распределение виртуальных файлов журнала для текущего размера журнала транзакций всех баз данных в определенном экземпляре, а также требуемые приращения для достижения нужного размера, см. следующий скрипт. To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

Даже если включено автоматическое увеличение, вы можете получить сообщение, что журнал транзакций заполнен, если его размер не может достаточно быстро увеличиваться под нужды вашего запроса. Even with autogrow enabled, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query. Дополнительные сведения об изменении шага приращения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп For more information on changing the growth increment, see ALTER DATABASE (Transact-SQL) File and Filegroup options

Наличие множества файлов журнала в базе данных не способствует повышению производительности, так как файлы журнала транзакций не используют пропорциональное заполнение, как файлы данных в одной файловой группе. Having multiple log files in a database does not enhance performance in any way, because the transaction log files do not use proportional fill like data files in a same filegroup.

Вы можете настроить автоматическое сжатие файлов журналов. Log files can be set to shrink automatically. Но делать это не рекомендуется, и параметру базы данных auto_shrink по умолчанию задано значение FALSE. However this is not recommended, and the auto_shrink database property is set to FALSE by default. Если параметру auto_shrink задано значение TRUE, автоматическое сжатие уменьшает размер файла, только если в нем не использовано более 25 % объема. If auto_shrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused.

  • Файл будет сжат либо до размера, в котором 25 % пространства не используется, либо до исходного размера, каким бы большим он ни был. The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger.
  • Сведения об изменении свойства auto_shrink см. в разделах Просмотр или изменение свойств базы данных и Параметры ALTER DATABASE SET (Transact-SQL). For information about changing the setting of the auto_shrink property, see View or Change the Properties of a Database and ALTER DATABASE SET Options (Transact-SQL).

Многие администраторы Microsoft SQL Server сталкивались с проблемой значительного увеличения физического размера базы данных и файлов журнала транзакций и, конечно же, им хотелось бы каким-то образом уменьшить этот размер, для того чтобы не предпринимать какие-либо действия, связанные с увеличением свободного пространства на жестком диске. Способ уменьшить физический размер базы данных и файлов журнала транзакций в SQL сервере есть – это сжатие.

Что такое сжатие в Microsoft SQL Server?

Сжатие — это процесс удаления неиспользуемого пространства в файлах базы данных и журнала транзакций.

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

Наибольший эффект от сжатия достигается тогда, когда операция сжатия выполняется после операции удаления таблиц из БД или удаления данных из таблиц.

Следует отличать процедуру сжатия журнала транзакций от процедуры усечения журнала транзакций. Сжатие — это уменьшение физического размера журнала за счет удаления неиспользуемого пространства, а усечение – это освобождение места в логическом журнале для повторного использования (т.е. образуется неиспользуемое пространство) журналом транзакций при этом размер физического файла не уменьшается.

Усечение журнала транзакций происходит автоматически:

  • В простой модели восстановления — после достижения контрольной точки, которая может возникнуть, например, после создания BACKUP базы данных, при явном выполнении инструкции CHECKPOINT, или тогда когда размер логического журнала транзакций заполняется на 70 процентов, во всех этих случаях происходит автоматическая очистка неактивной части журнала, т.е. его усечение;
  • В модели полного восстановления или в модели восстановления с неполным протоколированием — после создания резервной копии журнала при условии, что с момента создания последней резервной копии журнала была достигнута контрольная точка.

Если Вы используете модель полного восстановления или в модель восстановления с неполным протоколированием и у Вас файлы журнала транзакций слишком велики, то скорей всего Вы достаточно долго не делали BACKUP (резервную копию) журнала транзакций. В данном случае Вам необходимо сделать сначала BACKUP журнала транзакций, а затем выполнить сжатие журнала транзакций, которое мы как раз и рассмотрим чуть ниже.

Читайте также:  Слова только из гласных букв

Также возможно размер файлов журнала транзакций слишком большой (как при простой, так и при полной модели восстановления) за счет задержки процедуры усечения, т.е. размер журнала, состоит в основном из активной части журнала, а активную часть усечь нельзя, поэтому физический размер журнала растет. На задержку процедуры усечения влияют такие факторы как: активные длительные транзакции, некоторые сценарии отображения зеркальных баз данных и журнала транзакций, некоторые сценарии при репликации транзакций и журнала транзакций, а также усечение журнала невозможно во время операций резервного копирования и восстановления данных. В данном случае Вам нужно устранить причины задержки, затем сделать усечение (т.е. например, для полной модели восстановления BACKUP журнала), а затем сжатие до приемлемых размеров.

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

Как сжать базу данных в MS SQL Server?

Сжать файлы базы данных и журнала транзакций можно и с помощью графического интерфейса Management Studio и с помощью инструкций Transact-SQL: DBCC SHRINKDATABASE и DBCC SHRINKFILE. Также возможно настроить базу данных на автоматическое сжатие путем выставления параметра БД AUTO_SHRINK в значение ON.

Примечание! Сжатие базы данных я буду рассматривать на примере Microsoft SQL Server 2016 Express.

Сжимаем базу данных с помощью среды Management Studio

Запускаем Management Studio и в обозревателе объектов открываем объект «Базы данных». Затем щелкаем правой кнопкой мыши по БД, которую необходимо сжать, далее выбираем «Задачи ->Сжать -> База данных (или Файлы, если, например, нужно сжать только журнал транзакций)». Я для примера выбираю «База данных».

В итоге у Вас откроется окно «Сжатие базы данных», в котором Вы, кстати, можете наблюдать размер базы данных, а также доступное свободное место, которое можно удалить (т.е. сжать). Нажимаем «ОК».

Через некоторое время, в зависимости от размера базы данных, сжатие будет завершено.

Сжимаем базу данных с помощью инструкций SHRINKDATABASE и SHRINKFILE

В MS SQL Server для выполнения сжатия файлов базы данных и журнала транзакций существуют две инструкции SHRINKDATABASE и SHRINKFILE.

  • DBCC SHRINKDATABASE – это команда для сжатия базы данных;
  • DBCC SHRINKFILE – с помощью данной команды можно выполнить сжатие некоторых файлов базы данных (например, только журнала транзакций).

Для того чтобы выполнить сжатие БД (например, TestBase) точно также как мы это сделали чуть ранее в Management Studio, выполните следующую инструкцию.

SHRINKDATABASE имеет следующие параметры:

    database_name или database_ >Синтаксис SHRINKDATABASE

Для того чтобы сжать только журнал транзакций можно использовать инструкцию SHRINKFILE, например.

В данном случае мы осуществим сжатие файла журнала (TestBase_log – это название файла журнала транзакций), до его начального значения, т.е. до значения по умолчанию. Для того чтобы сжать файл до определенного размера, укажите вторым параметром размер в мегабайтах. Например, следующей инструкцией мы уменьшим размер файла журнала транзакций до 5 мегабайт.

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

SHRINKFILE также имеет параметры NOTRUNCATE и TRUNCATEONLY.

Синтаксис SHRINKFILE

Рекомендации и важные моменты при сжатии базы данных

  • Операция сжатия базы данных может вызвать фрагментацию индексов и замедлить работу БД. Поэтому слишком часто не рекомендуется выполнять сжатие базы данных;
  • Сжимать БД лучше до операции перестроения индексов, т.е. после сжатия запустите процедуру перестроения индексов;
  • Параметр базы данных AUTO_SHRINK (автоматическое сжатие) лучше не выставлять в значение ON, а оставлять по умолчанию, т.е. в OFF, если конечно у Вас нет на это достаточно серьезных оснований;
  • Инструкция SHRINKDATABASE не позволяет уменьшить размер базы данных до размера, который меньше начального, т.е. минимального. Однако инструкция SHRINKFILE сделать это может (вторым параметром указываем размер меньше минимального). Минимальный размер базы данных — это размер, который указан при создании базы данных или явно установленный операцией изменения размера БД, такой как DBCC SHRINKFILE или ALTER DATABASE. Например, если база данных была создана с размером 10 мегабайт, потом увеличилась до 100 мегабайт, ее можно сжать с помощью SHRINKDATABASE только до начальных 10 мегабайт, даже если все данные были удалены из базы данных;
  • Сжимать файлы базы данных и журнала транзакций нельзя, когда идет процесс их резервирования. И наоборот, создавать резервные копии базы и журнала транзакций нельзя пока идет процесс их сжатия;
  • Выполнение инструкции DBCC SHRINKDATABASE без указания параметра NOTRUNCATE или TRUNCATEONLY равносильно выполнению инструкции DBCC SHRINKDATABASE с параметром NOTRUNCATE после выполнения инструкции DBCC SHRINKDATABASE с параметром TRUNCATEONLY;
  • В процессе сжатия базы данных пользователи могут работать в ней (т.е. переводить БД в однопользовательский режим не нужно);
  • В любой момент времени Вы можете прервать процесс выполнения операций SHRINKDATABASE и SHRINKFILE, при этом вся выполненная работа сохраняется;
  • Перед запуском процедуры сжатия проверьте, есть ли свободное пространство для удаления в файлах базы данных, т.е. можно ли вообще сжать файлы, выполнив следующий запрос (он покажет в мегабайтах, на сколько Вы можете уменьшить файлы БД).

  • Для того чтобы выполнить процедуру сжатия БД необходимо быть членом группы роли сервера sysadmin или роли базы данных db_owner;
  • Сжатие файлов базы данных и журнала транзакций достаточно ресурсоемкий процесс, требующий определенного количества времени (в зависимости от размера файлов), поэтому данную процедуру необходимо планировать и вообще выполнять ее только в случае крайней необходимости (например, размер БД и журнала стал слишком велик и больше половины отдельно взятого файла занимает неиспользуемое пространство).
  • На этом у меня все, надеюсь, статья была Вам полезна, удачи!

    Рекомендуем к прочтению

    Добавить комментарий

    Ваш адрес email не будет опубликован.