Search code examples
sql-serverasynchronousservice-broker

SQL Broker: Async procedure execution

I have read this great Remus Rusanu's article http://rusanu.com/2009/08/05/asynchronous-procedure-execution/

How to implement this idea:

I have a big main table, user can mark 'as delete' records there (set field to 1) I cannot use SQL Jobs because customers can use SQLExpress.

The idea is: when user 'delete' or 'undelete' records in big table need to send message to a queue. An activation proc 'fire and forget' proc to execute real Delete statement for marked records in the main table - all or parts, it depends.

But need maximally to avoid blocking.... That's why the question:

How to execute real deletion when SQL Server has a lowest loading? or when database has a lowest activity? How to detect these 'Low database loading' moments in the async proc?

Solution

  • There is no way to lync Service Broker activation directly to workload and only activate during 'low activity'.

    I cannot use SQL Jobs because customers can use SQLExpress

    While is true that SQL Server Express Edition lacks SQL Agent scheduling, there are work arounds using Service Broker conversation timers. See Scheduling Jobs in SQL Server Express (and part 2).