Ms sql не сжимается файл данных

Обновлено: 04.07.2024

Размер лога 8Гб. Свободное место в файле 98%. Первая операция сжатия выдала что все успешно сжато, файл остался как был. Вторая и последующие уже ничего не пишут, просто молча отрабатывают и ничего не меняется

Если выполнять скриптом, то пишут что

Не удалось сжать файл журнала 2 (КА_log), так как все логические файлы журналов, расположенные в конце файла, находятся в использовании

sp_dboption 'ИмяБазы', 'trunc. log on chkpt.', 'ON'
пробовал? (0) дык шринкани лог, делов то..опосля бекапа он никак не ужмётся.

(3)
Starting with SQL Server 2000, setting the trunc. log on chkpt. option to true sets the recovery model of the database to SIMPLE. Setting the option to false sets the recovery model to FULL

Спасибо, не надо

(2)
Спасибо! Помогло CHECKPOINT, лог ужался до 130 мб. Дальше не хочет по тем же отмазкам что там типо в конце используемые логические файлы логов USE BaseName
GO
BACKUP LOG BaseName WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE ('BaseName_Log', 1)
GO

(8)
Что изменится через час? В базе никто не работает, открытых транзакций нет, лог бекапится каждые 15 минут, кроме того, я уже написал что помогло CHECKPOINT, и проблема стало быть была именно в этом

>> документацию читать не модно?
А, ну извините, задал вопрос предварительно не прорубив все статьи MSDN по теме, криво переведенные на русский при этом. Я правда так и не понял чего там в приведенной вами ссылке имеет отношение к моему вопросу

(9)
WITH TRUNCATE_ONLY не поддерживается с 2008, да и бекап с усечением, как я писал в исходном посте сделан был (10) не нравится русский перевод, читай по англицки, кто мешает?
по ссылке написано в каком случае лог не может шринкануться меньше определенного размера, твое "Не удалось сжать файл журнала 2 (КА_log), так как все логические файлы журналов, расположенные в конце файла, находятся в использовании" из (1) разжевано по самое не могу, с картинками.
чекпойнт в полной модели восстановления помочь не мог, ты делал что-то кроме него

(13)
>> чекпойнт в полной модели восстановления помочь не мог, ты делал что-то кроме него

Мог, дружище, мог. Если б не мог, то не помогло бы, учи матан

(10) Как раз по вопросам журналов транзакций, модели восстановления, резервного копирования в MSDN практически идеально и очень аккуратно переведено.

(0) Самое простое средство для однократного (но не для регулярного) уменьшения "нечаянно" выросшего лога транзакций: прочтитать документацию, сделать резервную копию базы, перевести в SIMPLE, шринкнуть файл лога в 0, увеличить файл лога до 0,25-0,5 размера базы, включить FULL, следать резервную копию, настроить резервное копирование регулярное для данных и для журнала транзакций, забыть об этой проблеме.

(14) лол. а как, если не секрет?
и заодно посчитай сколько бэкапов журнала транзакций (если верить (10)) было между (0) и чудесным "исцелением" в (7) и подумай - не было ли это как-то связано

(16)
Дружище, бекапов было. дай подумать. это. ноль? да, где-то в районе того

При использовании MS SQL появляется проблема, когда размеры расположенных баз данных на физическом носителе увеличиваются до огромных объемов.

Одно из решений — это покупка нового жесткого диска с большим объемом памяти. Но тот же самый MS SQL Server предлагает более экономичное решение (бесплатное) — свои собственные функции (как сжатие). Ниже представлены четыре основных метода по решению данной проблемы.

Метод 1: Использование SQL Server Management Studio

Шаг 1: Правая кнопка мыши по названию БД → Задачи (Tasks) → Сжать (Shrink) → База данных (Database)

Шаг 2: Нажимаем на «ОК»

Готово. Мы видим, что доступное свободное место можно освободить (сжать) на 0.69 МВ (11%).

Метод 2: Использование Transact SQL Command

Метод 1: Использование SQL Server Management Studio

Шаг 1: Открываем наш SQL Server Management Studio

Шаг 2: Подключаемся к необходимой Базе данных

Шаг 3: Нажимаем на «Создать запрос» (New Query)

Шаг 4: После чего в открывшемся окне прописываем соответствующую команду (ниже) и жмем кнопку «Выполнить» (Execute)

Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)

Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:

