Методы борьбы с увеличением размера баз в связке MS SQL + 1С:Предприятие

средствами MS SQL 2008.

Очень часто в практике встречаются случаи, когда, с точки зрения администраторов, "внезапно" начинает катастрофически увеличиваться размер журнала транзакций (*.ldf) и файла с данными (*.mdf) у рабочей базы MS SQL. Для связки 1С:Предприятие + MS SQL вообще характерно такое поведение. Связано оно, в первую очередь, с обновлениями конфигураций 1С, когда с очередным обновлением добавляются новые объекты и процедуры, проводятся какие-то манипуляции с существующими объектами типа их реиндексации, реструктуризации баз и т.п. Второй вариант причины такого поведения - запуск массовой обработки объектов базы 1С, возможно, не оптимальными методами. Ну и третий вариант - просто резкое увеличение рабочей нагрузки на базу. Поскольку дисковый ресурс серверов БД, как известно, не резиновый, резкий рост размеров базы зачастую приводит к панике администраторов и разнообразным попыткам самостоятельно эту проблему решить. Предлагаю администраторам БД отработанные на практике несколько более или менее эффективных способов решения.

1. Если у вас есть возможность перенести рассматриваемую базу на более ёмкий диск, а лучше, просто на другой, можно это сделать. Как вариант - перенести только журнал транзакций *.ldf или файл с данными *.mdf. Причем, если вы не опирались на мои и другие известные рекомендации при развертывании этой самой базы, не поздно это сделать и сейчас, тем более, ситуация позволяет). Делается это, в основном, с помощью SQL Server Management Studio (SSMS) следующим образом:

- подготовить новый раздел, куда будет перенесен журнал транзакций (или данные), желательно, если это будет физически вообще другой диск;

- убедиться, что нет ни одного активного сеанса работы с базой. Если это база 1С:Предприятия, рекомендуется остановить службу "Агент сервера 1С:Предприятия";

- выполнить отсоединение базы (detach);

- перенести файл журнала (или данных) на подготовленный новый раздел;

- выполнить присоединение (attach) базы с указанием нового места расположения файла с журналом (или данными);

- запустить сервер 1С:Предприятия;

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

2. В оснастке SSMS существуют инструменты для "сжатия" или "усечения" (shrink) как отдельных файлов, так и базы данных целиком:

Открытие меню инструментов для сжатия в SSMS:

Сжатие базы SQL целиком:

Сжатие отдельных файлов данных и лога базы SQL: 

Бывают случаи, когда подобное усечение помогает существенно уменьшить занимаемое базой дисковое пространство как за счет уменьшения mdf-файла, так и за счет уменьшения размера файла с журналом *.ldf. 

Хотелось бы отметить, что как временная мера методы 1 и 2 в общем то эффективны. Но если база уже "пошла в рост", скорее всего, этот процесс не остановить и через некоторое время вы опять столкнетесь с увеличением ее размеров. Поэтому давайте подробно рассмотрим еще один, более радикальный способ борьбы с ростом баз.

3. А метод этот, как ни странно - backup.

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

Теперь о том, как это настроить. Для начала необходимо выделить место, куда будут выгружаться бэкапы. Это должен быть ёмкий дисковый массив, достаточно скоростной (в первую очередь необходим скоростной линк к серверу БД) и надежный, учитывая хранение на нем бэкапов. Причем, повторюсь, я говорю не о стандартном, скажем, ежесуточном бэкапе средствами MS SQL, а об оперативном бэкапе, который мы будем выполнять чаще, чем раз в сутки, а именно, в рабочие часы, с интервалом от 30 минут до 2-х часов (зависит от интенсивности работы в базе и ее размеров).  Остальные действия по настройке выполняются в SSMS.

Задача: 

Обеспечить оперативное резервное копирование в течение дня, а именно:

- иметь полную копию на начало дня , хранится 1 сутки, затем перезаписывается;

- иметь копии журнала транзакций (TL) через каждые 20 минут;

- актуальность копий журнала транзакций 1 сутки, по прошествии - должны удаляться;

- оперативные бэкапы хранятся на сетевом диске (NAS);

Решение:

Необходимое условие: пользователь, под которым запускается SQL Server agent и SQL Server должен иметь права на сетевой ресурс NAS сервера, куда будут складываться бэкапы. Кроме того, Recovery Model базы, которую планируется бэкапить должна быть Full.

Создаем новый запрос в SSMS:

Необходимо обеспечить возможность поключения SQL сервера к сетевому диску Х:.

а) Предварительная операция, требуется для включения расширенных опций SQL 2008 

(код Т-SQL):

-- Включение использования расширенных опций.

EXEC sp_configure 'show advanced options', 1

GO

-- Применение этого значения для текущей конфигурации.

RECONFIGURE

GO

-- Разрешение использования cmdshell в MS SQL.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- Применение этого значения для текущей конфигурации.

RECONFIGURE

GO

б) Непосредственно подключение к экземпляру MS SQL логического диска 

(код T-SQL):

EXEC xp_cmdshell 'net use Х: \\SRV-nas\1c_backup'

GO

б1) Полный бэкап (Full) будет перезаписываться поверх предыдущего, поэтому создаем Backup Device на нашем сетевом диске 
(код T-SQL)*:

USE master
EXEC sp_addumpdevice 'disk', 'FULLBAСK', '\\SRV-nas\1c_backup\FullBak.bak'
GO

Таким образом, в Server Objects\Backup device появляется новое устройство FULLBAK.

в) Настраиваем Maintenance Plan (MP) из 2-х подпланов:

- subplan_1 - полный ежесуточный оперативный бэкап по расписанию;

- subplan_2 - бэкап Transaction log и удаление бэкапов Transaction log с датой ранее 1 дня, по расписанию ;

г) Запускаем в SSMS мастер Мaintenance Рlan Wizard:

д) Указываем Separate shedules for each task (отдельные расписания для каждой задачи)

е) В задачах выбираем: 

- Back up Database (FULL); 

- Back up Database (Transaction Log),

таким образом получаем 2 подплана;

ж) Настраиваем subplan_1: 

- выбираем базы для копирования, 

- выбираем Back up databases across one or more files, 

- кнопкой ADD выбираем подключенное устройство FULLBAСK, 

- в If backup files exist выбираем “owerwrite”, 

- настраиваем расписание для выполнения задачи Shedule кнопкой change - выполнение каждый день в определенное время, в моем случае в 7:00.

з) Настраиваем subplan_2 по аналогичному принципу: 

- выбираем базы для копирования; 

- выбираем create a backup file for every database

- указываем путь к папке на диске Х:, куда будут складываться бэкапы TL; 

- настраиваем расписание Shedule - каждые двадцать минут в интервале, например, с 07:20 до 23:00;

и) Сохраняем, открываем на редактирование (Modify в дереве Management\Maintenance plans) наш сохраненный Maintenance Plan.

к) В subplan_2 drag-and-drop мышью добавляем из Toolbox задачу Maintenance Cleanup Task.

л) Щелкаем Edit по этой задаче, 

- в пункте Search folder and delete files based on an extension, поле Folder выбираем папку, куда складываются файлы бэкапа Transaction Log. 

- В поле File extension (расширение файлов) ставим trn. 

- В Delete files older than following, согласно задаче, ставим 1 сутки. 

- Соединяем стрелкой обе подзадачи (бэкап и удаление), чтобы они выполнялись по одному расписанию.

м) Сохраняем наш Maintenance Plan, он готов к выполнению.

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

Для тех, кто осилил статью, хотел бы пояснить один нюанс. Использование cmdshell для подключения сетевого диска к экземпляру MS SQL имеет одну особенность - оно действует до перезапуска службы сервера. Поэтому рекомендую либо создать дополнительное задание для агента сервера, которое будет при запуске службы MS SQL выполнять команду: 

EXEC xp_cmdshell 'net use Х: \\*******\1c_backup'

GO

либо делать это вручную после каждого перезапуска службы.

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

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

Поэтому желающих приглашаю обсудить этот вопрос и так же поделиться опытом. )

--------------------------------------------------------------------------------------------

* имеется альтернативный вариант, когда, как и для бэкапа Transaction Log, можно обойтись без создания Backup Device , воспользовавшись Create a backup file for every database. Это несколько упрощает решение, чем воспользоваться вы решите сами.)

При перепечатке ссылка на источник обязательна.
(с) alexeistar.blogspot.com