KB2455009 – ВИПРАВЛЕННЯ: Низька продуктивність під час відновлення бази даних, якщо багато VLFs в журналі транзакцій SQL Server 2005, у SQL Server 2008 або SQL Server 2008 R2

Ознаки

Розглянемо такий сценарій:

Microsoft SQL Server 2005, Microsoft SQL Server 2008 або Microsoft SQL Server 2008 R2 інсталюється на комп'ютер.
Одна або кілька баз даних входять до повної або групової моделі відновлення.
Початковий розмір файлу журналу транзакцій для бази даних невеликий. Однак файл транзакції збільшується.
Збільшення автоматичного збільшення файлу журналу транзакцій настроєно на невеликий розмір. Наприклад, крок приросту автоматичного збільшення настроєно на один мегабайт або один відсоток. Крім того, можна збільшити файл журналу транзакцій за допомогою невеликого ручного зростання.
Розмір файлу журналу транзакцій збільшується за рахунок невеликого кроку зростання. Через деякий час файл журналу транзакцій великий.

Наприклад, розмір файлу журналу транзакцій збільшується з ста мегабайтів до десяти гігабайтів. Розмір файлу журналу транзакцій збільшується на один мегабайт у кожному зростанні.

У цьому випадку можуть виникнути такі проблеми:

Відновлення бази даних відбувається повільно, коли база даних запускається, якщо є великий обсяг роботи для відновлення.

Наприклад, транзакція очікує на відкриття протягом тривалого часу. Або багато операцій малого розміру не затверджено, коли SQL Server завершується або перезапускається. У цьому випадку може з'явитися повідомлення про помилку протягом тривалого приблизного часу відновлення на етапі аналізу, на етапі скасування або на етапі повторення. Помилка приблизно такого вигляду, і записується у файл журналу помилок SQL Server 2005:

Аналіз бази даних "mydatabase" (7) виконано на 0% (залишилося приблизно 1234 секунди). Це лише інформаційне повідомлення. Дії користувача не потрібні.
Відновлення бази даних відбувається повільно, коли файл резервної копії повної бази даних і додаткової послідовності журналу транзакцій відновлюються, якщо базу даних перенесено в онлайнове підключення.

Примітка. Щоб вивести базу даних через Інтернет під час операції відновлення, використовуйте синтаксис WITH RECOVERY або WITH STANDBY = <параметри> синтаксис.
Відновлення віддзеркалення бази даних уповільнюється на сервері партнерів у віддзеркалюючий парі.
Перебіг виконання агент читання журналу для транзакцій реплікації повільно для таких операцій:
- Аналіз журналу транзакцій бази даних видавця.
- До бази даних розсилки додається команда.
- Транзакції реплікуються.
Низька продуктивність виникає, коли створюється знімок бази даних, якщо багато транзакцій реєструються або журнали транзакцій залишаються активними протягом тривалого часу. Ця проблема виникає тому, що знімок має запустити відновлення, і журнали транзакцій потрібно відкотити.
Низька продуктивність відбувається, коли використовується синтаксис DBCC CHECKDB, щоб створити прихований знімок бази даних, який має повністю відновити знімок бази даних для виконання перевірки узгодженості.

Причина

Ці проблеми виникають через те, що файли віртуальних журналів (VLFs) у файлі журналу транзакцій може призвести до зниження продуктивності під час SQL Server сканує ці файли VLFs під час таких операцій:

Відновлення бази даних
Віддзеркалення бази даних
Знімок бази даних
Дії зчитування журналу транзакцій реплікації бази даних

Коли файл журналу транзакцій для бази даних збільшується невеликими кроками під час екстремальних умов зростання, тисячі до сотень тисяч файлів VLFs присутні в одному наборі файлів журналу транзакцій (LDF). Однак, великі журнали зростання з великим кроком створює лише кілька VLFs для порівняння. Наприклад, може бути менше 100 VLFs.

Примітка. Файли VLFs – це підрозділи або сегменти, які використовуються внутрішньо SQL Server у файлі журналу транзакцій.

Спосіб вирішення

Сукупні відомості про оновлення

SQL Server 2008 R2

Виправлення цієї проблеми вперше випущено в сукупному пакеті оновлень 6. Щоб отримати додаткові відомості про те, як отримати цей пакет сукупний пакет оновлень для SQL Server 2008 R2 клацніть номер статті в базі знань Microsoft Knowledge Base:

2489376 Сукупний пакет оновлень 6 для SQL Server 2008 R2 Примітка Оскільки збірки сукупні, кожен новий випуск виправлення містить усі виправлення та всі виправлення системи безпеки, які входили в попередній випуск виправлення SQL Server 2008 R2. Радимо застосувати останній випуск виправлення, який містить це виправлення. Щоб отримати докладні відомості, клацніть номер цієї статті бази знань Microsoft:

981356 Збірки SQL Server 2008 R2, випущені після випуску SQL Server 2008 R2

SQL Server 2008 з пакетом оновлень 1

Виправлення цієї проблеми вперше випущено в сукупному пакеті оновлень 12 для SQL Server 2008 з пакетом оновлень 1.

Примітка. Оскільки ці збірки сукупні, кожен новий випуск виправлення містить усі виправлення та всі виправлення системи безпеки, які входили в попередній випуск виправлення SQL Server 2008. Корпорація Майкрософт рекомендує розглянути застосування останнього випуску виправлення, який містить це виправлення. Щоб отримати докладні відомості, клацніть номер цієї статті бази знань Microsoft:

970365 Збірки SQL Server 2008 року, випущені після випуску пакета оновлень 1 SQL Server 2008 року microsoft SQL Server 2008, створюються для певних пакетів оновлень SQL Server. Потрібно застосувати виправлення SQL Server 2008 з пакетом оновлень 1 до інсталяції пакета оновлень 1 SQL Server 2008. За промовчанням будь-які виправлення, які надаються в SQL Server пакет оновлень входить до складу наступного пакета оновлень SQL Server.

SQL Server 2008 із пакетом оновлень 2

Виправлення цієї проблеми вперше випущено в сукупному пакеті оновлень 2 для пакета оновлень 2 SQL Server 2008 року.

Примітка. Оскільки ці збірки сукупні, кожен новий випуск виправлення містить усі виправлення та всі виправлення системи безпеки, які входили в попередній випуск виправлення SQL Server 2008. Корпорація Майкрософт рекомендує розглянути застосування останнього випуску виправлення, який містить це виправлення. Щоб отримати докладні відомості, клацніть номер цієї статті бази знань Microsoft:

2402659 Збірки SQL Server 2008 року, випущені після випуску пакета оновлень 2 SQL Server 2008 microsoft SQL Server 2008, створюються для певних пакетів оновлень SQL Server. Потрібно застосувати виправлення SQL Server 2008 з пакетом оновлень 2 до інсталяції пакета оновлень 2 SQL Server 2008 року. За промовчанням будь-які виправлення, які надаються в SQL Server пакет оновлень входить до складу наступного пакета оновлень SQL Server.

SQL Server 2005 із пакетом оновлень 3

Виправлення цієї проблеми вперше випущено в сукупному пакеті оновлень 13 для пакета оновлень 3 SQL Server 2005.

Примітка. Оскільки ці збірки сукупні, кожен новий випуск виправлення містить усі виправлення та всі виправлення системи безпеки, які входили в попередній випуск виправлення SQL Server 2005 року. Корпорація Майкрософт рекомендує розглянути застосування останнього випуску виправлення, який містить це виправлення. Щоб отримати докладні відомості, клацніть номер цієї статті бази знань Microsoft:

960598 Збірки SQL Server 2005, випущені після випуску SQL Server 2005 із пакетом оновлень 3 (SP3) microsoft SQL Server 2005, створюються для певних пакетів оновлень SQL Server. Потрібно застосувати виправлення SQL Server 2005 із пакетом оновлень 3 для інсталяції пакета оновлень 3 SQL Server 2005. За промовчанням будь-які виправлення, які надаються в SQL Server пакет оновлень входить до складу наступного пакета оновлень SQL Server.

