SqlDependency causes out of memory error on SQL Server RRS feed

  • Question

  • We have been using SqlDependency from an ASP.NET web site successfully for a database on a server for a long time.

    Now we have copied this database to another database on the same server, applied the required permissions (supposedly all correctly) and notifications are created as expected (we can see this in the management views).

    However after the command ALTER DATABASE SET ENABLE_BROKER is executed, the server's I/O increases a lot, and after a while the server seems to use more and more disk space in tempdb. Ultimately, the server seems to use up most of the available memory (2GB) and the server stops working. Management studio reports "There is insufficient system memory to run this query." (Microsoft SQL Server, Error: 701). Only restarting the entire server can stop the process, but it re-appears after restart again as long as the broker is enabled.

    How could SqlDependency and Query Notification be driving excessive disk I/O usage? This problem did not exist before, the database size and queries are the same, it only started to appear after copying the database.

    What type of factors and settings in SqlDependency could drive excessive disk I/O usage?

    Advice very appreciated.

    At the moment we couldn't re-enable the broker.

    Thursday, October 7, 2010 1:29 PM