Всем доброго времени суток!
Сразу оговорюсь, что мои познания в 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', использую для разных целей. Сегодя она играет роль поврежденной базы данных. Результ ее проверки и поместим во временную таблицу.
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
На этом все. Всем спасибо за внимание.
