DBCC CHECKDB оповещение о повреждении баз данных SQL

09.05.20

База данных - Администрирование СУБД

Проверка целостности баз данных SQL при помощи DBCC CHECKDB и рассылка оповещений на почту.

Всем доброго времени суток!

Сразу оговорюсь, что мои познания в T-SQL не сильно велики т. к. по большей части пишу код для конфигураций на платформе 1С:Предприятие, и предложенное решение может быть не совсем оптимальным.

Оптимизация и улучшения предложенного скрипта приветствуется.

 

Небольшое предисловие.

Часто случается, что базы данных повреждаются, по различным причинам, и мы не всегда это вовремя замечаем.

Что бы проверить базу данных надо зайти в интерфейс, запустить скрипт, получить результат. И уже в зависимости от результата принимать какие-то решения и действия. Возможно это нормально, когда есть свободно время и не лень запустить скрипт вручную. Но что делать когда, к примеру, на поддержке с 10-к и более баз и находятся они на разных серверах. Подключаться к каждому серверу и запускать скрипт руками занимает много времени, да делать это вручную лень.

Для разработчика, администратора и т. п. лень это двигатель его прогресса, настроил систему как надо и читай логи, письма и прочее оповещения.

Вот после очередного повреждения базы, я опять вернулся к задаче проверки целостности баз по регламенту и рассылке результата на почту. Ранее я уже занимался этой задачей но не доделал, точнее не нашел нужного мне решения.

Это было небольшое предисловие, теперь перейдем к самой задаче.

 

Целью задачи было: проверять целостность баз данных 1с на сервере SQL по регламенту и при обнаружении поврежденной базы оповещать по почте. Оповещение только если найдена поврежденная база. Состав письма краткий, все данные результата проверки высылать не требуется.


Поиски готового решения в интернете ничего не дали. Нашел всего одно решение, но оно мне не подходит. Кому интересно, можете ознакомится с публикацией Отправляем результаты задания DBCC CHECKDB по электронной почте.

Сложность в решении задачи заключается в том, что функция DBCC CHCKDB возвращает результат в виде текстового сообщения. Для обработки и формирования сообщения на почту это не годится.

На просторах интернета прочитал, что данные можно вывести в таблицу.

DBCC CHCKDB WITH TABLERESULTS выведет данные в таблицу, но просто так взять и сделать выборку из из этой таблицы нельзя, но выход все же нашелся.

В поисках нужной информации для решения моей задачи наткнулся на публикацию Capture and Store SQL Server Database Integrity History using DBCC CHECKDB, которая была взята за основу решения задачи.

Спасибо тебе Rodert Pearl, что ты ее когда то написал.

 

Решение:

1. Создаем временную таблицу.

Исходное описание таблицы немного изменено.

Добавлена колонка "DatabaseName".

Изменил типы данных в некоторых колонках, т.к. при первом же тесте получил ошибки о невозможности преобразования типов данных

Колонки: PartitionID, AllocUnitID изменил тип данных с INT на BIGINT,

Колонка: RepairLevel с INT на VARCHAR(300)

В каких колонках нужно менять тип данных искал методом тыка и исключения.

 
 Скрипт временной таблицы

 

2. В ранее созданную временную таблицу, при помощи CURSOR, по списку баз, поместим выходные данные DBCC CHCKDB.

У меня есть база 'Recovery', использую для разных целей. Сегодя она играет роль поврежденной базы данных. Результ ее проверки и поместим во временную таблицу.

 
 Результаты DBCC CHECKDB вставляем во временную таблицу
 
 Результаты DBCC CHECKDB. Выборка уже из временной таблицы

 

3. Делаем выборку из временной таблицы и формируем строку сообщения. Выбираем только строки, которые содержат текст ошибки.

Теперь с данными можно работать, накладывать отборы, делать сортировку и все остальное.

Мне на выходе нужна одна строка с описанием ошибок. Строку собираю из колонок: DatabaseName, MessageText при помощи конкатенации. Дополнительно накладываю условия на 'MessageText', что бы получить нужные строки, т.к. если база не повреждена данные в выходном наборе все равно будут. Только в тексте будет количество ошибок "0". Мне эти данные не нужны.

 
 Скипт формирования строки сообщения
 
 Результат выполнения скипта

Строку сообщения сформировали, временная таблица более не нужна, поэтому

DROP TABLE #DBCC_DataReport

 

Осталось проверить есть ли у нас в сформированной строке данные, при их наличии отправляем данные на почту.

 
 Скрипт отправки сообщения

 

Проверяем скрипт, все работает.

Создаем Job, настраиваем расписание и готово

 
 Полный текст скрипта

IF OBJECT_iD('tempdb..#DBCC_DataReport')  is not null
DROP TABLE #DBCC_DataReport
GO

-- table structure for SQL Server 2012, 2014, 2016 and 2017
CREATE TABLE #DBCC_DataReport(
    [DatabaseName][VARCHAR](100) NULL
    ,[Error] [int] NULL
    ,[Level] [int] NULL
    ,[State] [int] NULL
    ,[MessageText] [VARCHAR](7000) NULL
    ,[RepairLevel] [VARCHAR](300) NULL
    ,[Status] [int] NULL
    ,[DbId] [int] NULL
    ,[DbFragId] [int] NULL
    ,[ObjectId] [int] NULL
    ,[IndexId] [int] NULL
    ,[PartitionID] [bigint] NULL
    ,[AllocUnitID] [bigint] NULL
    ,[RidDbId] [int] NULL
    ,[RidPruId] [int] NULL
    ,[File] [int] NULL
    ,[Page] [int] NULL
    ,[Slot] [int] NULL
    ,[RefDbId] [int] NULL
    ,[RefPruId] [int] NULL
    ,[RefFile] [int] NULL
    ,[RefPage] [int] NULL
    ,[RefSlot] [int] NULL
    ,[Allocation] [int] NULL
)

