средствами 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
.jpg)
