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.
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.
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
Afterwards you run the procedure with
EXEC [dbo].[IndexOptimizeLogToTable] @databases = 'Test',@logtotable='Y'
And you should see the results in the new