DECLARE @database_name NVARCHAR(50)
DECLARE database_cursor CURSOR FOR

SELECT name
FROM sys.databases db
WHERE name = 'Recovery'
   AND db.state_desc = 'ONLINE'
   AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
   AND is_read_only = 0

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database_name
WHILE @@FETCH_STATUS=0
    BEGIN

      INSERT INTO #DBCC_DataReport ([Error], [Level], [State], MessageText, RepairLevel, [Status],
      [DbId], DbFragId, ObjectId, IndexId, PartitionId, AllocUnitId, RidDbId, RidPruId, [File], Page, Slot,
      RefDbId, RefPruId, RefFile, RefPage, RefSlot,Allocation)
      EXEC ('DBCC CHECKDB(''' + @database_name + ''') WITH TABLERESULTS, ALL_ERRORMSGS, DATA_PURITY')

      UPDATE #DBCC_DataReport SET [DatabaseName] = 'DB ' + @database_name WhERE [DatabaseName] IS NULL
       
      FETCH NEXT FROM database_cursor INTO @database_name
    END

CLOSE database_cursor
DEALLOCATE database_cursor

DECLARE @MSG NVARCHAR(MAX)
SET @MSG = (
SELECT
    TextData + CHAR(10) AS [text()]
FROM (
    SELECT
        Concat(DatabaseName, ': ', MessageText) as TextData
    FROM #DBCC_DataReport
    WHERE
        SUBSTRING(MessageText, 1, 45) like 'CHECKDB обнаружил [1-9]%'
        or SUBSTRING(MessageText, 1, 45) like 'CHECKDB обнаружил [0-9]%[1-9]%'
    ) AS ReportData
FOR XML PATH (''))

DROP TABLE #DBCC_DataReport

IF @MSG IS NOT NULL
BEGIN
    DECLARE @Profilename as nvarchar(100) = 'Main' -- Имя почтового профиля, для отправки электонной почты                
    DECLARE @Recipients as nvarchar(30) = 'mymail@ya.ru' -- Получатели сообщений электронной почты, разделенные знаком ";"
    DECLARE @Msubject nvarchar(20)= N'SQL SERVER. CHECKDB RESULT' -- Тема сообщения

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = @Profilename,
        @recipients = @Recipients,
        @body = @MSG,
        @subject = @Msubject;
END
GO

 

На этом все. Всем спасибо за внимание.

DBCC CHCKDB SQL ЦелостностьБазыДанных

См. также

Администрирование СУБД Программист Платформа 1С v8.3 Конфигурации 1cv8 Россия Бесплатно (free)

В статье описала свой опыт аудита 1С базы, порядок действий + статьи, которые сильно помогли в работе.

14.07.2024    5444    limonen    13    

19

Администрирование СУБД Платформа 1С v8.3 Россия Бесплатно (free)

Безопасное полное удаление пользователей из конфигураций 1С.

25.06.2024    878    It-digit    2    

1

Администрирование СУБД Системный администратор Платформа 1С v8.3 Бесплатно (free)

Ситуация: при обновлении серверной базы данных произошёл сбой и теперь невозможно войти ни в конфигуратор, ни в 1С:Предприятие по причине ошибки, вынесенной в заголовок. Рецепт лечения.

24.05.2024    1632    Kernelbug    9    

20

Администрирование СУБД Платформа 1С v8.3 Конфигурации 1cv8 Россия Бесплатно (free)

При хранении файлов в томах на диске они иногда исчезают. Разбираемся, почему.

23.05.2024    8303    human_new    18    

55

Администрирование СУБД Системный администратор Платформа 1С v8.3 Бесплатно (free)

Краткое описание шагов по установке, настройке и применению инструмента 1С:Исполнитель на операционной системе РедОС, а также по борьбе с некоторыми возникающими ошибками.

06.05.2024    870    artemusII    0    

1

Администрирование СУБД Системный администратор Программист Платформа 1С v8.3 Бесплатно (free)

Статья для тех, кто столкнулся с необходимостью пересчета итогов для "больших таблиц" и нет возможности поставить на паузу ИБ для проведения работ.

25.04.2024    2789    virustam    33    

12

Администрирование СУБД Системный администратор Платформа 1С v8.3 1С:Бухгалтерия 3.0 Россия Бесплатно (free)

Иногда в конфигурации 1С:Бухгалтерский учет v3.0 возникает ситуация, когда программа всем пользователям выдает предупреждение, что авторизация не выполнена и работа программы будет завершена. Данная инструкция позволяет решить возникшую проблему.

24.04.2024    1583    Yan_Malyakov    0    

3
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. TerveRus 20.05.20 16:40 Сейчас в теме
Спасибо!
Осталось понять что такое DBCC CHECKDB и что делать с оповещением)
2. Fox-trot 161 20.05.20 17:33 Сейчас в теме
SELECT name
FROM sys.databases db
WHERE name = 'Recovery'
   AND db.state_desc = 'ONLINE'
   AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
   AND is_read_only = 0

зачем столько уточнений? платят за количество буков? достаточно и
SELECT name
FROM sys.databases
WHERE name = 'Recovery'
3. itoptimum 24 19.06.20 11:41 Сейчас в теме
Спасибо, помогло в автоматической обработке результатов работы checkdb для исправления:
https://infostart.ru/public/1253058/
Оставьте свое сообщение