sqltabledependency trigger on my table “locks” all upcoming DML operations

[hdamis]

Hi, I enable Service Broker on my sql server 2012 to use sqltabledependency in vb.net application to notify me on update a field. but recently I saw a trigger on my table which "locks" all upcoming DML operations to this table. the error message is when I trying to update the field " No row was updated. Error Source: .Net SqlClient Data provider. Error Message: Cannot find object ID 467661280 in database ID 49. Correct the errors and retry or press ESC to cancel the change(s). I disable the trigger named(tr_dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender) and the locks goes. so why this happen and how to avoid it again. below is the trigger which automatically generated. USE [db0001] GO /** Object: Trigger [dbo].[tr_dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender] Script Date: 02/05/2021 6:28:58 PM **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[tr_dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender] ON [dbo].[Appointments] WITH EXECUTE AS SELF AFTER insert, update, delete AS BEGIN SET NOCOUNT ON;

DECLARE @rowsToProcess INT
DECLARE @currentRow INT
DECLARE @records XML
DECLARE @theMessageContainer NVARCHAR(MAX)
DECLARE @dmlType NVARCHAR(10)
DECLARE @modifiedRecordsTable TABLE ([RowNumber] INT IDENTITY(1, 1), [UniqueID] int, [lngContact] nvarchar(50), [StartDate] smalldatetime, [Status] int)
DECLARE @exceptTable TABLE ([RowNumber] INT, [UniqueID] int, [lngContact] nvarchar(50), [StartDate] smalldatetime, [Status] int)
DECLARE @deletedTable TABLE ([RowNumber] INT IDENTITY(1, 1), [UniqueID] int, [lngContact] nvarchar(50), [StartDate] smalldatetime, [Status] int)
DECLARE @insertedTable TABLE ([RowNumber] INT IDENTITY(1, 1), [UniqueID] int, [lngContact] nvarchar(50), [StartDate] smalldatetime, [Status] int)
DECLARE @var1 int
DECLARE @var2 nvarchar(50)
DECLARE @var3 smalldatetime
DECLARE @var4 int

