Using extended events to capture sort warnings and hash warnings

  • Hi

    I have just downloaded the extended events gui for sql2008.

    Can anybody point me to any articles on how to set this up to capture the info for sort warnings and hash warnings ?

  • Thanks Steve.

    I did see that first article earlier today but then decided not follow it as Mr Fritchey mentioned extended events where easier to setup (but that the article would absolutely work, because i initially asked him about that very one!)

    However - i have just tried to implement its advice.

    I have run the query in the lising number 6

    use database1

    --enable service broker --

    ALTER DATABASE database1 SET ENABLE_BROKER;

    --ENSURE WE CAN EXECUTE QUERIES AGAINST DMV'S --

    ALTER DATABASE database1 SET TRUSTWORTHY ON;

    GO

    --CREATE A QUEUE TO COLLECT EVENTS --

    CREATE QUEUE dbo.sortWarningsQueue

    WITH STATUS=ON;

    GO

    --CREATE A SERVICE ON THE QUEUE THAT REFERENCES THE EVENT NOTIFICATIONS CONTRACT --

    CREATE SERVICE SortWarningsService

    onqueue dbo.sortwarningsqueue

    (

    [http://schemas.microsoft.com/sql/notifications/PostEventNotification]

    );

    go

    -- CREATE THE DATABASE EVENT NOTIFICATION

    CREATE EVENT NOTIFICATION NotifySort_Warnings

    on server

    FOR SORT_WARNINGS

    TO SERVICE 'SortWarningsService','current database';

    GO

    and i get the following error:

    Msg 15151, Level 16, State 1, Line 1

    Cannot find the contract 'http://schemas.microsoft.com/sql/notifications/PostEventNotification', because it does not exist or you do not have permission.

    I have checked the database settings and it is now enabled service broker. Can this error be ignored or how can i fix it ?

    Thanks

  • I'm not an expert in this area at all. Grant is much better.

    That error looks like either a contract isn't set up properly, or it's trying to make a call out to microsoft.com to verify some schema in a DOM.

  • Can anybody explain how I set up a 'contract' if I have not already done so? Is there anything I can do to check the settings of this contract' ?

  • If anybody is having the same issue as me regarding the 'service contract' error from the above script - i think i have fixed it. Well it now runs on my machine at least. I altered the code slightly to look like this.....

    --CREATE A SERVICE ON THE QUEUE THAT REFERENCES THE EVENT NOTIFICATIONS CONTRACT --

    USE ServerName

    CREATE SERVICE [//ServerName/SortWarningsService]

    onqueue dbo.sortWarningsQueue

    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

    go

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply