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

Сбор данных

Для начала нам нужно создать таблицу для хранения вывода DBCC CHECKDB. Структура таблицы будет зависеть от версии SQL Server, которую вы используете. Ниже приведены структуры таблиц для SQL Server 2005, 2008 и 2008 R2:

CREATE TABLE [dbo].[dbcc_history](
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [int] NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [int] NULL,
[IndId] [int] NULL,
[PartitionID] [int] NULL,
[AllocUnitID] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[TimeStamp] [datetime] NULL CONSTRAINT [DF_dbcc_history_TimeStamp] DEFAULT (GETDATE())
) ON [PRIMARY]

Затем мы создадим хранимую процедуру с именем ‘usp_CheckDBIntegrity’, которая будет выполнять команду DBCC CHECKDB и вставлять результаты в таблицу ‘dbcc_history’. Хранимая процедура принимает необязательный параметр для указания конкретной базы данных, в которой нужно выполнить команду. Если база данных не указана, команда будет выполняться для всех баз данных на сервере.

CREATE PROC [dbo].[usp_CheckDBIntegrity]
@database_name SYSNAME=NULL
AS
IF @database_name IS NULL -- Выполнить для всех баз данных
BEGIN
   DECLARE database_cursor CURSOR FOR
   SELECT name 
   FROM sys.databases db
   WHERE name NOT IN ('master','model','msdb','tempdb') 
   AND db.state_desc = 'ONLINE'
   AND source_database_id IS NULL -- ТОЛЬКО РЕАЛЬНЫЕ БД (не снимки)
   AND is_read_only = 0

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

      INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status], 
      [DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage, 
      RefSlot,Allocation)
      EXEC ('dbcc checkdb(''' + @database_name + ''') with tableresults')

      FETCH next FROM database_cursor INTO @database_name
   END

   CLOSE database_cursor
   DEALLOCATE database_cursor
END 

ELSE -- Выполнить для указанной базы данных (например, usp_CheckDBIntegrity 'Имя БД')

   INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status], 
   [DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage, RefSlot,Allocation)
   EXEC ('dbcc checkdb(''' + @database_name + ''') with tableresults')

Анализ данных

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

EXEC usp_CheckDBIntegrity 'МояБаза' -- указывает конкретную базу данных, в противном случае ВСЕ БД

Чтобы запланировать выполнение команды DBCC CHECKDB регулярно, вы можете создать задание SQL Server Agent. В шаге задания укажите базу данных, где вы создали хранимую процедуру, и выполните хранимую процедуру с желаемым именем базы данных в качестве параметра. Запланируйте выполнение задания во время обслуживания.

После завершения задания вы можете запросить таблицу ‘dbcc_history’ для анализа собранных данных. Например, вы можете получить номер ошибки, уровень серьезности, имя базы данных и объекта, а также временную метку каждого выполнения DBCC CHECKDB.

SELECT Error, LEVEL, DB_NAME(dbid) AS DBName, OBJECT_NAME(id,dbid) AS ObjectName, Messagetext, TimeStamp
FROM dbcc_history

Вы можете уточнить свой запрос, чтобы отфильтровать данные на основе конкретных критериев, таких как ошибка, база данных, временной диапазон или объект. Например, чтобы получить общий результат команды DBCC CHECKDB, вы можете выполнить запрос ‘WHERE Error = 8989’.

SELECT Error, LEVEL, DB_NAME(dbid) AS DBName, Messagetext, TimeStamp
FROM dbcc_history
WHERE Error = 8989

Захватывая и сохраняя вывод DBCC CHECKDB в пользовательской таблице, вы можете легко анализировать историю целостности базы данных и выявлять повторяющиеся проблемы или тенденции. Эта информация может быть бесценной для поддержания здоровья и стабильности ваших баз данных SQL Server.