DECLARE @conversationHandlerExists INT
SELECT @conversationHandlerExists = COUNT(*) FROM sys.conversation_endpoints WHERE conversation_handle = '871bf209-30ab-eb11-8cba-c8d9d20cebfa';
IF @conversationHandlerExists = 0
BEGIN
    DECLARE @conversation_handle UNIQUEIDENTIFIER;
    DECLARE @schema_id INT;
    SELECT @schema_id = schema_id FROM sys.schemas WITH (NOLOCK) WHERE name = N'dbo';

    IF EXISTS (SELECT * FROM sys.triggers WITH (NOLOCK) WHERE object_id = OBJECT_ID(N'[dbo].[tr_dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender]')) DROP TRIGGER [dbo].[tr_dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender];

    IF EXISTS (SELECT * FROM sys.service_queues WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender') EXEC (N'ALTER QUEUE [dbo].[dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender] WITH ACTIVATION (STATUS = OFF)');

    SELECT conversation_handle INTO #Conversations FROM sys.conversation_endpoints WITH (NOLOCK) WHERE far_service LIKE N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_%' ORDER BY is_initiator ASC;
    DECLARE conversation_cursor CURSOR FAST_FORWARD FOR SELECT conversation_handle FROM #Conversations;
    OPEN conversation_cursor;
    FETCH NEXT FROM conversation_cursor INTO @conversation_handle;
    WHILE @@FETCH_STATUS = 0 
    BEGIN
        END CONVERSATION @conversation_handle WITH CLEANUP;
        FETCH NEXT FROM conversation_cursor INTO @conversation_handle;
    END
    CLOSE conversation_cursor;
    DEALLOCATE conversation_cursor;
    DROP TABLE #Conversations;

    IF EXISTS (SELECT * FROM sys.services WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Receiver') DROP SERVICE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Receiver];

    IF EXISTS (SELECT * FROM sys.services WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender') DROP SERVICE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender];

    IF EXISTS (SELECT * FROM sys.service_queues WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Receiver') DROP QUEUE [dbo].[dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Receiver];

    IF EXISTS (SELECT * FROM sys.service_queues WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender') DROP QUEUE [dbo].[dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_Sender];

    IF EXISTS (SELECT * FROM sys.service_contracts WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3') DROP CONTRACT [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3];

    IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartMessage/Insert') DROP MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartMessage/Insert];
    IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartMessage/Update') DROP MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartMessage/Update];
    IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartMessage/Delete') DROP MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartMessage/Delete];
    IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/UniqueID') DROP MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/UniqueID];
    IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/lngContact') DROP MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/lngContact];
    IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartDate') DROP MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartDate];
    IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/Status') DROP MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/Status];
    IF EXISTS (SELECT * FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/EndMessage') DROP MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/EndMessage];

    IF EXISTS (SELECT * FROM sys.objects WITH (NOLOCK) WHERE schema_id = @schema_id AND name = N'dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_QueueActivationSender') DROP PROCEDURE [dbo].[dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3_QueueActivationSender];
    RETURN
END

IF NOT EXISTS(SELECT 1 FROM INSERTED)
BEGIN
    SET @dmlType = 'Delete'
    INSERT INTO @modifiedRecordsTable SELECT [UniqueID], [lngContact], [StartDate], [Status] FROM DELETED 
END
ELSE
BEGIN
    IF NOT EXISTS(SELECT * FROM DELETED)
    BEGIN
        SET @dmlType = 'Insert'
        INSERT INTO @modifiedRecordsTable SELECT [UniqueID], [lngContact], [StartDate], [Status] FROM INSERTED 
    END
    ELSE
    BEGIN
        SET @dmlType = 'Update';
        INSERT INTO @deletedTable SELECT [UniqueID],[lngContact],[StartDate],[Status] FROM DELETED
        INSERT INTO @insertedTable SELECT [UniqueID],[lngContact],[StartDate],[Status] FROM INSERTED
        INSERT INTO @exceptTable SELECT [RowNumber],[UniqueID],[lngContact],[StartDate],[Status] FROM @insertedTable EXCEPT SELECT [RowNumber],[UniqueID],[lngContact],[StartDate],[Status] FROM @deletedTable

        INSERT INTO @modifiedRecordsTable SELECT [UniqueID],[lngContact],[StartDate],[Status] FROM @exceptTable e 
    END
END

SELECT @rowsToProcess = COUNT(1) FROM @modifiedRecordsTable    

BEGIN TRY
    WHILE @rowsToProcess > 0
    BEGIN
        SELECT  @var1 = [UniqueID], @var2 = [lngContact], @var3 = [StartDate], @var4 = [Status]
        FROM    @modifiedRecordsTable
        WHERE   [RowNumber] = @rowsToProcess

        IF @dmlType = 'Insert' 
        BEGIN
            ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartMessage/Insert] (CONVERT(NVARCHAR, @dmlType))

            IF @var1 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/UniqueID] (CONVERT(NVARCHAR(MAX), @var1))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/UniqueID] (0x)
            END
            IF @var2 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/lngContact] (CONVERT(NVARCHAR(MAX), @var2))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/lngContact] (0x)
            END
            IF @var3 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartDate] (CONVERT(NVARCHAR(MAX), @var3))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartDate] (0x)
            END
            IF @var4 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/Status] (CONVERT(NVARCHAR(MAX), @var4))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/Status] (0x)
            END

            ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/EndMessage] (0x)
        END

        IF @dmlType = 'Update'
        BEGIN
            ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartMessage/Update] (CONVERT(NVARCHAR, @dmlType))

            IF @var1 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/UniqueID] (CONVERT(NVARCHAR(MAX), @var1))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/UniqueID] (0x)
            END
            IF @var2 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/lngContact] (CONVERT(NVARCHAR(MAX), @var2))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/lngContact] (0x)
            END
            IF @var3 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartDate] (CONVERT(NVARCHAR(MAX), @var3))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartDate] (0x)
            END
            IF @var4 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/Status] (CONVERT(NVARCHAR(MAX), @var4))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/Status] (0x)
            END

            ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/EndMessage] (0x)
        END

        IF @dmlType = 'Delete'
        BEGIN
            ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartMessage/Delete] (CONVERT(NVARCHAR, @dmlType))

            IF @var1 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/UniqueID] (CONVERT(NVARCHAR(MAX), @var1))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/UniqueID] (0x)
            END
            IF @var2 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/lngContact] (CONVERT(NVARCHAR(MAX), @var2))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/lngContact] (0x)
            END
            IF @var3 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartDate] (CONVERT(NVARCHAR(MAX), @var3))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/StartDate] (0x)
            END
            IF @var4 IS NOT NULL BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/Status] (CONVERT(NVARCHAR(MAX), @var4))
            END
            ELSE BEGIN
                ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/Status] (0x)
            END

            ;SEND ON CONVERSATION '871bf209-30ab-eb11-8cba-c8d9d20cebfa' MESSAGE TYPE [dbo_Appointments_772a313c-6865-4057-95a7-63dc7b7191e3/EndMessage] (0x)
        END

        SET @rowsToProcess = @rowsToProcess - 1
    END
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000)
    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT

    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) 
END CATCH

END

[JinsPeter]

An internal database error occurred. ---> System.Data.SqlClient.SqlException: Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
We identified that the

I am also getting a similar issue. I have a huge traffic of nearly 100-1000 updates per second in the listened columns. I update the screen in 3 seconds by querying from DB. The data received in the table changed event cannot suffice my requirement as the grid I show has a JOIN with other 5+ tables.

Is SQLTableDependency ideal for this scenario with this high traffic? Is it worth it if I actually build infrastructure to rerun the transaction? Is there a way to debounce / throttle the trigger from the DB itself?