SQL Server 2005 із пакетом оновлень 4

Виправлення цієї проблеми вперше випущено в сукупному пакеті оновлень 1 для пакета оновлень 4 SQL Server 2005.

Примітка. Оскільки ці збірки сукупні, кожен новий випуск виправлення містить усі виправлення та всі виправлення системи безпеки, які входили в попередній випуск виправлення SQL Server 2005 року. Корпорація Майкрософт рекомендує розглянути застосування останнього випуску виправлення, який містить це виправлення.

Потрібно застосувати виправлення SQL Server 2005 з пакетом оновлень 4 для інсталяції пакета оновлень 4 SQL Server 2005. За промовчанням будь-які виправлення, які надаються в SQL Server пакет оновлень входить до складу наступного пакета оновлень SQL Server.

Стан

Корпорація Майкрософт підтвердила, що це проблема в продуктах Microsoft, перелічених у розділі "Стосується".

Інші способи вирішення

Інсталюйте це виправлення та перезавантажте SQL Server може підвищити продуктивність відновлення. Наприклад, продуктивність може бути покращено для таких операцій після інсталяції цього виправлення та перезавантажити SQL Server:

Відновлення бази даних
Віддзеркалення бази даних
Знімок бази даних
Дії зчитування журналу транзакцій реплікації бази даних

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

Зменшення наявної проблеми

Дочекайтеся завершення

операції відновлення або відновлення Якщо у вас є не відновлена база даних, яка спостерігається повільно під час відновлення або відновлення бази даних, можливо, доведеться дочекатися завершення операції відновлення або відновлення. Наприклад, може відображатися стан автономного режиму або стан відновлення в SQL Server Management Studio (SSMS) для невідновленої бази даних. Зупинка SQL Server зазвичай не надає полегшення для повільного відновлення та може зайняти більше часу, щоб повторити той самий етап аналізу відновлення, повторити етап або етап скасування.
Уникайте відновлення послідовності журналу транзакцій, яка містить тисячі файлів VLFs

Якщо під час відновлення та відновлення бази даних за допомогою файлу резервної копії низька продуктивність, можна уникнути відновлення послідовностей журналу транзакцій, які містять тисячі файлів VLFs. Щоб визначити файл резервної копії, який містить найбільш віртуальні файли журналу, записані, скористайтеся наведеною нижче інструкцією, щоб переглянути стовпці FirstLSN і LastLSN у файлах резервного копіювання журналу:
ВІДНОВИТИ ВЕРХНІЙ КОЛОНТИТУЛ ІЗ ДИСКА='C:\folder\file.trn'

Ви можете вирішити уникнути відновлення файлів резервної копії журналу. Або можна скористатися інструкцією STOP AT у командах RESTORE, щоб уникнути дуже фрагментованих частин журналів транзакцій. Якщо не повністю відновити послідовність журналів до останньої точки часу під час сценарію відновлення помилок, у базі даних SQL Server втрата даних. Ця втрата даних відбувається через те, що зберігаються не всі транзакції. Тому є рішення про компроміс бізнесу. Ви можете повністю відновити дуже фрагментований журнал транзакцій. Проте ця операція може тривати багато годин. Або ви можете використовувати інструкцію STOP AT у відновленні, щоб зупинити відновлення перед сильно фрагментованою частиною журналу. Проте всі відсутні транзакції буде втрачено.

Примітка. Без інсталяції цього виправлення, зазвичай немає безпечного регресивний для прискореного відновлення після перезавантаження SQL Server. SQL Server потрібно знайти список файлів VLFs для аналізу файлів журналу, повторити завершені транзакції, а потім скасувати незавершені транзакції, щоб завершити відновлення, щоб безпечно перенести базу даних в онлайнове підключення. Не можна безпечно пропустити транзакції під час відновлення.

