В процессе эксплуатации крупных информационных систем на базе 1С и MS SQL Server администраторы часто сталкиваются с ситуацией, когда физический размер файлов базы данных (MDF) во много раз превышает объем реально хранящихся в них данных. Например, база может занимать 900 Гб, в то время как полезной информации в ней всего 65 Гб. Такая ситуация возникает после масштабных чисток данных, удаления старых периодов или некорректной первоначальной настройки сервера. В данной статье мы разберем по шагам, как эффективно вернуть дисковое пространство и оптимизировать работу SQL-сервера.
Прежде чем приступать к каким-либо манипуляциям, нам необходимо четко разделить понятия «размер файла» и «объем данных». Файл базы данных может быть огромным просто потому, что SQL Server зарезервировал это место «на вырост». Чтобы понять реальную картину, проанализируем отчеты в среде SQL Server Management Studio (SSMS).
Рассмотрим стандартный способ проверки: выберем базу данных, нажмем правую кнопку мыши, перейдем в раздел «Отчеты» (Reports) – «Стандартные отчеты» (Standard Reports) – «Использование диска» (Disk Usage). Здесь мы увидим два критически важных показателя:
Если разрыв между этими значениями велик, значит, база нуждается в оптимизации.
Как показывает практика, часто основной причиной невозможности уменьшить базу является установленный параметр Initial Size (Начальный размер). Если при создании базы или в процессе эксплуатации этот параметр был вручную изменен на большое значение (например, 900 Гб), SQL Server будет воспринимать этот объем как минимально допустимый «пол» и не позволит стандартным средствам сжатия опуститься ниже него.
Разберем алгоритм исправления этой ситуации:
Management Studio и подключимся к нашему серверу.ROWS Data и обратим внимание на колонку Initial Size.Важный нюанс: выполнение этой операции может занять значительное время (от нескольких минут до нескольких часов), так как серверу необходимо переупорядочить страницы данных внутри файла, чтобы «отрезать» лишнее. В это время нагрузка на дисковую подсистему будет максимальной.
Выясним разницу между операциями SHRINK DATABASE и SHRINK FILE. Первая команда пытается сжать все файлы базы данных одновременно, что часто приводит к огромным очередям на дисках и зависаниям. Вторая команда позволяет работать с конкретным файлом (данных или лога) более точечно.
Проанализируем выполнение команды через T-SQL, что зачастую эффективнее графического интерфейса:
-- Сжатие конкретного файла данных до минимально возможного размера
DBCC SHRINKFILE (N'Имя_Файла_Данных', 0, TRUNCATEONLY);
Опция TRUNCATEONLY просто освобождает неиспользуемое место в конце файла, не перемещая страницы внутри него. Это самая быстрая и безопасная операция, которая не вызывает фрагментации индексов. Если же нам нужно максимальное сжатие, мы используем перестроение страниц, но должны быть готовы к последствиям.
Важно помнить: любая операция SHRINK (кроме TRUNCATEONLY) катастрофически фрагментирует индексы 1С. После физического уменьшения файла производительность системы может упасть в разы. Поэтому рассмотрим обязательный шаг после сжатия — перестроение индексов (Rebuild). Для автоматизированного перестроения и дефрагментации на уровне СУБД можно использовать решение для оптимизации индексов на MS SQL Server.
Для этого в 1С можно запустить процедуру «Тестирование и исправление» (ТиИ) с установленным флагом «Реиндексация таблиц информационной базы». Однако на больших объемах эффективнее использовать SQL-запрос для всех таблиц:
-- Пример массового перестроения индексов (упрощенно)
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD';
Помните, что для выполнения REBUILD серверу снова потребуется свободное место в базе (примерно 1.2 объема самой крупной таблицы), поэтому никогда не сжимайте файл «под завязку», оставляйте 10-15% запаса.
Чтобы база не разрасталась бесконтрольно и не тормозила в моменты расширения файлов, проанализируем настройки автоприроста. По умолчанию во многих версиях стоит прирост в 10% или по 1 Мб. Для базы объемом в сотни гигабайт это крайне неэффективно:
Рекомендуемая настройка: установите фиксированный прирост (например, от 200 до 500 Мб) в зависимости от интенсивности записи данных. Это сделает рост базы предсказуемым и быстрым. Для комплексной проверки параметров файлов, автоприроста и конфигурации СУБД можно использовать обработку для чек-апа СУБД под 1С.
Если ваша версия SQL Server (Enterprise или Standard начиная с 2016 SP1) позволяет, мы можем использовать технологию сжатия на уровне страниц (Page Compression). Это не просто уменьшает файл, а упаковывает данные внутри таблиц 1С.
Рассмотрим преимущества:
Включение сжатия для конкретной таблицы 1С:
ALTER TABLE [Имя_Таблицы] REBUILD WITH (DATA_COMPRESSION = PAGE);
В теме часто упоминается выгрузка в *.dt. Выясним, оправдан ли этот метод для базы в 900 Гб. Выгрузка и последующая загрузка в новую базу действительно «схлопывает» все пустые пространства и перестраивает индексы идеально. Однако для такого объема данных:
Рассмотрим этот вариант только как крайнюю меру, если SQL-средства не помогли или база имеет логические повреждения, требующие исправления через dt.
Для эффективного уменьшения базы данных 1С на MS SQL Server мы рекомендуем придерживаться следующего плана: сначала проверьте и скорректируйте Initial Size в свойствах файлов, затем выполните Shrink File для удаления пустого пространства, настройте фиксированный автоприрост и обязательно завершите процесс реиндексацией. Такой системный подход позволит не только сэкономить место на дисках, но и повысить общую отзывчивость системы 1С.