Как сжать базу данных sql

Сжатие базы данных

Сжатие базы данных – это процесс уменьшения размеров файлов базы данных за счет удаления неиспользуемых частей файла. Существует три способа сжатия базы данных:

автоматическое сжатие при установке соответствующего параметра в настройках базы данных;

удаление свободного пространства из файлов базы данных с помощью утилит администрирования MS SQL Server;

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

Автоматическое сжатие данных выполняется постоянно с определенными интервалами, если установлен параметр базы данных autoshrink. При операциях автоматического сжатия нельзя определить, какую часть базы данных необходимо сжать. MS SQL Server пытается освободить значительную часть базы данных самостоятельно. Эти операции выполняются в период наименьшей активности пользователей.

Сжатие всей базы данных вручную осуществляется с использованием следующей команды:

DBCC SHRINKDATABASE (‘имя_БД’, [‘процент’] [, NOTRUNCATE | TRUNCATEONLY])

имя_БД – имя базы данных, которую необходимо сжать;

процент – количество процентов свободного пространства, которое желательно оставить после сжатия;

NOTRUNCATE – сводное пространство не возвращается операционной системе, а резервируется в файлах, т.е. физически уменьшения размера базы данных не происходит;

TRUNCATEONLY – свободное пространство удаляется за последним используемым в файле экстентом, при этом данные не перемещаются, а параметр процент игнорируется.

Права на сжатие базы данных выданы только членам роли sysadmin и владельцам базы данных. После сжатия базы данных выводится отчет, в котором указывается:

количество страниц, до которых сжимается файл;

расчетное число страниц, в которые могут быть помещены все данные файла;

количество страниц, содержащих данные;

количество страниц, на которые файл может быть еще сжат.

Нельзя сжать базу данных до размера меньше первоначального.

Сжатие базы данных можно осуществить также и путем сжатия каждого ее файла с помощью следующей команды:

DBCC SHRINKFILE (‘имя_файла’, [‘конечный_размер’] [, EMPTYFILE | NOTRUNCATE | TRUNCATEONLY ])

имя_файла – логическое имя файла, который необходимо сжать;

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

EMPTYFILE – выполняется перенос данных из файла в другие файлы файловой группы;

NOTRUNCATE – освободившееся место не возвращается операционной системе, т.е. размер файла не уменьшается на самом деле. При этом данные располагаются более компактно и смещаются к началу файла;

TRUNCATEONLY – происходит обрезание файла, начиная с последней используемой страницы. Никакого перемещения данных не происходит.

Резервное копирование данных

MS SQL Server предлагает следующие типы резервного копирования информации:

полная копия базы данных, которая является отправной точкой при восстановлении базы данных после сбоя, однако в зависимости от объема данных этот процесс может занимать много времени, поэтому не рекомендуется выполнять его слишком часто. Полная копия содержит все данные, содержащиеся в базе данных на момент окончания резервирования;

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

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

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

sp_addumpdevice ‘тип_устройства’, ‘логическое_имя’, ‘физическое_имя’

тип_устройства – тип устройства резервного копирования. Допустимые значения: TAPE (магнитная лента), DISK (магнитный диск);

логическое_имя, физическое_имя – логическое и физическое имя устройства резервного копирования соответственно.

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

DIFFERENTIAL – создается дифференциальная копия базы данных;

EXPIREDATE – определяется дата, после которой резервная копия считается устаревшей и может быть перезаписана;

INIT | NOINIT – система осуществляет или нет инициализацию устройства.

Сжатие, дефрагментация и оптимизация базы данных MariaDB/MySQL

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

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

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

Сжатие и оптимизация таблиц InnoDB

Файлы ibdata1 и ib_log

Большинство проектов с таблицами InnoDB имеют проблемы с большими файлами ibdata1 и ib_log. В большинстве случаев это связано с неправильной конфигурацией MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого само не используется. Я предпочитаю хранить данные таблицы в отдельных файлах ibd*. Для этого добавьте в my.cnf следующую строку:

Если ваш сервер настроен и у вас есть продуктивные базы данных с таблицами InnoDB, сделайте следующее:

Сделайте резервную копию всех баз данных на вашем сервере (кроме mysql и performance_schema). Вы можете получить дамп базы данных с помощью этой команды:
# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
После создания резервной копии базы данных остановите сервер mysql/mariadb;
Измените настройки в my.cfg;
Удалите файлы ibdata1 и ib_log;
Запустите демон mysql/mariadb;
Восстановить все базы из резервной копии:
# mysql -u [username] –p[password] [database_name] < [dump_file.sql]

После этого все таблицы InnoDB будут храниться в отдельных файлах, и ibdata1 перестанет экспоненциально расти.

Сжатие таблиц InnoDB

Вы можете сжимать таблицы с текстовыми данными / данными BLOB и экономить довольно много места на диске.

У меня есть база данных innodb_test, содержащая таблицы, которые потенциально могут быть сжаты, и поэтому я могу освободить место на диске. Прежде чем что-либо делать, я рекомендую сделать резервную копию всех баз данных. Подключитесь к серверу mysql:

Выберите нужную базу данных в консоли mysql:

Чтобы отобразить список таблиц и их размеры, используйте следующий запрос:

Где innodb_test — имя вашей базы данных.

Некоторые таблицы могут быть сжаты. Возьмем для примера таблицу b_crm_event_relations. Запустите этот запрос:

После его запуска вы можете увидеть, что размер таблицы уменьшился с 26 МБ до 11 МБ из-за сжатия.

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

Сжатие таблиц MyISAM в MySQL / MariDB

Для сжатия таблиц Myisam используйте специальный запрос в консоли сервера вместо консоли mysql. Чтобы сжать таблицу, запустите следующее:

Где /var/lib/mysql/test/modx_session — это путь к вашей таблице. К сожалению, у меня не было большой таблицы и пришлось сжимать маленькие, но результат все равно можно было увидеть (файл был сжат с 25 МБ до 18 МБ):

Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records)
— Calculating statistics
— Compressing file
29.84%
Remember to run myisamchk -rq on compressed tables

Я использовал в команде ключ -b. Когда вы добавляете его, таблица создается перед сжатием и помечается меткой OLD:

Оптимизация таблиц и баз данных в MySQL и MariaDB

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

Откройте консоль MySQL, выберите базу данных и выполните этот запрос:

Таким образом, вы отобразите все таблицы с не менее 50 МБ неиспользуемого пространства:

data_length_mb — общий размер стола

data_free_mb — неиспользуемое место в столе

Это таблицы, которые мы можем дефрагментировать. Проверьте, сколько места они занимают на диске:

Чтобы оптимизировать эти таблицы, выполните следующую команду в консоли mysql:

После успешной дефрагментации вы увидите следующий результат:

Как видите, data_free_mb теперь равно 0, а размер таблицы значительно уменьшился (в 3-4 раза).

Вы также можете запустить дефрагментацию, используя mysqlcheck в консоли сервера:

Где innodb_test ваша база данных и b_workflow_file название таблицы.

Чтобы оптимизировать все таблицы в базе данных, запустите эту команду в консоли сервера:

Где innodb_test — имя базы данных

Или запустите оптимизацию всех баз на сервере:

Если вы проверите размер базы данных до и после оптимизации, вы увидите, что общий размер уменьшился:

Таким образом, чтобы сэкономить место на вашем сервере, вы можете время от времени оптимизировать и сжимать свои таблицы и базы данных MySQL/MariDB. Не забудьте создать резервную копию базы данных перед выполнением любой работы по оптимизации.

Сжатие для таблицы sql server express. Сжатие базы данных и журнала транзакций в Microsoft SQL Server. б) Сохраняем БД на сетевом диске

а) Разделение БД на файловые группы
б) Размещение БД или её части на сетевом диске
в) Сжатие таблиц БД
г) Секционирование таблиц БД
2) Проблема низкой производительности запросов
3) Проблема большого объёма «ненужных данных», которые мешают работе пользователей

В современных условиях очень странно бывает иногда слышать «нам нужно свернуть БД 1С — её объём превышает 50 ГБ». Если бы такое собирались сделать администраторы систем SAP R3 или Oracle e Business Suite или даже MS Dynamics Ax их бы наверное уволили. Тем не менее, для 1С это является «стандартной практикой».

Для файловых версий история тянется ещё с версии 1С 7.7 с ограничением в 2ГБ на размер базы. Сейчас ограничение 2ГБ уже только на размер таблицы, размер файла уже может получиться очень и очень не маленьким. Правда если база у вас выросла до такого размера, то наверное туда активно вносились данные — может нужно задуматься о клиент-сервере?

Собственно целью данной статьи является «отговорить» от выполнения свертки БД пользователей клиент-серверного варианта 1С, за счет использования несколько более «продвинутых» технологий.

I) Проблемы, которые мы пытаемся решать сверткой БД

1) Увеличение размера БД

Собственно главный вопрос: а для чего уменьшать размер БД?
Давайте приложим немного математики:
Серверный жесткий диск на 500 ГБ стоит около 10 т.р. Объединить в RAID 1 для надежности будет 20 т.р.
Естественно могут быть проблемы отсутствия места под новые жесткие диски в самом сервере.
А покупка внешнего дискового массива уже обойдётся не так дешево. Что же делать?
Да всё просто — разместить файлы БД на сетевом диске, а как? Ну об этом статье далее.

Увеличение доступного для БД дискового пространства обойдётся нам в 20 т.р. + 10 минут работы специалиста. Сколько часов работы специалиста потребует свертка БД? А сколько времени простоя может получиться? По самым скромным оценкам за свертку УПП объемом гигабайт в 60, со средним количеством ошибок, партионным учетом с проверкой результатов свертки, выправления этого же партионного учета возьмутся тысяч за 30-40.
Универсальной обработкой всё и сразу вряд ли свернётся, особенно если у вас база практически «никогда не останавливается». Партионный учет в любом случае выправлять. Вообщем много там работы. А самое главное, что итоговая проверка должна быть очень тщательной, и всё равно останутся ошибки.

Кроме того, если база уже размером не 60 а, к примеру, 120 ГБ. малейшая ошибка в коде 1С при свертке и всё. процедура заканчивается не удачно. А ошибки точно будут. Как «недостаточно памяти» при работе с ТЗ, так и ошибки вроде

Итоговая цифра получается 30-40 т. минимум против 20-25 в случае покупки жесткого диска, и получения 500 ГБ дополнительного места

Поэтому появляются продукты вроде [необходимо зарегистрироваться для просмотра ссылки]

Хорошие наверное продукты, и цели свои выполняют. Вот только меняется структура таблиц от версии к версии платформы. 1С нам об этом не раз говорили. Появился разделитель данных в 14-ом релизе и всё. скорее всего эта обработка для 14 релиза уже не подойдёт. Да и страшно как-то, не говоря уже о нарушении лицензионного соглашения.

И даже после этого найдутся пользователи которым «вдруг неожиданно понадобились» стертые данные, которые «как раз хотели поправить» каку-то циферку, которая «не влияет на последовательнсти» в документе закрытого периода. А хуже если выяснится что кто-то эти документы смотрел постоянно для каких-то только ему ведомых целей. Конечно это всё лишь ошибки в методике работы, но тем не менее недовольство пользователей будет.

2) Низкая производительность выполнения запросов

Ну кто же вам сказал что «чем меньше тем быстрее»? Для корректно разработанной ИС это утверждение не верно.
На рисунке ниже кратко и «на птичьем языке» приведен простейший пример выборки по индексу типа B-Дерева записи в таблице адресов:

В тему индексов углубляться не хочу, тем более там всё несколько сложнее. Самое главное — поиск проходит не «горизонтально» по таблице, а «вертикально» по «уровням» индекса.

Аналогия — записная книжка: Каждая страница начинается со своей буквы, только вот на каждой странице ещё такая же записная книжка в которой вы можете выбрать вторую букву в слове, и так до тех пор, пока не встретите ту страницу, на которой будет одна или несколько записей. Удобно? Конечно удобно, в случае если у вас больше нескольких сотен контактов. А если у вас их всего десять? Не проще ли их просто записать на один листочек, который можно просмотреть глазами? Вот и в случае индексов так же. Он эффективен если в таблице несколько тысяч записей, а вот если одна единственная — не очень. Благо СУБД научились самостоятельно выбирать «план запроса» и решать использовать или не использовать тот или иной индекс. Вот только в случае «перебора» всех строк таблицы без индекса СУБД очень часто блокирует всю эту таблицу, и вы наблюдаете «непонятно откуда взявшиеся блокировки» после свертки ИБ.

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

Об этом вы пользователям сперва сделайте рассылку. И получите кучу сообщений что «данные ненужными не бывают». Тем не менее многим не нравится «видеть документы за прошлые периоды» и «архивные данные», с ними нельзя не согласиться. Но решает ли сверка эти проблемы? Убирает ли она ненужные номенклатуры из номенклатурного справочника? Контрагентов с которыми больше не будет вестись работа? А как показывает практика большинство проблем именно в этом.

II) «Технологические» решения проблем

1) Проблема увеличения размера БД
а) разделяем БД на файловые группы

Открываем Management Studio в списке баз выбираем нужную, открываем её свойства.
— Переходим на вкладку «Файловые группы» как показано на рисунке, и добавляем ещё одну файловую группу (на примере она названа SECONDARY

Переходим на вкладку «Файлы» и добавляем новый файл, для которого выбираем созданную файловую группу. Этот файл МОЖНО РАСПОЛОЖИТЬ НА ДРУГОМ ДИСКЕ

Теперь используя обработку к примеру:http://infostart.ru/public/78049/ определяем какие таблицы мы можем смело «пожертвовать» на более медленный (ну или наоборот всё на медленный, остальные — на более быстрый) носитель. Правило 80/20 здесь действует. 80% операций проводятся с 20% данными, так что думайте какие таблички вам нужны оперативно, а какие не очень. «Хранилище дополнительной информации», документы ввода начальных остатков, документы которые уже не используете сразу определяйте как те которые можно перенести в «медленную» файловую группу.

Выбираем таблицу которую нужно перенести в другую файловую группу — выбираем меню изменения таблицы (проект) и в свойствах меняем файловую группу:

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

б) Сохраняем БД на сетевом диске

DBCC TRACEON (1807)

Пишем данную команду в Management Studio, выполняем и можем успешно создавать базы по сети. Само собой при этом экземпляр SQL Server-а должен быть запущен от имени доменной учетной записи, и у этой записи должны быть права на нужную сетевую папку.
Но прошу быть очень внимательными при использовании данной команды в случае если у вас пропадёт сеть при работе с БД вся БД на время её отсутствия будет не доступной. Microsoft не зря закрыли эту возможность для массового использования. Вообще эта возможность предполагается для создания баз на NAS хранилищах, что и настоятельно рекомендую. Подойдёт так же стабильный и надежный файловый сервер, имеющий прямое подключение к серверу на котором запущен MS SQL СУБД.
Подробнее про другие флаги трассировки можно прочитать в статье [необходимо зарегистрироваться для просмотра ссылки]
Т.е. часть файловую группу можно вообще хранить в сети, а уж там дисковое пространство расширяется без проблем.

в) Сжатие таблиц базы данных

EXEC sp_MSforeachtable «ALTER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE)» GO

После выполнения этого кода все таблицы в БД будут сжаты. Очевидно, что можно сжимать и таблицы по отдельности. это как бы на ваш выбор. Что даёт сжатие?
— Экономия дискового пространства
— Снижение нагрузки на дисковую подсистему
Что расходуется? — процессорное время.
Так что если у вас процессор загружен всё время на 70% и выше — сжатие вам использовать нельзя. Если 20-30% загрузка процессора, и при этом очередь к диску вырастает до 3-4. то сжатие таблиц — как раз «лекарство» для вас. Подробнее про сжатие таблиц БД — [необходимо зарегистрироваться для просмотра ссылки]
Важное замечание — функция сжатия таблиц доступна только для обладателей версии Enterprise SQL Server

г) Секционирование таблиц БД

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

Создаём функуцию секционирования по дате:

create partition function YearSection(datetime)
as range right for values («20110101»);

Всё что до 2011 года будет попадать в одну секцию, всё что после — в другую.
— Создаём схему секционирования

create partition scheme YearScheme
as partition YearSection to (SECONDARY, PRIMARY);

Этим говорим, что все данные до 11 года будут попадать в файловую группу «Secondary» а после — в «Primary»

Теперь осталось таблицу перестроить с разделением на секции. Для этого проще всего воспользоваться уже management studio, потому как процесс не простой. Вам нужно перестроить кластерный индекс для таблицы (который по сути и является самой таблицей), выбрав для индекса созданную схему секционирования:

На рисунке вы видите что выбор не доступен — всё правильно, секционирование таблиц возможно только в версии Enterprise MS SQL Server. Кластерный индекс отличить легко — картинка с круглыми скобками. Для РН и всех объектов 1С он создаётся. Для РН кластерный индекс по периоду есть всегда. Для документов и справочников хорошо бы конечно создать другой, который включает реквизит по которому будет секционирование. но это уже будет являться нарушением лицензионного соглашения.

2) Низкая производительность выполнения запросов.

Все действия, описанные выше не должны повлиять на скорость выполнения основных запросов. Более того, использование файловых групп и секций таблиц позволит вам разместить наиболее часто используемые данные на быстрых дисковых массивах, позволит поменять конфигурацию дисковых массивов, использовать небольшие по размеру i/o accelerator. Таким образом скорость выполнение запросов только повысится. А сжатие таблиц позволит вам дополнительно разгрузить дисковую подсистему, если она являлась узким местом. А вообще если говорить о скорости выполнения запросов, то анализ их планов выполнения, оптимизация запросов для грамотного использования индексов даст намного более существенный прирост производительности, чем все «ухищрения» на уровне MS SQL.

3) Большой объём «ненужных данных» которые мешают работе пользователей

Но для этого нужно не сворачивать базу, а проделать следующее:
а) Объяснить всем как пользоваться отборами, как они сохраняются, как пользоваться интервалами журнала, как они сохраняются
б) Пометить на удаление ненужные данные если они не несут никакой смысловой нагрузки (контрагентов и номенклатуру, с которыми больш не работаете) — этим вы принесёте пользователям больше пользы чем сверткой. В случае наличия ресурсов настроить автоматическую пометку на удаление неиспользуемых объектов и сделать отбор по умолчанию в программном коде для того чтобы не отображались по умолчанию не нужные пользователям объекты — помеченные на удаление
в) Настроить другие полезные «отборы по умолчанию» — например чтобы каждый менеджер по умолчанию видел только свои документы. А если хочет посмотреть документы «товарища» — нужно отключать отбор.

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

Tags: 1С, 1С Оптимизация, sql, Администрирование 1С, Обучение, Программирование, Технологии

[необходимо зарегистрироваться для просмотра ссылки]

Многие администраторы 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_id — имя или идентификатор базы данных, которую необходимо сжать. Если указать значение 0, то будет использоваться текущая база данных;
target_percent – свободное пространство в процентах, которое должно остаться в базе данных после сжатия;
NOTRUNCATE — сжимает данные в файлах с помощью перемещения распределенных страниц из конца файла на место нераспределенных страниц в начале файла. Если указан данный параметр, физический размер файла не изменяется;
TRUNCATEONLY — освобождает все свободное пространство в конце файла операционной системе, но не перемещает страницы внутри файла. Файл данных сокращается только до последнего выделенного экстента. Если указан данный параметр, то параметр target_percent не обрабатывается;
WITH NO_INFOMSGS — подавляет все информационные сообщения со степенями серьезности от 0 до 10.

Синтаксис SHRINKDATABASE

DBCC SHRINKDATABASE (database_name | database_id | 0 [ , target_percent ] [ , < NOTRUNCATE | TRUNCATEONLY >]) [ WITH NO_INFOMSGS ]

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

DBCC SHRINKFILE (N»TestBase_log»)

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

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

На этом у меня все, надеюсь, статья была Вам полезна, удачи!

Поэтому появляются продукты вроде
Хорошие наверное продукты, и цели свои выполняют. Вот только меняется структура таблиц от версии к версии платформы. 1С нам об этом не раз говорили. Появился разделитель данных в 14-ом релизе и всё. скорее всего эта обработка для 14 релиза уже не подойдёт. Да и страшно как-то, не говоря уже о нарушении лицензионного соглашения.

— Открываем Management Studio в списке баз выбираем нужную, открываем её свойства.
— Переходим на вкладку «Файловые группы» как показано на рисунке, и добавляем ещё одну файловую группу (на примере она названа SECONDARY)

— Переходим на вкладку «Файлы» и добавляем новый файл, для которого выбираем созданную файловую группу. Этот файл МОЖНО РАСПОЛОЖИТЬ НА ДРУГОМ ДИСКЕ

— Теперь используя обработку к примеру: определяем какие таблицы мы можем смело «пожертвовать» на более медленный (ну или наоборот всё на медленный, остальные — на более быстрый) носитель. Правило 80/20 здесь действует. 80% операций проводятся с 20% данными, так что думайте какие таблички вам нужны оперативно, а какие не очень. «Хранилище дополнительной информации», документы ввода начальных остатков, документы которые уже не используете сразу определяйте как те которые можно перенести в «медленную» файловую группу.

