средствами 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
(код 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 могут так же содержать более продвинутый функционал, где функции бэкапа будут совмещены с дефрагментацией индексов, например.
Поэтому желающих приглашаю обсудить этот вопрос и так же поделиться опытом. )
Описанные методы в статье не являются панацеей, есть и другие методики, например, Autoshrink, использование модели восстановления Simple, скрипты SQL могут так же содержать более продвинутый функционал, где функции бэкапа будут совмещены с дефрагментацией индексов, например.
Поэтому желающих приглашаю обсудить этот вопрос и так же поделиться опытом. )
--------------------------------------------------------------------------------------------
* имеется альтернативный вариант, когда, как и для бэкапа Transaction Log, можно обойтись без создания Backup Device , воспользовавшись Create a backup file for every database. Это несколько упрощает решение, чем воспользоваться вы решите сами.)
При перепечатке ссылка на источник обязательна.
(с) alexeistar.blogspot.com
При перепечатке ссылка на источник обязательна.
(с) alexeistar.blogspot.com