Запобігання майбутньому екземпляру

Установлення відповідного розміру

кроку автоматичного збільшення бази даних Якщо розмір кроку автоматичного збільшення замалий, буде багато файлів віртуальних журналів (VLFs), і ви можете помітити низьку продуктивність у SQL Server. Якщо розмір кроку автоматичного збільшення завеликий, запити, які змушують журнали транзакцій збільшуватися автоматично, можливо, доведеться зачекати довгий час, щоб завершити зростання. Таким чином, у SQL Server може статися помилка тайм-ауту. Щоб вирішити ці проблеми, можна встановити відповідний розмір кроку автоматичного збільшення для бази даних.
Видаліть велику кількість VLFs і використайте ручне зростання

Якщо в журналі транзакцій багато файлів VLFs, зменште розмір журналу транзакцій і збільште його перед максимальним зростанням бізнесу, щоб задовольнити попит за допомогою ручного зростання. Наприклад, журнал транзакцій збільшується до розумного середнього розміру у великому прирості або в одному ручному зростанні. Таким чином, розмір журналу транзакцій досягає максимального навантаження, а файли резервного копіювання журналу плануються на часті та періодично. Крім того, журнал транзакцій може бути скорочено, і VLFs для журналу транзакцій можна використовувати повторно протягом циклу.
Стискання та збільшення журналу транзакцій вручну

Щоб виправити журнал із забагато файлів VLFs, виконайте наведені нижче дії, щоб стиснути журнал і знову збільшити його вручну.
1. Якщо база даних перебуває в повній або груповій моделі відновлення, потрібно створити резервну копію журналу транзакцій, щоб дозволити скорочення активних файлів VLFs і повторного використання. BACKUP LOG databasename TO DISK='C:\folder\log_backupfile.trn'
  Щоб отримати додаткові відомості про те, як створити резервну копію файлу журналу транзакцій за допомогою SSMS відвідайте такий веб-сайт Microsoft Developer Network (MSDN):
  
  Резервне копіювання файлу журналу транзакцій за допомогою SSMSЩоб отримати додаткові відомості про те, як створити резервну копію файлу журналу транзакцій за допомогою операторів Transact-SQL відвідайте такий веб-сайт MSDN:
  
  Резервне копіювання файлу журналу транзакцій за допомогою операторів Transact-SQL
2. Щоб визначити логічне ім'я файлу журналу транзакцій, виконайте одну з наведених нижче інструкцій.
  Оператор 1
  Інструкція exec sp_helpfile 2
  Select * from sys.sysfiles To reduce the size of the transaction log file to the desired size, use the following code:DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
3. Розмір файлу журналу транзакцій можна збільшити до відповідного розміру. Радимо, щоб розмір файлу журналу транзакцій зростав до максимального звичайного розміру. Таким чином, слід уникати приросту автогроу. Щоб установити розмір журналу транзакцій, скористайтеся сторінкою "Властивості бази даних" у службах SSMS або скористайтеся таким синтаксисом ALTER DATABASE:MODIFY FILE (NAME = transactionloglogicalfilenae, SIZE=newtotalsize MB)
  Щоб отримати додаткові відомості про те, як збільшити розмір бази даних у службах SSMS відвідайте такий веб-сайт MSDN:
  
  Збільшення розміру бази даних у службах SSMSЩоб отримати додаткові відомості про синтаксис ALTER DATABASE MODIFY FILE відвідайте такий веб-сайт MSDN:
  
  Загальні відомості про синтаксис ALTER DATABASE MODIFY FILE

Додаткові відомості

Ви можете перевірити кількість сегментів VLF, переглянувши файл журналу помилок SQL, а потім знайшовши номер послідовності журналу (LSN) у кожному файлі резервної копії журналу транзакцій. Перші цифри перед символом двокрапки в LSN відповідають кількості LSN.

Наприклад, перший номер у першому інформаційному повідомленні LSN – 1. Однак перший номер у другому інформаційному повідомленні для LSN 100001. У цьому сценарії існує 100 000 VLFs, які використовуються між часом першого інформаційного повідомлення та другого інформаційного повідомлення. Таким чином, журнал записаних фрагментованих транзакцій, який має багато файлів віртуальних журналів (VLFs) приблизно такого вигляду:

{Резервну копію журналу створено. База даних: mydbname, дата створення(час): 2010/07/08(12:36:46), перший LSN: 1:5068:70, останній LSN: 1:5108:1, кількість пристроїв дампа: 1, відомості про пристрій: (FILE=1, TYPE=DISK: {'C:\folder\logbackup1.trn'}). Це лише інформаційне повідомлення. Дії користувача не потрібні.

Резервне копіювання журналу. База даних: mydbname, дата створення(час): 2010/07/08(15:36:46), перший LSN: 100001:5108:1, останній LSN: 100002:5108:1, кількість пристроїв дампа: 1, відомості про пристрій: (FILE=2, TYPE=DISK: {'C:\folder\logbackup2.trn'}). Це лише інформаційне повідомлення. Дії користувача не потрібні.}

У таблиці нижче наведено докладні відомості про продукти або засоби, які автоматично перевіряють стан, описаний у розділі "Ознаки" екземпляра SQL Server та у версіях SQL Server, з якими обчислюється правило.

Програмне забезпечення для правил	Заголовок правила	Опис правила	Версії продуктів, для яких обчислюється правило
Помічник системного центру	SQL Server з транзакцій реплікації, агент читання журналу продуктивність може бути порушено через розмір журналу транзакцій або кількість VLF	У цьому SQL Server екземпляр радник виявив присутність транзакцій реплікації з кількістю VLFs або TLOG розмір значно більше. На продуктивність агента читання журналів негативно впливає розмір журналу транзакцій або кількість VLF. Зменште розмір журналу транзакцій і кількість VLF, щоб підвищити продуктивність агента читання журналу.	SQL Server 2008 р . SQL Server 2008 R2 SQL Server 2012 р.

Посилання

Щоб отримати додаткові відомості про фізичну архітектуру журналу транзакцій відвідайте такий веб-сайт MSDN:

Загальні відомості про фізичну архітектуру журналу

транзакцій Щоб отримати додаткові відомості про номери послідовності журналу (LSN), відвідайте такий веб-сайт MSDN:

Загальні відомості про номери послідовностей журналу

Щоб отримати додаткові відомості про помилку 1413 під час запуску віддзеркалення бази даних відвідайте такий веб-сайт MSDN:

Загальні відомості про помилку 1413 під час запуску

віддзеркалення бази даних Щоб отримати додаткові відомості про те, як структура файлу журналу може вплинути на час відновлення бази даних, відвідайте такий веб-сайт MSDN:

Як структура файлу журналу може вплинути на час

відновлення бази даних Щоб отримати додаткові відомості про журнал транзакцій VLFs відвідайте такий веб-сайт MSDN:

Загальні відомості про файл

журналу транзакцій Щоб отримати додаткові відомості про те, як створити знімок бази даних відвідайте такий веб-сайт MSDN:

Створення знімка бази данихЩоб отримати додаткові відомості про модель інкрементного обслуговування для SQL Server клацніть номер статті в базі знань Microsoft Knowledge Base:

935897 Інкрементна модель обслуговування доступна в команді SQL Server для доставки виправлень для повідомлених проблем. Щоб отримати додаткові відомості про схему іменування для оновлень SQL Server, клацніть номер статті в базі знань Microsoft Knowledge Base:

822499Нова схема іменування для пакетів оновлення програмного забезпечення Microsoft SQL Server Щоб отримати додаткові відомості про термінологію оновлення програмного забезпечення, клацніть номер статті в базі знань Microsoft Knowledge Base:

824684 Опис стандартної термінології, яка використовується для опису оновлень програмного забезпечення Microsoft