Monitor BizTalk Server (BizTalkMgmtDb) SQL Job Fails

A client was experiencing failures with the BizTalk job Monitor BizTalk Server (BizTalkMgmtDb), which calls stored procedure [dbo].[btsmon_Inconsistent]. This proc calls several other stored procedures to cleanup the messagebox and other meta-tables within BizTalk.

These procs generate dynamic SQL scripts and to run against the tables within the BizTalk databases. ?Depending on your configuration, you might receive errors when this job runs. ?We have several host instances within our configuration, which causes the generated SQL scripts to be quite large. ?The generated SQL script was overflowing the nvarchar variable used to hold the generated script. ?This caused us to get errors like:

Incorrect syntax near ‘FROM’. [SQLSTATE 42000] (Error 102). ?The step failed. ?

The primary proc that caused our problem was?[dbo].[btsmon_MessagesWithoutReferences] within the BizTalkMsgBoxDb. ?The variable used to store the generated script was of type nvarchar(4000). ?The generated script ?from our configuration caused the script’s length to exceed the 4000 characters allowed.

Here is the original script:

CREATE PROCEDURE [dbo].[btsmon_MessagesWithoutReferences]
@nIssues bigint output
AS
	declare @Host sysname
	declare @tsql nvarchar(4000)
	--Find Message without refcounts
	set @nIssues = 0
	set @tsql = N'Select @MessagesWithoutReferences = count(*) from [dbo].[Spool] (READPAST)
		WHERE [uidMessageID ] != N''50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3'' AND --Control Messages
		[uidMessageID ] != N''61EAA7FC-AC85-42D9-BF3E-1BED258B82BE'' AND
		[uidMessageID ] != N''57E5E753-0207-435D-8BE7-2B9F3C6556F9'' AND
		[uidMessageID ] != N''2BE3D5B8-5685-40F2-BD97-51ADA3D02347'' AND
		[uidMessageID] NOT IN (
			SELECT [uidMessageID] FROM [dbo].[TrackingMessageReferences]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLog1]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLog2]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLogTotals]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageZeroSum]
	'

	declare Host_Cursor insensitive cursor for
	select nvcApplicationName from dbo.Applications
	open Host_Cursor
	fetch next from Host_Cursor into @Host
	while @@fetch_status = 0
	begin
		set @tsql = @tsql + '
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q_Scheduled]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q_Suspended]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].[InstanceStateMessageReferences_'+ @Host +']
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'_MessageRefCountLog]
		'
		fetch next from Host_Cursor into @Host
	end
	close Host_Cursor
	deallocate Host_Cursor
	set @tsql = @tsql + ')'

	exec sp_executesql @tsql, N'@MessagesWithoutReferences bigint output', @MessagesWithoutReferences = @nIssues output

To resolve the issue, I changed the @tsql variable from nvarchar(4000) to nvarchar(max) on line 5.

Here is the adjusted script:

ALTER PROCEDURE [dbo].[btsmon_MessagesWithoutReferences]
@nIssues bigint output
AS
	declare @Host sysname
	declare @tsql nvarchar(max)
	--Find Message without refcounts
	set @nIssues = 0
	set @tsql = N'Select @MessagesWithoutReferences = count(*) from [dbo].[Spool] (READPAST)
		WHERE [uidMessageID ] != N''50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3'' AND
		[uidMessageID ] != N''61EAA7FC-AC85-42D9-BF3E-1BED258B82BE'' AND
		[uidMessageID ] != N''57E5E753-0207-435D-8BE7-2B9F3C6556F9'' AND
		[uidMessageID ] != N''2BE3D5B8-5685-40F2-BD97-51ADA3D02347'' AND
		[uidMessageID] NOT IN (
			SELECT [uidMessageID] FROM [dbo].[TrackingMessageReferences]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLog1]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLog2]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLogTotals]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageZeroSum]
	'

	declare Host_Cursor insensitive cursor for
	select nvcApplicationName from dbo.Applications
	open Host_Cursor
	fetch next from Host_Cursor into @Host
	while @@fetch_status = 0
	begin
		set @tsql = @tsql + '
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q_Scheduled]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q_Suspended]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].[InstanceStateMessageReferences_'+ @Host +']
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'_MessageRefCountLog]
		'
		fetch next from Host_Cursor into @Host
	end
	close Host_Cursor
	deallocate Host_Cursor
	set @tsql = @tsql + ')'

	exec sp_executesql @tsql, N'@MessagesWithoutReferences bigint output', @MessagesWithoutReferences = @nIssues output

I understand what Microsoft is doing, but I’m not a fan of the implementation, due to the problems when you have more host instances. ?I believe a better approach would have been to create stored procs for each host instance that accomplished the same task, generated the SQL scripts to call the stored proc and prevent the overflow from occuring.