CODEWITHSUNDEEP
sql-servermemory-managementdatabase-administration
Briskstar Technologies
Briskstar Technologies

Reputation: 2253

SQL Server Notification Disable - Free up sysdercv table

After enabling server broker sql notification. My sql memory goes 2 gb.

How can I kill queues? How to release one of notification table?

Below query "sysdercv" tables goes huge.

select OBJECT_NAME(p.object_id),
 reservedpages = sum(a.total_pages),
    usedpages = sum(a.used_pages),
    pages = sum(
            CASE
                -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
                When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
                When a.type <> 1 Then a.used_pages
                When p.index_id < 2 Then a.data_pages
                Else 0
            END
        )
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
GROUP BY p.object_id
with rollup

How can I frees up and kill all service broker notifications? I also want to disable notification. But want to kill and frees up above table first.

Upvotes: 1

Views: 1891

Answers (1)

Briskstar Technologies
Briskstar Technologies

Reputation: 2253

Finally done with making conversation clean up.

SET NOCOUNT OFF;
DECLARE @handle UniqueIdentifier
DECLARE @count INT =0

DECLARE handleCursor CURSOR
FOR 
SELECT TOP 1000 [conversation_handle]
FROM sys.conversation_endpoints with(nolock)
WHERE [state] = 'co'
AND far_service = 'ChangeNotifications' 
AND security_timestamp < '2013-01-01'

DECLARE @Rows INT
SELECT @Rows = COUNT(*) FROM sys.conversation_endpoints with(nolock)
WHERE [state] = 'co'
AND far_service = 'ChangeNotifications'

WHILE @ROWS>0
BEGIN
    OPEN handleCursor

    FETCH NEXT FROM handleCursor 
    INTO @handle

    BEGIN TRANSACTION

    WHILE @@FETCH_STATUS = 0
    BEGIN

        END CONVERSATION @handle WITH CLEANUP

        FETCH NEXT FROM handleCursor INTO @handle
        SET @count= @count+1
    END

    COMMIT TRANSACTION
    print @count

    CLOSE handleCursor;

    IF @count > 100000
    BEGIN
        BREAK;
    END

    SELECT @Rows = COUNT(*) FROM sys.conversation_endpoints with(nolock)
    WHERE [state] = 'co'
    AND far_service = 'ChangeNotifications'
END
DEALLOCATE handleCursor;

Upvotes: 3

Related Questions