Using Service Broker to Perform Multiple CheckDB’s at Once

I have lots of small databases on one SQL Server 2012 instance. The no. of databases on the server when I first started looking after it was 3 so setting up separate jobs for each CheckDB was no hassle. This soon got out of hand and so I had to come up with a better way of solving the problem.

The solution I first implemented used a table to store the database names of all the databases I want to run CheckDB against. There was then a stored proc that made use of a cursor to execute a dynamic bit of SQL against each of the databases names held in the table. All we needed to do to run a CheckDB against a new database was insert the new name into the table. Easy peasy. Well that was great but to get through all the databases in this serial fashion was taking too long. I wanted to multi thread this process and then bingo this article appeared on SQL Server Central http://www.sqlservercentral.com/articles/Service+Broker/76715/

I thought, I know, I can use the concepts from this article (and large bits of the code admittedly) to achieve what I wanted.

So here it is, a multi threadable service broker CheckDB solution. I should start off by saying that I have a DBA database that I use to store routines of this nature, I would advise you create one too to keep your maintenance scripts out of production databases.

First things first, let’s enable Service Broker and allow our DBA databases to run operations against other databases:


ALTER DATABASE [DBA] SET ENABLE_BROKER;

GO

ALTER DATABASE [DBA] SET TRUSTWORTHY ON;

GO

Then you’ll need a table to store your database names in….:


CREATE TABLE [dbo].[CheckDBDatabases](

[name] [sysname] NOT NULL

)

…. and a log table so that we can keep track of how long this is taking….:


