Service Broker Issues - SQL 2005 Enterprise Edition 64 SP3

• ERIC CRUDELI

  SSCommitted

  Points: 1786

  More actions

  April 16, 2013 at 6:43 am

  #292532

  Hello,

  Before to start I just resume actions I made to reduce msdb size (earn space on c: volume).

  I checked my msdb and I has a sysxmitqueue table using 3 Gb. I purged table by using the following commands :

  ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  GO

  ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE

  GO

  ALTER DATABASE [msdb] SET MULTI_USER

  GO

  After that I shrinked msdb database and reorganized Index with Stat. Well every worked fine but now some Alerts defined don't work like "Capture deadlock" or "Capture Process blocked" (WMI Events).

  I run some scripts

  select * from msdb.sys.server_event_notifications

  The result is : one line with SQLWEP_RECHECK_SUBSCRIPTIONS before Purge sysxmitqueue I had a lot of line.

  SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases only tempdb and msdb as service broker enabled.

  I didn't restart SQL AGENT.

  Is the purge generated the issue ?

  Thanks

  Eric