Багато адміністратори 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.
Стискаємо базу даних за допомогою середовища 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.
Для того щоб стиснути тільки журнал транзакцій можна використовувати інструкцію SHRINKFILE. наприклад.
В даному випадку ми здійснимо стиснення файлу журналу (TestBase_log - це назва файлу журналу транзакцій), до його початкового значення, тобто до значення за замовчуванням. Для того щоб стиснути файл до певного розміру, вкажіть другим параметром розмір в мегабайтах. Наприклад, наступною інструкцією ми зменшимо розмір файлу журналу транзакцій до 5 мегабайт.
Також необхідно врахувати, що якщо Ви вкажете розмір менше того, чим потрібно для зберігання даних у файлі, то файл до цього розміру стиснутий не буде. Наприклад, припустимо, якщо Ви вказали 5 мегабайт, а для зберігання даних у файлі потрібно 7 мегабайт, файл буде стиснутий тільки до 7 мегабайт.
SHRINKFILE також має параметри NOTRUNCATE і TRUNCATEONLY.
Рекомендації і важливі моменти при стисненні бази даних
- Операція стиснення бази даних може викликати фрагментацію індексів і уповільнити роботу БД. Тому дуже часто не рекомендується виконувати стиснення бази даних;
- Стискати БД краще до операції перестроювання індексів, тобто після стиснення запустіть процедуру перестроювання індексів;
- Параметр бази даних 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, при цьому вся виконана робота зберігається;
- Перед запуском процедури стиснення перевірте, чи є вільний простір для видалення в файлах бази даних, тобто чи можна взагалі стиснути файли, виконавши наступний запит (він покаже в мегабайтах, на скільки Ви можете зменшити файли БД).