CREATE TABLE [dbo].[CheckDBLog](

[CheckDBLogID] [int] IDENTITY(1,1) NOT NULL,

[ReqDlgHandle] [uniqueidentifier] NOT NULL,

[ReqMsg] [xml] NOT NULL,

[SQLText] [varchar](200) NOT NULL,

[dbName] [sysname] NOT NULL,

[CheckDBStarted] [datetime] NOT NULL,

[CheckDBFinished] [datetime] NULL,

CONSTRAINT [PK_CheckDBLog] PRIMARY KEY CLUSTERED

(

[CheckDBLogID] 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]

….and an error table incase Service Broker runs into a problem:


CREATE TABLE [dbo].[ServiceBroker_Errors](

[ServiceBroker_ErrorsID] [int] IDENTITY(1,1) NOT NULL,

[ErrorMessage] [nvarchar](max) NOT NULL,

[ErrorDateTime] [datetime] NOT NULL,

CONSTRAINT [PK_ServiceBroker_Errors] PRIMARY KEY CLUSTERED

(

[ServiceBroker_ErrorsID] 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]

Now for the Service Broker objects. First of all you’ll need to create two message types:


CREATE MESSAGE TYPE [//DBA/ReplyMessage] VALIDATION = WELL_FORMED_XML

CREATE MESSAGE TYPE [//DBA/RequestMessage] VALIDATION = WELL_FORMED_XML

Then a contract:


CREATE CONTRACT [//DBA/Contract] ([//DBA/ReplyMessage] SENT BY TARGET,

[//DBA/RequestMessage] SENT BY INITIATOR)

Then we need some procedures to do some work. This first one will receive an incoming message and perform the CheckDB:


CREATE PROCEDURE [dbo].[CheckDB_internalActivationTarget]

AS

SET NOCOUNT ON;

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;

DECLARE @RecvReqMsg XML;

DECLARE @RecvReqMsgName SYSNAME;

DECLARE @dbName SYSNAME;

DECLARE @sql VARCHAR(200);

BEGIN TRY

WHILE (1=1)

BEGIN

WAITFOR

( RECEIVE TOP(1)

@RecvReqDlgHandle = conversation_handle,

@RecvReqMsg = message_body,

@RecvReqMsgName = message_type_name

FROM CheckDB_TargetQueue

), TIMEOUT 5000;

IF (@@ROWCOUNT = 0)

BEGIN

BREAK;

END

IF @RecvReqMsgName = N''//DBA/RequestMessage''

BEGIN

DECLARE @ReplyMsg NVARCHAR(500);

SELECT

@dbName = @RecvReqMsg.value(''(/Request/Database)[1]'', ''SYSNAME'');

SET @sql = N''DBCC CHECKDB('''''' + @dbName + '''''') WITH NO_INFOMSGS, ALL_ERRORMSGS'';

INSERT INTO [dbo].[CheckDBLog]

(

[ReqDlgHandle]

,      [ReqMsg]

,      [SQLText]

,      [dbName]

,      [CheckDBStarted]

)

SELECT

@RecvReqDlgHandle,

@RecvReqMsg,

@sql,

@dbName,

GETDATE();

EXEC (@sql);

UPDATE [dbo].[CheckDBLog]

SET [CheckDBFinished] = GETDATE()

WHERE [ReqDlgHandle] = @RecvReqDlgHandle

SELECT @ReplyMsg = N''<ReplyMsg>DBCC CheckDB has been completed on '' + @dbName + ''</ReplyMsg>'';

SEND ON CONVERSATION @RecvReqDlgHandle

MESSAGE TYPE

[//DBA/ReplyMessage]

(@ReplyMsg);

END

ELSE IF @RecvReqMsgName =

N''http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog''

BEGIN

END CONVERSATION @RecvReqDlgHandle;

END

ELSE IF @RecvReqMsgName =

N''http://schemas.microsoft.com/SQL/ServiceBroker/Error''

BEGIN

END CONVERSATION @RecvReqDlgHandle;

END

END

END TRY

BEGIN CATCH

DECLARE @ErrorMessage VARCHAR(MAX);

SET @ErrorMessage = ''ErrorNumber - '' + CONVERT(VARCHAR(100), ERROR_NUMBER()) + '' : ErrorMessage - '' + ERROR_MESSAGE()

INSERT INTO [dbo].[ServiceBroker_Errors]

(

[ErrorMessage],

[ErrorDateTime]

)

VALUES

(

@ErrorMessage,

GETDATE()

);

END CATCH

Now that’s done and the reply has been sent to say we’ve done the CheckDB we need to have a procedure that will clear up the Service Broker conversation:


CREATE PROCEDURE [dbo].[CheckDB_internalActivationInitiator]

AS

SET NOCOUNT ON;

DECLARE @RecvReplyMsg NVARCHAR(500);

DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

WHILE (1=1)

BEGIN

BEGIN TRANSACTION;

WAITFOR

( RECEIVE TOP(1)

@RecvReplyDlgHandle = conversation_handle,

@RecvReplyMsg = message_body

FROM CheckDB_InitiatorQueue

), TIMEOUT 5000;

IF (@@ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END

END CONVERSATION @RecvReplyDlgHandle;

COMMIT TRANSACTION;

END

Ok. So we’ve got two out of the 3 needed stored procedures. We need to create some message queues to receive the messages:


CREATE QUEUE [dbo].[CheckDB_InitiatorQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[CheckDB_internalActivationInitiator] , MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo'  ), POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY]

CREATE QUEUE [dbo].[CheckDB_TargetQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[CheckDB_internalActivationTarget] , MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo'  ), POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY]

And the final Service Broker objects (the services):


CREATE SERVICE [//DBA/CheckDB/InitiatorService]  ON QUEUE [dbo].[CheckDB_InitiatorQueue]

CREATE SERVICE [//DBA/CheckDB/TargetService]  ON QUEUE [dbo].[CheckDB_TargetQueue] ([//DBA/Contract])

And now we need the final procedure that creates the XML to start the whole process off:


CREATE PROCEDURE [dbo].[CheckDB_serviceBroker_beginDialog]

AS

SET NOCOUNT ON;

DECLARE @dbName SYSNAME;

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

DECLARE @RequestMsg NVARCHAR(500);

-- Select the list of databases into a #table

SELECT

*

INTO #temp

FROM

dbo.CheckDBDatabases

WHILE (SELECT COUNT(*) FROM #temp) > 0

BEGIN

SELECT TOP 1 @dbName = name FROM #temp

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle

FROM SERVICE [//DBA/CheckDB/InitiatorService]

TO SERVICE N''//DBA/CheckDB/TargetService''

ON CONTRACT [//DBA/Contract]

WITH ENCRYPTION = OFF;

SELECT @RequestMsg = N''<Request><Database>'' + @dbName + N''</Database></Request>'';

SEND ON CONVERSATION @InitDlgHandle

MESSAGE TYPE [//DBA/RequestMessage] (@RequestMsg);

DELETE FROM #temp WHERE name = @dbName

COMMIT TRANSACTION;

END

So, now that that’s all created (hopefully). We can put some databases names in our table and then all we have to do to start the Check DB process off is this:


EXEC [dbo].[CheckDB_serviceBroker_beginDialog]

With Service Broker now running these Check DB’s in the background, we can use Adam Machanic’s sp_whoisactive script to see all the processes running.

screenshot

And then you can check the CheckDBLog table we created at the start to see start and finish times for each DB.

I hope you have found this useful. Using this approach I have halved the time it took to perform my CheckDB’s. I accept that there is an increase in IO on the system but I am willing to accept that hit for decreased total CheckDB time. This is something I’ve implemented on one of my servers. That’s not to say it’s suitable to implement on yours and so I advise that you carry out your own testing before implementing it.