[hdamis]

so please can anyone answer me ?

[MaximG1234]

My experience is that this code does not function particularly well under extremely high load where you have a complex schema and I have seen similar errors to what you describe. Frankly at 100-1000 updates per second I don't believe this library is designed for dealing with such high load, nor do I believe it is appropriate to bombard an SQL database in such a way.

If I was dealing with such a high load I would probably be looking at database designed specifically for such scenarios or at the very least batching my updates in some sort of intermediary service.

With that said I have generally used a retry pattern similar to that described in this question. Along with the following code which ensures that all the queues and contracts related to this library are cleaned up prior to restarting the service.

PRINT 'Starting Killing Services'

DECLARE @name VARCHAR(5000) 

DECLARE db_services_cursor CURSOR FOR 
SELECT [name] FROM sys.services 
WHERE service_id > 3

OPEN db_services_cursor  
FETCH NEXT FROM db_services_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ('Current Name: ' + @name)
    EXEC('drop service [' + @name + ']')
    FETCH NEXT FROM db_services_cursor INTO @name 
END 

CLOSE db_services_cursor  
DEALLOCATE db_services_cursor 

PRINT 'Ended'

PRINT 'Starting Killing Contracts'

DECLARE @contract_name VARCHAR(5000) 

DECLARE db_contract_cursor CURSOR FOR 
SELECT [name] FROM sys.service_contracts where service_contract_id > 6

OPEN db_contract_cursor  
FETCH NEXT FROM db_contract_cursor INTO @contract_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ('Current Name: ' + @contract_name)
    EXEC('drop contract [' + @contract_name + ']')
    FETCH NEXT FROM db_contract_cursor INTO @contract_name 
END 

CLOSE db_contract_cursor  
DEALLOCATE db_contract_cursor 

PRINT 'Ended'

PRINT 'Starting Killing Triggers Procs'

DECLARE @triggers_name VARCHAR(5000) 

DECLARE db_triggers_cursor CURSOR FOR 
SELECT name FROM sys.triggers WHERE type = 'TR' and name LIKE 'tr_dbo%_Sender'

OPEN db_triggers_cursor  
FETCH NEXT FROM db_triggers_cursor INTO @triggers_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ('Current Name: ' + @triggers_name)
    EXEC('drop trigger [' + @triggers_name + ']')
    FETCH NEXT FROM db_triggers_cursor INTO @triggers_name 
END 

CLOSE db_triggers_cursor  
DEALLOCATE db_triggers_cursor 

PRINT 'Ended'

PRINT 'Starting Killing Stored Procs'

DECLARE @storedprocs_name VARCHAR(5000) 

DECLARE db_storedprocs_cursor CURSOR FOR 
SELECT [name] from sysobjects where type = 'P' and category = 0 and name LIKE '%QueueActivationSender'

OPEN db_storedprocs_cursor  
FETCH NEXT FROM db_storedprocs_cursor INTO @storedprocs_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ('Current Name: ' + @storedprocs_name)
    EXEC('drop PROCEDURE [' + @storedprocs_name + ']')
    FETCH NEXT FROM db_storedprocs_cursor INTO @storedprocs_name 
END 

CLOSE db_storedprocs_cursor  
DEALLOCATE db_storedprocs_cursor 

PRINT 'Ended'

PRINT 'Starting Killing Message Types'

DECLARE @messagetype_name VARCHAR(5000) 

DECLARE db_messagetype_cursor CURSOR FOR 
SELECT [name] FROM sys.service_message_types where message_type_id > 14

OPEN db_messagetype_cursor  
FETCH NEXT FROM db_messagetype_cursor INTO @messagetype_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ('Current Name: ' + @messagetype_name)
    EXEC('drop MESSAGE TYPE [' + @messagetype_name + ']')
    FETCH NEXT FROM db_messagetype_cursor INTO @messagetype_name 
END 

CLOSE db_messagetype_cursor  
DEALLOCATE db_messagetype_cursor 

PRINT 'Ended'

PRINT 'Starting Killing Queues'

DECLARE @queue_name VARCHAR(5000) 

DECLARE db_queue_cursor CURSOR FOR 
SELECT [name] FROM sys.service_queues where is_ms_shipped = 0

OPEN db_queue_cursor  
FETCH NEXT FROM db_queue_cursor INTO @queue_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ('Current Name: ' + @queue_name)
    EXEC('drop QUEUE [' + @queue_name + ']')
    FETCH NEXT FROM db_queue_cursor INTO @queue_name 
END 

CLOSE db_queue_cursor  
DEALLOCATE db_queue_cursor 

PRINT 'Ended'

[hdamis]

Dear Maxim, but this will drop the trigger and the clients will not notify for any farther changes. as the trigger is dropped. and please tell me do you mean to make a job using the above code or a Stored Procedure and when I have to call it. Thanks,