sys.conversation_endpoints not purged

I desperately need help with service broker. I have created a simple 
application (code below), which uses a message queue to send messages from 
the initiator to the target. No response is required (I need a fire and 
forget strategy here). 

Although both sides of the conversation call END CONVERSATION, the table 
sys.conversation_endpoints is not purged and keeps filling up forever. SQL 
Server books online recommend using END CONVERSATION WITH CLEANUP for 
administrative purposes only. Can anyone please show me a way to properly end 
conversations in a "normal" way? I have not been able to find one.

Code follows:

USE [master]
GO
/****** Object:  Database [BrokerTest]    Script Date: 06/09/2010 16:03:27 
******/
CREATE DATABASE [BrokerTest] ON  PRIMARY 
( NAME = N'BrokerTest', FILENAME = N'C:\Program Files\Microsoft SQL 
Server\MSSQL.2\MSSQL\DATA\BrokerTest.mdf' , SIZE = 9920KB , MAXSIZE = 
UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'BrokerTest_log', FILENAME = N'C:\Program Files\Microsoft SQL 
Server\MSSQL.2\MSSQL\DATA\BrokerTest_log.ldf' , SIZE = 16576KB , MAXSIZE = 
2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [BrokerTest] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [BrokerTest].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [BrokerTest] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [BrokerTest] SET ANSI_NULLS OFF
GO
ALTER DATABASE [BrokerTest] SET ANSI_PADDING OFF
GO
ALTER DATABASE [BrokerTest] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [BrokerTest] SET ARITHABORT OFF
GO
ALTER DATABASE [BrokerTest] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [BrokerTest] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [BrokerTest] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [BrokerTest] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [BrokerTest] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [BrokerTest] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [BrokerTest] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [BrokerTest] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [BrokerTest] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [BrokerTest] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [BrokerTest] SET  ENABLE_BROKER
GO
ALTER DATABASE [BrokerTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [BrokerTest] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [BrokerTest] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [BrokerTest] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [BrokerTest] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [BrokerTest] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [BrokerTest] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [BrokerTest] SET  READ_WRITE
GO
ALTER DATABASE [BrokerTest] SET RECOVERY FULL
GO
ALTER DATABASE [BrokerTest] SET  MULTI_USER
GO
ALTER DATABASE [BrokerTest] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [BrokerTest] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'BrokerTest', N'ON'
GO
USE [BrokerTest]
GO
/****** Object:  StoredProcedure [dbo].[ReadMe]    Script Date: 06/09/2010 
16:03:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ReadMe]
AS
BEGIN
	SET NOCOUNT ON;
	
	PRINT 'Tut nix, speichert nur Doku in der Datenbank'

/*

exec dbo.RaiseTestMessage 'Type1', '<test>Message</test>'
select queuing_order, service_name, message_body = CAST(message_body as xml) 
from dbo.TestMessageQueue where validation = 'X'
select * from dbo.MsgDebug order by id

ALTER QUEUE [dbo].[TestMessageQueue] WITH STATUS = ON , RETENTION = OFF , 
ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[TestMessageQueueHandler] 
, MAX_QUEUE_READERS = 3 , EXECUTE AS N'dbo'  )
ALTER QUEUE [dbo].[TestMessageQueue] WITH STATUS = ON , RETENTION = OFF , 
ACTIVATION (  STATUS = OFF , PROCEDURE_NAME = [dbo].[TestMessageQueueHandler] 
, MAX_QUEUE_READERS = 3 , EXECUTE AS N'dbo'  )
truncate table dbo.MsgDebug


exec dbo.BatchRaiseTestMessage 3

*/

END
GO
/****** Object:  StoredProcedure [dbo].[RaiseTestMessage]    Script Date: 
06/09/2010 16:03:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RaiseTestMessage]
	@Name				NVARCHAR(32),
	@DataBlock			XML = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @dlg		UNIQUEIDENTIFIER, 
			@x			XML;

	SET @x = CASE WHEN @DataBlock is null 
				  THEN '<TestMessage/>' 
				  ELSE '<TestMessage>' + cast(@DataBlock as nvarchar(max)) + 
'</TestMessage>' 
				  END;

	SET @x.modify(N'insert <name>{sql:variable("@Name")}</name> into 
/TestMessage[1]');

	print 'Sending message: ' + cast(@x as nvarchar(max));

	BEGIN DIALOG @dlg
	FROM SERVICE TestMessageProducer
	TO	 SERVICE 'TestMessageConsumer'
	ON   CONTRACT TestMessageContract
	WITH ENCRYPTION=OFF;

	SEND ON CONVERSATION @dlg MESSAGE TYPE TestMessageType(@x);

	END CONVERSATION @dlg;
END
GO
/****** Object:  Table [dbo].[MsgDebug]    Script Date: 06/09/2010 16:03:30 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MsgDebug](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[dt] [datetime] NOT NULL,
	[c_handle] [uniqueidentifier] NULL,
	[m_body] [xml] NULL,
	[m_type] [nvarchar](128) NULL,
	[a_id] [int] NULL,
 CONSTRAINT [PK_MsgDebug] 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]
GO
/****** Object:  StoredProcedure [dbo].[TestMessageQueueHandler]    Script 
Date: 06/09/2010 16:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------------------------
-- set up event queue handler
----------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[TestMessageQueueHandler]
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE	@conversation_handle UNIQUEIDENTIFIER,
			@message_body XML,
			@message_type_name NVARCHAR(128);

	RECEIVE TOP(1)
		@conversation_handle = conversation_handle,
		@message_type_name = message_type_name,
		@message_body = (CASE WHEN validation = 'X' 
							  THEN CAST(message_body as XML)
							  ELSE NULL
							  END)
	FROM dbo.TestMessageQueue;

	IF @@ROWCOUNT = 0
		RETURN;

	IF @message_type_name IN 
(N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog', 
							  N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
	BEGIN
		INSERT dbo.MsgDebug (a_id, c_handle, m_body, m_type, dt)
		VALUES (0, @conversation_handle, 'ending dialog', @message_type_name, 
GETDATE());
		
		END CONVERSATION @conversation_handle;
		RETURN;
	END

	-- extract message parameters
	INSERT dbo.MsgDebug (a_id, c_handle, m_body, m_type, dt)
	VALUES (0, @conversation_handle, @message_body, @message_type_name, 
GETDATE());

	END CONVERSATION @conversation_handle;
END
GO
/****** Object:  StoredProcedure [dbo].[BatchRaiseTestMessage]    Script 
Date: 06/09/2010 16:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BatchRaiseTestMessage]
	@BatchSize	INT = 1
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @cnt INT, @name NVARCHAR(64), @x XML
	SET @cnt = 0
	WHILE @cnt < @BatchSize
	BEGIN
		SET @cnt = @cnt + 1
		SET @name = 'Name ' + CAST(@cnt AS NVARCHAR)
		SET @x = '<test>Message ' + CONVERT(NVARCHAR, GETDATE(), 114) + '</test>'
		EXEC dbo.RaiseTestMessage @name, @x
	END
END
GO

0
Utf
6/9/2010 2:34:02 PM
sqlserver.programming 1873 articles. 0 followers. Follow

0 Replies
649 Views

Similar Articles

[PageSpeed] 21

Reply:

Similar Artilces:

sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests
Hello All, I was going through the DMVs in SQLServer 2005 to figure out how these views are related and better understand performance optimization. I have a question about the same: I was checking the data returned by sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests. Looks like, sys.dm_exec_connections, returns data about the physical connections such as Client protocol, version, encryption, net packet size ..... etc. Basically these are details that does not change for a connection till the connection is disconnected and reconnected. The sys.dm_ex...

Installing Exchange Sys Manager on Win2k Pro
Hi Everyone, I cannot get the Excahnge 2k Sys Manager installed on Win2k Pro. I have installed the AdminPak.msi. I receive the following messages in the Exchange log: regsvr32.exe -s imapadm.dll failed, returning error code 5 (Access is denied.). ScCreateProcess (K:\admin\src\libs\exsetup\hiddenw1.cxx:1763) Error code 0XC103798A (31114): An internal component has failed. [10:29:04] CInsParser::ScProcessLine (K:\admin\src\libs\exsetup\hiddenw1.cxx:1226) Error code 0XC103798A (31114): An internal component has failed. [10:29:04] Processing file 'd:\setup\i386\...

sys.dm_exec_query_stats
Is there a way to tell which spid, and in turn from which ip, ran a last query found in sys.dm_exec_query_stats? Thanks RG (nobody@nowhere.com) writes: > Is there a way to tell which spid, and in turn from which ip, ran a last > query found in sys.dm_exec_query_stats? No. That spid may have disconnected since it rant the query. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000...

newbie-sys.dm_db_index_physical_stats
Hello, where can I find this function? I'm looking int system view, but I can't find this function... Can anyone tell me the right way? Thanks in advance Hi Under master database, Functions---SystemFunctions----Table-Valued Functions "Alciabide" <alces@nothing.no> wrote in message news:4b4ede2d$0$1113$4fafbaef@reader2.news.tin.it... > Hello, where can I find this function? > I'm looking int system view, but I can't find this function... > Can anyone tell me the right way? > Thanks in advance > > ...

What processor(sys requirements) should my computer have to run the following:
Hello I have developed a series of reports using pivot tables, multiple vlookup functions. Example: Report 1 has a database of 20000plus records, from which I created a pivot table. In addition, I have 2 columns which return values using a vlookup everytime I refresh the pivot table. These Vlookup formulas read information from another database, but this database if for 2000 records only My computer spends around 5 minutes calculaing everytime a single change is done (it often crashes) In addition, my job requires me to have open applications as Outlook, and Oracle(manufacturing system...

sys admin privileges
is there a way to restore the original privileges for the sys admin. The ability to override order pricing was unchecked and now we cannot get it back. (It just gives us an error message when we check it and save it.) ...

tdidis32.sys?????????????????
Ok so about 5 days ago my computer blue screened on me while I was surfing the internet. Rebooted it and now it blue screens on me every time. I restart the computer and after putting my password in it show's my desktop for about 4 seconds and blue screens. Here is what it says. This problem seems to be caused to file tdidis32.sys. I looked it up and I guess it�s a Trojan. Page_fault_in_nonpaged_area.. Stop: 0x00000050 (0x81000078, 0x00000000, 0xad49c3fb, 0x00000000.) Tdidis32.sys- address ad49c3f8, base at ad49b000, date stamp 4af69b2e. I can�t start my system in safe ...

STOP: 0x00000050 pci.sys
STOP: 0x00000050 (0xFA3A9BC4,0x00000000,0xF84F3AB9,0x00000000) pci.sys - Address F84F3AB9 base at F84F1000, datestamp 480252bb This is the error im getting when computer starts. It wont even go into safe mode by pressing F8 on start up. Its a 3.6ghz with 512mb ram running xp. Recently had a virus on it but was cleared off. So i was told it was cleared off. Any Ideas? -- JPKaos ------------------------------------------------------------------------ JPKaos's Profile: http://forums.techarena.in/members/156350.htm View this thread: http://forums.techarena.in/windows-x...

Replacing USBSTOR.sys
Is it possible to replace USBSTOR.sys? How can we start developing driver to replace USBSTOR.sys? What are the responsibilities of USBSTOR.sys that must be fulfilled to be able to replace it? > Is it possible to replace USBSTOR.sys? ImagePath registry value. > What are the responsibilities of USBSTOR.sys that must be fulfilled to = be=20 > able to replace it? Conversion of SCSI CDBs to USBSTOR URBs Enumeration of the child devnode for the storage LUN Supporting all IOCTLs the storage port driver should --=20 Maxim S. Shatskih Windows DDK MVP maxim@storagecraf...

delete hiberfil.sys?
Can I stop Vista from creating/using this file? I don't use hibernate mode. Type in search on the Start menu "command" and in the list of options right click Command Prompt and choose Run as administrator. Type powercfg -h off -- .. -- "+Bob+" <nomailplease@example.com> wrote in message news:rsgis597a9lksk3m2jau9ql6umd7f3919m@4ax.com... > Can I stop Vista from creating/using this file? I don't use hibernate > mode. > > On Sat, 17 Apr 2010 15:46:44 +1000, <.> wrote: >Type in search on the Start menu "c...

Windows Server 2003 BSOD "Apparantley Caused by Win32k.sys Help!!!
Hi All, One of my Windows Server 2003 Enterprise Server Running SP2 Will Most Certainly BSOD at least once a day (Usually Occuring at Night) I have done scan's of the memory Dumps and they all point to the win32k.sys file. This is a Virtual Machine BTW running on ESX 3.5. Here are the Error Codes.... System Failure : Stop Error 0x00000050 (0xbc529e90, 0x00000001, 0xbf8b8006, 0x00000000) Error Signature BCCode : 50 BCP1 : bc529e90 BCP2 : 00000001 MCP3 BF8B8006 BCP4 000000 OSVER : 5_2_3790 SP : 2_0 PRODUCT 18_3 I have Search to ends earth and ...

sys.dm_db_index_physical_stats
I'm looking for a good, indepth, writeup on sys.dm_db_index_physical_stats. Jay Have you read BOL? "Jay" <spam@nospam.org> wrote in message news:OEzcUCukKHA.2468@TK2MSFTNGP04.phx.gbl... > I'm looking for a good, indepth, writeup on > sys.dm_db_index_physical_stats. > Yes, however, it clearly gets much more involved. "Uri Dimant" <urid@iscar.co.il> wrote in message news:e8hqYR2kKHA.5568@TK2MSFTNGP02.phx.gbl... > Jay > Have you read BOL? > > "Jay" <spam@nospam.org> wrote in message >...

Serscan.sys
I am installing my Brother wireless printer (MFC-495CW) and I encountered the following "Files Needed" pop up window. "The file 'serscan.sys' on Windows XP Home Edition CD-ROM is needed'. Type the path where the file is located and then click OK" I installed the OS CDROM that Dell gave me and nothing works. I also tried to find the file itself in the C drive under C:\\Windows\Driver Cache|I386 and still nothing. Does anyone know where I can this file? Thanks "rma2113" <rma2113@discussions.microsoft.com> wrote in message news:3F...

Workflow Logging
The following tables relating to workflow are huge especially the WFRule log, these three tables are over 90% of the <organisation_name>_MSCRM database file. dbo.WFActionLog dbo.WFProcessInstance dbo.WFRuleLog Is there any way to purge the workflow logs rather than 1000 at a time? I'm sure other CRM administrators must be having a similar problem! Thanks James Isn' there anyone that can help me? What about you MVP guys? Thanks James You could probably write a query to do this yourelf, but you will need to race the calls exectued when you purge them. -- Matt Parks MVP...

sys.dm_db_index_operational_stats
I am a .net programmer, but sometimes get SQL Server DBA tasks as we don't have a DBA. There is a situation where the database seems to lock up occasionally. Below is a row from sys.dm_db_index_operational_stats, where the row_lock_wait_in_ms is very high. Can you please give me some clues as to what would cause this? Also, can you please give me a straightforward reference that would help someone like myself who is not a DBA by trade as to what best practices are to maintain a database? Thanks. database_id 15 object_id ...

messages are automatically purge
I'm using Outlook 2003 SP1 My outlook is configured with IMAP folders. When I delete a message the message is deleted for good. Meaning it's not put on the Deleted Items. It's gone completely. I want to turn off this feature. How do I do that? If you need anymore information let me know. Thanks in advance Vincent Sounds like you have the "Hide Messages Marked for Deletion" view turned on for that folder. Outlook's IMAP component does not send messages to Deleted Items when you delete them. Instead, it marks the for deletion in the original folder. You perform p...

sys.database_files
Can I increase the database size in Express08 by updating the sys. database_files table? any place else I would need to update? Thanks! Are you asking for the hack that allows you to get around the size limitation of the free SQL Server version? "Miss1868" <u57228@uwe> wrote in message news:a196816e83b90@uwe... > Can I increase the database size in Express08 by updating the sys. > database_files table? any place else I would need to update? > > Thanks! > You can’t go past the rated size, period. -- Andrew J. Kelly SQL MVP Soli...

Sys Manager
On my exchange server I had System manager open. I went to the server today and had a few error messages come up - A local error has occured Facility: Win32 ID No: 8007203b Exchange System Manager When I closed the error windows and closed sys manager I then tried to re-open it and got this message - Access denied Dacility: Win32 ID No: c0070005 Exchange System manager I have looked on the microsoft site nad cannot find naything to explain these error messages. I can now not open sys man and it looks like I'm going to have to reboot . Any ideas? ...

Purge Deleted Messages does not Work!
Hi, I'm using Outlook 2002 to connect to an IMAP server, however recently the Purge Deleted Message is failing to work. It asks if I'm sure then just doesn't to anything?! Any ideas? stevie ...

error with sys.dm_db_index_physical_stats
When I run this on the following database/table (or any table in the "Caser" database for that matter): select * from sys.dm_db_index_physical_stats (DB_ID(N'Caser'), OBJECT_ID(N'dbo.tblJudges'), NULL, NULL, NULL) I get the error: Incorrect syntax near '(' But if I run it on a different table in a different database (something in Adventureworks for example or one of my own other databases) it works correctly. Owner of the above table is dbo. I ran PRINT DB_ID(N'Caser') and PRINT OBJECT_ID(N'dbo.tblJudges') and they b...

sys.dm_db_missing_index_*
Have the sys.dm_db_missing_index_* DMV's proven to be useful? Yes, I find them very useful. But consider the limitations described by Bart: http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx -- Plamen Ratchev http://www.SQLStudio.com ...

sys menu problem in MDI
I have an MDI with 3 childwnd. Whenever group of views from chiuldwnd type X is shown the childwnd Y views and childwnd Z views are hidden via ShowWindow(SW_HIDE) when changing the activate frame. it works. But when I press the minimize box on the ChildWnd sys menu, i C all the views no matter what type are them. I want that when I press the system menu the other views types will still be hidden and the views of the vurrent type will be shown as minimize, how di I do it ? 10x. J. ...

HookCentre.sys
Good Morning, I've some problems with a File named HookCentre.sys because it causes a blue screen..!!! Can somebody help me?? Thanks Christian Jehle Christian Jehle <spammichdochvoll@gmx.de> wrote: >Good Morning, > >I've some problems with a File named HookCentre.sys because it causes a >blue screen..!!! Can somebody help me?? G-DATA Anti-Virus Kit? http://www.chip.de/c1_forum/thread.html?bwthreadid=851546 -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm Don't send mail to this address mailto:h.pott@get...

Delete pagefile.sys
I want to delete my pagefile and can not do Chanding to no page file and rebooting does not delete the page file tried everything , when I tried to delete it , it tell some other program is using it Tried deleting it in safe mode no good. There must be a away to delete I can scan for it and it shows where it is. But I can not delete it. Thanks Bob The page file is part of the operating system and cannot be deleted. --- Leonard Grey Errare humanum est Bob wrote: > I want to delete my pagefile and can not do > Chanding to no page file and rebooting does not delete the...

Offline Fulltext Catalog
Hi, I have inherited a few SQL servers with a number of databases all exhibiting the same problem. They all had a missing Fulltext Catalog file which was causing errors on backup. I sorted most of them by dropping the the full text catalog using DROP FULLTEXT CATALOG. However 6 of the databases report no fulltext catalogs exists even though sys.database_files has a entry for a fulltext catalog called sysft_DatabaseName on drive S:. Neither DROP FULLTEXT CATALOG or ALTER DATABASE REMOVE FILE work. 5 of these I managed to fix by creating and then deleting a new fulltext...