Index / Check DB Script question

Question

I'm using Ola Hallengren's maintenance scripts and I wanted to see if I could create 2 tables so that it logs integrity checks and index optimizations to different tables.

Answer 1

Logging info to different tables is not possible without rewriting parts of Ola's solution and creating new objects.

By default, you can log the output to the dbo.CommandLog table by using @LogToTable = 'Y'

You can then query the recent outputs for the integrity checks with this command:

SELECT TOP (100) [ID]
 ,[DatabaseName]
 ,[SchemaName]
 ,[ObjectName]
 ,[ObjectType]
 ,[IndexName]
 ,[IndexType]
 ,[StatisticsName]
 ,[PartitionNumber]
 ,[ExtendedInfo]
 ,[Command]
 ,[CommandType]
 ,[StartTime]
 ,[EndTime]
 ,[ErrorNumber]
 ,[ErrorMessage]
 FROM [MNGDB].[dbo].[CommandLog]
 WHERE CommandType = 'DBCC_CHECKDB'
 ORDER BY EndTime desc;

You can then change the filter to check the index rebuilds / reorgs by changing the filter to WHERE CommandType = 'ALTER_INDEX'.

---

If you really want to change the tables, you could create a copy of the dbo.CommandLog table and create dbo.CommandLogIndex for example.

CREATE TABLE [dbo].[CommandLogIndex](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [DatabaseName] [sysname] NULL,
 [SchemaName] [sysname] NULL,
 [ObjectName] [sysname] NULL,
 [ObjectType] [char](2) NULL,
 [IndexName] [sysname] NULL,
 [IndexType] [tinyint] NULL,
 [StatisticsName] [sysname] NULL,
 [PartitionNumber] [int] NULL,
 [ExtendedInfo] [xml] NULL,
 [Command] [nvarchar](max) NOT NULL,
 [CommandType] [nvarchar](60) NOT NULL,
 [StartTime] [datetime] NOT NULL,
 [EndTime] [datetime] NULL,
 [ErrorNumber] [int] NULL,
 [ErrorMessage] [nvarchar](max) NULL,
 CONSTRAINT [PK_CommandLog2] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Afterwards you have to create a new procedure, a copy of the dbo.CommandExecute procedure to use the dbo.CommandLogIndex table. I use Replace All (ALT + A) in SSMS to change the procedure calls.

enter image description here

As a last step, you could create a copy of the procedure dbo.IndexOptimize with the name [dbo].[IndexOptimizeLogToTable] and change the dbo.CommandExecute procedure calls to dbo.CommandExecuteIndex.

enter image description here

Afterwards you run the procedure with @logtotable='Y'

 EXEC [dbo].[IndexOptimizeLogToTable] @databases = 'Test',@logtotable='Y'

And you should see the results in the new dbo.CommandLogIndex table.