Хранит тип данных CHAR (фиксированной длины), так чтобы система думала, что они являются типами данными, которые имеют переменную длину,

Не применяет сохранение данных, если значения являются 0 и NULL

Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.

Осуществим сжатие по строкам.

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=PAGE)

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

Данное сжатие позволяет максимизировать кол-во строк, которые хранятся на странице,

Повторы данных заменяются ссылками, если происходит сжатие по префиксу.

Пример: используем ту же самую таблицу на 14 500 строк.

Осуществим сжатие по страницам.

Результат: занимаемое пространство данными уменьшилось до 2МВ.

Различия между сжатием на уровне страниц и строк

Если кратко резюмировать выше описанные способы, то главное различие между 3 и 4 способом – это данные которые используются в самой базе данных.

Если вам известно, что БД использует огромное количество повторяющихся значений, то лучше использовать «Сжатие на уровне страниц» (Метод 4), т.к. система хранит ссылки на эти значения, а не дублирует данные. В остальных случаях лучше использовать «Сжатие на уровне рядов» (Метод 3). Первые 2 метода используются по желанию.

Негативные факторы при использовании сжатия:

Частое сжатие Базы Данных не рекомендуется, т.к. сжатие приводит к фрагментации таблиц.

Размер базы данных никаким образом нельзя сделать меньше, чем минимальный размер этой БД. Пример: если базу данных создали с размером 5 МВ и она увеличилась до 50 МВ, то ее можно сжать только до изначального созданного размера в 5МВ (даже с пустыми столбцами и строками).

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

Сжатие таблицы в MS SQL позволяет существенно сэкономить дисковое пространство. Помимо экономии места, повышается производительность запросов, т.к. уменьшается количество обрабатываемых строк. При правильном выборе метода, мы можем увидеть значительное освобождение места для записи новых данных. Таблица на 14 500 строк это доказала (уменьшение размера в 2 и в 5 раз).

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

Скриншот 1

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

Скриншот 2

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

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

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

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

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

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

Синтаксис 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, при этом вся выполненная работа сохраняется;
  • Перед запуском процедуры сжатия проверьте, есть ли свободное пространство для удаления в файлах базы данных, т.е. можно ли вообще сжать файлы, выполнив следующий запрос (он покажет в мегабайтах, на сколько Вы можете уменьшить файлы БД).

Скриншот 3

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

У меня есть база данных SQL с большим количеством неиспользуемого пространства. Однако, когда я бегу Задачи | Термоусадочная пленка | База данных А также Задачи | Термоусадочная пленка | файлы от студии управления она не освобождает место.
К сожалению, на машине, на которой он работает, не хватает места на жестком диске. Любые идеи о том, почему это пространство не освобождает.

введите описание изображения здесь>> </p> <p> Вот неиспользуемый диалог, который появляется, когда я иду на сжатие файлов: <img src tags

Также режим восстановления в БД установлен на простой. sql-server shrink

3 ответа

Я собираюсь сделать догадку и сказать, что ваша база данных настроена на использование ПОЛНОГО восстановления. Вы никогда не делали резервное копирование журнала (не говоря уже о полном), основанном на изображении выше. Если это так, то файл журнала растет и растет, и его невозможно очистить, потому что вы не сделали резервную копию.

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

В худшем случае (и я имею в виду худшее, особенно если это производственная база данных) заключается в том, чтобы установить базу данных в SIMPLE-восстановление, сжать и затем сделать резервную копию. Это должно работать, даже если у вас недостаточно места для усадки.

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

Вы можете пойти здесь для более подробно.

EDIT:

Можете ли вы рассказать нам, какой% бесплатный журнал соответствует файлу данных? Также вы можете посмотреть log_reuse_wait_desc в sys.databases.

И последнее, но, конечно, не в последнюю очередь, вы попробовали вариант TRUNCATEONLY сокращения?

Перед сжатием вам нужно проверить полноту страницы кластеризованного индекса ваших самых больших таблиц

Полнота и фрагментация страницы

[Полнота и фрагментация страницы]

Восстановить кластеризованный индекс

Сделать перестроить [Восстановить кластеризованный индекс]

Сжатие данных: Задачи | Термоусадочная пленка | Файлы в SQL Server Management Studio с «Реорганизовать файлы перед освобождением неиспользуемого пространства»

P.S. Обратите внимание, что после Shrink вы получите сильно фрагментированную базу данных, которая может повлиять на производительность

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

Читайте также: