История про msdb размером в 42 Гб
Недавно выдалась минутка посмотреть почему старый тестовый сервер безбожно тормозил… К нему я не имел никакого отношения, но меня одолевал спортивный интерес разобраться, что с ним не так.
Первым делом открыл Resource Monitor и взглянул на общую нагрузку. Процесс sqlserv.exe нагружал ЦП под 100% и формировал большую дисковую очередь, которая была за 300… при том, что значение выше единицы уже считается проблемным.
При анализе дисковой активности заметил непрерывные IO операции в msdb:
Посмотрел на размер msdb:
и включил режим «рука-лицо»:
Файл данных занимал 42 Гб… Взяв небольшую паузу я начал разбираться в чем причина такого нездорового объема msdb и как побороть проблемы с производительностью сервера.
Проверил ресурсоемкие запросы, которые выполнялись на сервере:
На первом месте гордо расположилась системная хранимая процедура:
Из названия которой можно догадаться, что речь идет о CDC (Change Data Capture), который применяется как средство для отслеживания измененных данных. CDC основан на чтении журнала транзакций и всегда работает в асинхронном режиме за счет использования Service Broker.
Из-за проблем в конфигурации, при попытке отправить Event Notification для Service Broker, сообщение может не достигнуть места назначения и тогда архивируется в отдельной таблице… Сильно занудно сказано… В общем, если часто используется Service Broker, то нужно мониторить sys.sysxmitqueue. Когда в данной таблице идет постоянный прирост данных, то это либо баг, либо мы неправильно используем Service Broker.
Вот этим запросом можно вернуть список объектов и их размер:
После выполнения получил следующие результаты:
Сразу скажу, что все таблицы в этом списке мы не оставим без внимания. Но сперва нужно очистить sys.sysxmitqueue.
Удалить данные напрямую из sys.sysxmitqueue не получится, поскольку эта таблица является системным объектом (S). После непродолжительных поисков я нашел способ как заставить SQL Server очистить эту таблицу. При создании нового Service Broker автоматически удаляются все ассоциированные со старым брокером сообщения.
Но перед выполнение команды настоятельно рекомендуется отключить SQL Server Agent и перевести SQL Server в Single-User Mode. Удаление существующих сообщений во всех очередях Service Broker заняло у меня минут десять. По завершению выполнения я получил следующее сообщение:
После перезагрузки службы SQL Server все проблемы с производительностью ушли… душа радовалась и на этом можно было бы поставить точку. Но вспомним, что это была не единственная большая таблица в msdb. Давайте разберемся с остальными…
Для тех, кто любит отправлять почту через Database Mail нужно знать, что SQL Server всю почтовую рассылку логирует и хранит в msdb. Все почтовые вложения, которые отправляются с телом письма там аккуратненько сохраняются… Поэтому очень рекомендуется периодически очищать эту информацию. Делать это можно руками, т.е. смотреть какие таблицы нужно почистить:
История выполнения заданий SQL Server Agent также сохраняется в msdb. Когда записей в логе становится много с ним становится не сильно удобно работать, поэтому я стараюсь его периодически чистить sp_purge_jobhistory:
Еще нужно упомянуть, про информацию о резервных копиях, которая логируются в msdb. Старые записи о созданных бекапах можно удалять sp_delete_backuphistory:
Но нужно помнить про один нюанс — при удалении базы данных записи о ее резервных копиях не удаляются из msdb:
В моем случае, когда базы часто создаются и удаляются это может приводить к росту msdb. В ситуации, когда информация о бекапах не нужна, ее можно удалить хранимкой sp_delete_database_backuphistory:
Системная база msdb используется многими компонентами SQL Server, например, такими как Service Broker, SQL Server Agent и Database Mail. Стоит отметить, что нет готового плана обслуживания, который бы учитывал написанное выше, поэтому важно периодически проводить профилактические меры. В моем случае, после удаления лишней информации и усечения файла размер msdb стал 200 Мб против изначальных 42 Гб.
Надеюсь из этого поста вышла поучительная история о пользе постоянного администрирования… не только пользовательских, но и системных баз данных.
Если хотите поделиться этой статьей с англоязычной аудиторией:
How to reduce MSDB size from 42Gb to 200Mb
Сжатие базы данных и журнала транзакций в Microsoft SQL Server
Многие администраторы Microsoft SQL Server сталкивались с проблемой значительного увеличения физического размера базы данных и файлов журнала транзакций и, конечно же, им хотелось бы каким-то образом уменьшить этот размер, для того чтобы не предпринимать какие-либо действия, связанные с увеличением свободного пространства на жестком диске. Способ уменьшить физический размер базы данных и файлов журнала транзакций в SQL сервере есть – это сжатие.
Что такое сжатие в Microsoft SQL Server?
Сжатие — это процесс удаления неиспользуемого пространства в файлах базы данных и журнала транзакций.
Физический размер файлов базы данных со временем растет, это связанно с добавлением данных, но при их удалении физический размер файлов остается неизменным, однако в данных файлах появляется логическое неиспользуемое пространство, которое и можно удалить.
Наибольший эффект от сжатия достигается тогда, когда операция сжатия выполняется после операции удаления таблиц из БД или удаления данных из таблиц.
Следует отличать процедуру сжатия журнала транзакций от процедуры усечения журнала транзакций. Сжатие — это уменьшение физического размера журнала за счет удаления неиспользуемого пространства, а усечение – это освобождение места в логическом журнале для повторного использования (т.е. образуется неиспользуемое пространство) журналом транзакций при этом размер физического файла не уменьшается.
Усечение журнала транзакций происходит автоматически:
Если Вы используете модель полного восстановления или в модель восстановления с неполным протоколированием и у Вас файлы журнала транзакций слишком велики, то скорей всего Вы достаточно долго не делали BACKUP (резервную копию) журнала транзакций. В данном случае Вам необходимо сделать сначала BACKUP журнала транзакций, а затем выполнить сжатие журнала транзакций, которое мы как раз и рассмотрим чуть ниже.
Также возможно размер файлов журнала транзакций слишком большой (как при простой, так и при полной модели восстановления) за счет задержки процедуры усечения, т.е. размер журнала, состоит в основном из активной части журнала, а активную часть усечь нельзя, поэтому физический размер журнала растет. На задержку процедуры усечения влияют такие факторы как: активные длительные транзакции, некоторые сценарии отображения зеркальных баз данных и журнала транзакций, некоторые сценарии при репликации транзакций и журнала транзакций, а также усечение журнала невозможно во время операций резервного копирования и восстановления данных. В данном случае Вам нужно устранить причины задержки, затем сделать усечение (т.е. например, для полной модели восстановления BACKUP журнала), а затем сжатие до приемлемых размеров.
Обычно если на постоянной основе с определенной периодичностью создаются резервные копии журнала транзакций или базы данных (при простой модели восстановления), файлы журнала транзакций не растут, и не возникает переполнение журнала транзакций.
Как сжать базу данных в MS SQL Server?
Сжать файлы базы данных и журнала транзакций можно и с помощью графического интерфейса Management Studio и с помощью инструкций Transact-SQL: DBCC SHRINKDATABASE и DBCC SHRINKFILE. Также возможно настроить базу данных на автоматическое сжатие путем выставления параметра БД AUTO_SHRINK в значение ON.
Примечание! Сжатие базы данных я буду рассматривать на примере Microsoft SQL Server 2016 Express.
Сжимаем базу данных с помощью среды Management Studio
В итоге у Вас откроется окно «Сжатие базы данных», в котором Вы, кстати, можете наблюдать размер базы данных, а также доступное свободное место, которое можно удалить (т.е. сжать). Нажимаем «ОК».
Через некоторое время, в зависимости от размера базы данных, сжатие будет завершено.
Сжимаем базу данных с помощью инструкций SHRINKDATABASE и SHRINKFILE
В MS SQL Server для выполнения сжатия файлов базы данных и журнала транзакций существуют две инструкции SHRINKDATABASE и SHRINKFILE.
Для того чтобы выполнить сжатие БД (например, TestBase) точно также как мы это сделали чуть ранее в Management Studio, выполните следующую инструкцию.
SHRINKDATABASE имеет следующие параметры:
Синтаксис SHRINKDATABASE
Для того чтобы сжать только журнал транзакций можно использовать инструкцию SHRINKFILE, например.
В данном случае мы осуществим сжатие файла журнала (TestBase_log – это название файла журнала транзакций), до его начального значения, т.е. до значения по умолчанию. Для того чтобы сжать файл до определенного размера, укажите вторым параметром размер в мегабайтах. Например, следующей инструкцией мы уменьшим размер файла журнала транзакций до 5 мегабайт.
Также необходимо учесть, что если Вы укажете размер меньше того, чем требуется для хранения данных в файле, то файл до этого размера сжат не будет. Например, допустим, если Вы указали 5 мегабайт, а для хранения данных в файле требуется 7 мегабайт, файл будет сжат только до 7 мегабайт.
SHRINKFILE также имеет параметры NOTRUNCATE и TRUNCATEONLY.
Синтаксис SHRINKFILE
Рекомендации и важные моменты при сжатии базы данных
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
На этом у меня все, надеюсь, статья была Вам полезна, удачи!
База данных msdb
База данных msdb используется агентом SQL Server для создания расписания предупреждений и заданий, а также другими компонентами, такими как среда SQL Server Management Studioи компоненты Компонент Service Broker и Database Mail.
Например, SQL Server автоматически поддерживает полный журнал резервного копирования и восстановления «в сети» в таблицах в базе данных msdb. В эти сведения включено имя стороны, выполнившей резервное копирование, время резервного копирования и устройства или файлы, в которых храниться резервная копия. СредаSQL Server Management Studio использует эти сведения для создания плана восстановления базы данных и применения существующих резервных копий журнала транзакций. События резервного копирования для всех баз данных записываются, даже если они создаются средствами пользовательских приложений или сторонних разработчиков. Например, если приложение Microsoft Visual Basic при выполнении операций резервного копирования обращается к объектам SMO, то событие заносится в системные таблицы базы данных msdb, в журнал приложений Microsoft Windows и журнал ошибок SQL Server. Чтобы защитить сведения, хранящиеся в базе данных msdb, рекомендуется разместить журнал транзакций msdb в отказоустойчивом хранилище.
После любых операций, обновляющих базу данных msdb, например резервного копирования или восстановления любой другой базы данных, рекомендуется создать резервную копию базы данных msdb. Дополнительные сведения см. в статье Резервное копирование и восстановление системных баз данных (SQL Server).
Физические свойства базы данных msdb
| Файл | Логическое имя | Физическое имя | Увеличение размера файлов |
|---|---|---|---|
| Первичные данные | MSDBData | MSDBData.mdf | Автоувеличение на 10 % до заполнения диска. |
| Журнал | MSDBLog | MSDBLog.ldf | Автоувеличение на 10 % до максимального размера в 2 ТБ. |
Сведения о перемещении файлов базы данных и журналов msdb см. в разделе Перемещение системных баз данных.
Параметры базы данных
| Параметр базы данных | Значение по умолчанию | Можно ли изменить |
|---|---|---|
| ALLOW_SNAPSHOT_ISOLATION | ON | нет |
| ANSI_NULL_DEFAULT | OFF | Да |
| ANSI_NULLS | OFF | Да |
| ANSI_PADDING | OFF | Да |
| ANSI_WARNINGS | OFF | Да |
| ARITHABORT | OFF | Да |
| AUTO_CLOSE | OFF | Да |
| AUTO_CREATE_STATISTICS | ON | Да |
| AUTO_SHRINK | OFF | Да |
| AUTO_UPDATE_STATISTICS | ON | Да |
| AUTO_UPDATE_STATISTICS_ASYNC | OFF | Да |
| CHANGE_TRACKING | OFF | нет |
| CONCAT_NULL_YIELDS_NULL | OFF | Да |
| CURSOR_CLOSE_ON_COMMIT | OFF | Да |
| CURSOR_DEFAULT | GLOBAL | Да |
| Параметры доступности базы данных | ONLINE Да | |
| DATE_CORRELATION_OPTIMIZATION | OFF | Да |
| DB_CHAINING | ON | Да |
| ENCRYPTION | OFF | нет |
| MIXED_PAGE_ALLOCATION | ON | нет |
| NUMERIC_ROUNDABORT | OFF | Да |
| PAGE_VERIFY | CHECKSUM | Да |
| PARAMETERIZATION | ПРОСТОЙ | Да |
| QUOTED_IDENTIFIER | OFF | Да |
| READ_COMMITTED_SNAPSHOT | OFF | нет |
| RECOVERY | ПРОСТОЙ | Да |
| RECURSIVE_TRIGGERS | OFF | Да |
| Параметры компонента Service Broker | ENABLE_BROKER | Да |
| TRUSTWORTHY | ON | Да |
Описание этих параметров баз данных см. в разделе ALTER DATABASE (Transact-SQL).
Ограничения
С базой данных msdb нельзя выполнить следующие действия.
Как сжать/снизить размеры базы данных в MS SQL?
При использовании MS SQL появляется проблема, когда размеры расположенных баз данных на физическом носителе увеличиваются до огромных объемов.
Одно из решений — это покупка нового жесткого диска с большим объемом памяти. Но тот же самый MS SQL Server предлагает более экономичное решение (бесплатное) — свои собственные функции (как сжатие). Ниже представлены четыре основных метода по решению данной проблемы.
Шаг 1: Правая кнопка мыши по названию БД → Задачи (Tasks) → Сжать (Shrink) → База данных (Database)
Шаг 2: Нажимаем на «ОК»
Готово. Мы видим, что доступное свободное место можно освободить (сжать) на 0.69 МВ (11%).
Метод 2: Использование Transact SQL Command
Шаг 1: Открываем наш SQL Server Management Studio
Шаг 2: Подключаемся к необходимой Базе данных
Шаг 3: Нажимаем на «Создать запрос» (New Query)
Шаг 4: После чего в открывшемся окне прописываем соответствующую команду (ниже) и жмем кнопку «Выполнить» (Execute)
Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.
Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:
Хранит тип данных CHAR (фиксированной длины), так чтобы система думала, что они являются типами данными, которые имеют переменную длину,
Не применяет сохранение данных, если значения являются 0 и NULL
Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.
Осуществим сжатие по строкам.
Алгоритмы действия данного сжатия заключается в том, что система проходит по всей таблице. Если видит повторяющиеся значения, то вместо копирования этих данных, система создает ссылки на них. Аналогично осуществляется с общими префиксами.
Данное сжатие позволяет максимизировать кол-во строк, которые хранятся на странице,
Повторы данных заменяются ссылками, если происходит сжатие по префиксу.
Пример: используем ту же самую таблицу на 14 500 строк.
Осуществим сжатие по страницам.
Результат: занимаемое пространство данными уменьшилось до 2МВ.
Различия между сжатием на уровне страниц и строк
Если кратко резюмировать выше описанные способы, то главное различие между 3 и 4 способом – это данные которые используются в самой базе данных.
Если вам известно, что БД использует огромное количество повторяющихся значений, то лучше использовать «Сжатие на уровне страниц» (Метод 4), т.к. система хранит ссылки на эти значения, а не дублирует данные. В остальных случаях лучше использовать «Сжатие на уровне рядов» (Метод 3). Первые 2 метода используются по желанию.
Негативные факторы при использовании сжатия:
Частое сжатие Базы Данных не рекомендуется, т.к. сжатие приводит к фрагментации таблиц.
Размер базы данных никаким образом нельзя сделать меньше, чем минимальный размер этой БД. Пример: если базу данных создали с размером 5 МВ и она увеличилась до 50 МВ, то ее можно сжать только до изначального созданного размера в 5МВ (даже с пустыми столбцами и строками).
Чтобы достичь наибольшего эффекта от сжатия, ее нужно применять после операций, которые после своего применения создают большое количество неиспользуемого пространства в БД (удаление таблиц).
Сжатие таблицы в MS SQL позволяет существенно сэкономить дисковое пространство. Помимо экономии места, повышается производительность запросов, т.к. уменьшается количество обрабатываемых строк. При правильном выборе метода, мы можем увидеть значительное освобождение места для записи новых данных. Таблица на 14 500 строк это доказала (уменьшение размера в 2 и в 5 раз).
База данных MSDB не может быть открыта
У меня есть эта проблема в локальном экземпляре SQL Server 2008 R2 на моей машине. В данном случае имеется несколько баз данных. Но я не могу видеть ни одного из них из Обозревателя объектов.
Я могу запросить базы данных из нового окна запроса. Но не в состоянии увидеть никого из них.
всякий раз, когда я пытаюсь исследовать базы данных, я получаю эту ошибку :
база данных «msdb» не может быть открыта. Он был отмечен как «подозреваемый». Видеть дополнительные сведения см. В журнале ошибок SQL Server. (Microsoft SQL Server, ошибка: 926).
Я также пробовал комбинации выше, но ничего завод.
моя операционная система Windows 7 Максимальная (64 бит).
версия SQL Server Management Studio-10.50.2500.0.
3 ответов
я нашел свой ответ в этой ссылка.
EDIT: включая оба решения из ссылки из-за возможного Linkrot в будущем.
войти sa account, для обоих решений.
Решение 1
открыть новое окно запроса
ALTER DATABASE DB_Name SET EMERGENCY; (объяснение: как только база данных установлена в аварийный режим, она становится копией READ_ONLY, и только члены фиксированных ролей сервера sysadmin имеют права доступа к ней.)
DBCC checkdb(‘DB_Name’); (объяснение: проверьте целостность среди всех объекты.)
ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE; (пояснение : перевести базу данных в однопользовательский режим.)
DBCC CheckDB (‘DB_Name’, REPAIR_ALLOW_DATA_LOSS); (объяснение: исправить ошибки)
ALTER DATABASE DB_Name SET MULTI_USER; (объяснение: установите базу данных в многопользовательский режим, чтобы теперь к ней могли получить доступ другие.)
решение 2
открыть C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
движение MSDBData.mdf & MSDBlog.ldf в любое другое место
затем скопируйте эти файлы снова с нового места и поместите его в старый место
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
Затем Обновить Базу Данных.
затем вы можете отсоединить файл MSDB
2-е решение сработало для меня.
Примечание : мне пришлось получить mdf-файлы базы данных» msdb » и ldf с другой рабочей машины, чтобы заставить ее работать.
что мгновенно исправило мою проблему, чтобы заменить существующие MSDBData.mdf & MSDBlog.ldf файлы
«база данных в подозрительном режиме» является одной из распространенных ошибок, с которыми сталкиваются многие пользователи SQL и DBA. Общей причиной этой ошибки может быть поврежденный файл журнала, неправильное завершение работы SQL Server,отсутствие места в памяти и т. д. Вы можете восстановить его, выполнив команды repair. Вот пошаговый подход к восстановление базы данных из подозрительного режима.