DBCC TRACEON (1807)

Пишем данную команду в Management Studio, выполняем и можем успешно создавать базы по сети. Само собой при этом экземпляр SQL Server-а должен быть запущен от имени доменной учетной записи, и у этой записи должны быть права на нужную сетевую папку.
Но прошу быть очень внимательными при использовании данной команды в случае если у вас пропадёт сеть при работе с БД вся БД на время её отсутствия будет не доступной. Microsoft не зря закрыли эту возможность для массового использования. Вообще эта возможность предполагается для создания баз на NAS хранилищах, что и настоятельно рекомендую. Подойдёт так же стабильный и надежный файловый сервер, имеющий прямое подключение к серверу на котором запущен MS SQL СУБД.
Подробнее про другие флаги трассировки можно прочитать в статье http://msdn.microsoft.com/ru-ru/library/ms188396.aspx
Т.е. часть файловую группу можно вообще хранить в сети, а уж там дисковое пространство расширяется без проблем.

Создаём функуцию секционирования по дате:

create partition function YearSection(datetime)
as range right for values («20110101»);

Всё что до 2011 года будет попадать в одну секцию, всё что после — в другую.

Создаём схему секционирования

create partition scheme YearScheme
as partition YearSection to (SECONDARY, PRIMARY);

Этим говорим, что все данные до 11 года будут попадать в файловую группу «Secondary» а после — в «Primary»

На рисунке вы видите что выбор не доступен — всё правильно, секционирование таблиц возможно только в версии Enterprise MS SQL Server . Кластерный индекс отличить легко — картинка с круглыми скобками. Для РН и всех объектов 1С он создаётся. Для РН кластерный индекс по периоду есть всегда. Для документов и справочников хорошо бы конечно создать другой, который включает реквизит по которому будет секционирование. но это уже будет являться нарушением лицензионного соглашения.

Ну вот я и попробовал сжатие на реальной системе и на реальных базах данных. Пару слов о том, что это такое и переходим к делу. Компрессия данных появилась в SQL Server 2008 и со стороны её можно представить в виде айсберга. На верхушке которого — уменьшение занимаемого дискового пространства, а скрыто от глаз ещё более интересное преимущество — снижение нагрузки на ввод/вывод за счёт меньшего количества читаемых данных. Ну и конечно же, не могу обойти стороной один не очень хороший момент — увеличение нагрузки на CPU, но без этого никак. Более того, сам алгоритм сжатия данных не преследует цель максимально зажать данные, а призван соблюдать разумный баланс между процентом сжатия данных и затратами на упаковку/распаковку данных. Сжатие работает в двух режимах: Row Compression и Page Compression . И в своём посте я расскажу о том как и насколько я зажал данные реальных баз и как это сказалось на работе приложений.

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

ALTER TABLE Test REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

В этом случае будет сжат либо кластерный индекс таблицы (если он есть) либо данные таблицы хранящиеся в куче. Здесь нужно помнить, что некластерные индексы не будут сжаты в этом случае. А при использовании индексов с конструкцией INCLUDE это может быть довольно значительный объём данных. Более подробно о сжатии таблиц, индексов и различных аспектов, связанных со сжатием можно почитать здесь — Создание сжатых таблиц и индексов . Я же хочу показать свой вариант сжатия всей базы данных одним махом Для этого я использую недокументированную процедуру sp_MSforeachtable (вместо символа вопроса хранимка подставляет имя таблицы, таким образом код выполняется для всех таблиц в базе данных):

EXEC sp_MSforeachtable «ALTER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE)»
GO

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

Посмотреть фактический объём данных базы можно при помощи следующего скрипта:

CREATE TABLE #t (name SYSNAME, rows CHAR (11), reserved VARCHAR (18),
data VARCHAR (18), index_size VARCHAR (18), unused VARCHAR (18))

EXEC sp_msforeachtable «INSERT INTO #t EXEC sp_spaceused » «?» «»

SELECT SUM (CONVERT (INT , SUBSTRING (data , 1, LEN(data )-3))) FROM #t
DROP TABLE #t

Запустив этот скрипт до и после компрессии, можно посмотреть эффект, произведённый сжатием. На моих базах (размером от 10-и до 50 Гб) объём данных уменьшился примерно на одну четвёртую. Также вы может сделать замеры показателей использования CPU и памяти в моменты нагрузки системы и сравнить их с показателями до включения сжатия. Напоследок хочу сказать, что недостатков включения компрессии я не почувствовал, только преимущества . Система работает стабильно уже на протяжении нескольких недель, что не может не радовать Но в то же время, на нашем сервере, на котором стоит сиквел, процессор используется далеко не по полной. И я бы не рекомендовал рисковать и включать компрессию на системах с дефицитом процессорного времени.

Как уменьшить размер базы данных SQL Server?

Мне нужно сжать эту БД (и многие другие подобные в разных местах клиента).

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

Итак, теперь мне нужно сжать БД, чтобы учесть недостающие записи.

Я выполняю DBCC ShrinkDatabase(‘MyDB’) . Нет эффекта.
Я пробовал различные средства усадки, представленные в MSSMS . Все равно безрезультатно.
Я сделал резервную копию базы данных и восстановил ее . Все еще безрезультатно.

Тем не менее 1.9 ГБ

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

16 ответы

Работал у меня. Спасибо. — Супер Кодер

ВНИМАНИЕ: это означает, что вам не нужно то, что находится в файле LDF. Установка простого режима восстановления и сжатие файла журнала удаляет все журналы в нем безвозвратно. — Синан ИЛЬЯС

как я могу использовать это как запланированное задание в планировщике Windows? также я хочу использовать это из приглашения sqlcmd? Я попытался поместить эту команду dbbccshrink в расширение файла .sql: REM Запустить файл SQL, чтобы сжать файл журнала mydb11 SQLCMD -S. \ SQLEXPRESS -i «mydb11», но что означает. \ sqlexpress? — пользовательAZLogicApps

Это может показаться странным, но у меня это сработало, и я написал программу на C # для автоматизации этого.

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

Шаг 2. Запустите сжатие базы данных, переместив все страницы в начало файлов.

Шаг 3. Еще раз обрежьте журнал транзакций, так как на шаге 2 добавляются записи журнала.

Шаг 4. Снова запустите сжатие базы данных.

Мой урезанный код, использующий библиотеку SQL DMO, выглядит следующим образом:

Это может быть полезно, если кому-то нужно делать это программно — Чаба Тот

Это старый вопрос, но я только что наткнулся на него.

Действительно короткий и правильный ответ уже дан и набрал наибольшее количество голосов. Это КАК вы сокращаете журнал транзакций, и это, вероятно, проблема OP. А когда журнал транзакций вышел из-под контроля, его часто необходимо сжать обратно, но следует позаботиться о предотвращении в будущем ситуаций, когда журнал выходит из-под контроля. Этот вопрос о dba.se объясняет это. По сути — не позволяйте ему стать таким большим, прежде всего, благодаря правильной модели восстановления, ведению журнала транзакций, управлению транзакциями и т. Д.

Но при чтении этого вопроса об уменьшении файла данных (или даже файла журнала) у меня возникает более серьезный вопрос: Зачем? и что плохого случается, когда вы пытаетесь? Похоже, что были выполнены операции по усадке. В этом случае это имеет смысл в некотором смысле, потому что выпуски MSDE / Express ограничены максимальным размером БД. Но правильным ответом может быть поиск версии, которая соответствует вашим потребностям. И если вы наткнетесь на этот вопрос, пытаясь уменьшить свою производственную базу данных, и это не причина, почему, вы должны спросить себя Зачем? вопрос.

Я не хочу, чтобы кто-то искал в Интернете «как уменьшить базу данных», натолкнувшись на это и подумав, что это круто или приемлемо.

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

Я писал об этой концепции в нескольких сообщениях блога об уменьшении размера баз данных. Это называется «Не трогай эту кнопку сжатия»сначала приходит на ум. Я говорю об этих концепциях, изложенных здесь, а также о концепции» правильного определения размера «вашей базы данных. Намного лучше решить, каким должен быть размер вашей базы данных, спланировать будущий рост и выделить его для этого Благодаря мгновенной инициализации файлов, доступной в SQL Server 2005 и более поздних версиях для файлов данных, стоимость роста ниже — но я все же предпочитаю иметь правильное начальное приложение — и я гораздо меньше боюсь пробелов в базе данных, чем я Я сокращаюсь в общем, не думая сначала. 🙂