Turbocharge Your Database Maintenance With Service Broker: Part 1

Question

Post reply

Turbocharge Your Database Maintenance With Service Broker: Part 1

Viewing 5 posts - 16 through 19 (of 19 total)

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

srinath.m SSC Journeyman Points: 89 More actions · Answer 1

Good morning, Marios!! Here is the result of the query:

ServiceQueueContract

//UTILITY/UpdStats/InitiatorServiceUpdStats_InitiatorQueueNULL

//UTILITY/UpdStats/TargetServiceUpdStats_TargetQueue//UTILITY/Contract

http://schemas.microsoft.com/SQL/Notifications/EventNotificationServiceEventNotificationErrorsQueueNULL

http://schemas.microsoft.com/SQL/Notifications/QueryNotificationServiceQueryNotificationErrorsQueueNULL

http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBrokerServiceBrokerQueuehttp://schemas.microsoft.com/SQL/ServiceBroker/ServiceEcho

http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBrokerServiceBrokerQueuehttp://schemas.microsoft.com/SQL/ServiceBroker/ServiceDiagnostic

Any clue, where did I go wrong 🙂

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

srinath.m (11/12/2013)
Good morning, Marios!! Here is the result of the query:
ServiceQueueContract
//UTILITY/UpdStats/InitiatorServiceUpdStats_InitiatorQueueNULL
//UTILITY/UpdStats/TargetServiceUpdStats_TargetQueue//UTILITY/Contract
http://schemas.microsoft.com/SQL/Notifications/EventNotificationServiceEventNotificationErrorsQueueNULL
http://schemas.microsoft.com/SQL/Notifications/QueryNotificationServiceQueryNotificationErrorsQueueNULL
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBrokerServiceBrokerQueuehttp://schemas.microsoft.com/SQL/ServiceBroker/ServiceEcho
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBrokerServiceBrokerQueuehttp://schemas.microsoft.com/SQL/ServiceBroker/ServiceDiagnostic
Any clue, where did I go wrong 🙂

Hmm, that output is correct.

Can you go through the tips I mention in part 2[/url] and see if you can identify the issue?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

D-SQL SSCrazy Points: 2265 More actions · Answer 3

Hi I know this post is older bu had some questions. I'm not an expert at this, i have been trying to get this set up to test, I am able to get the list into the table but then nothing after that. I run the test query and get Run Duration NULL. I have took it down and re-did a few times. Just wondering if you had any ideas, I'm not getting any errors either.

I do have service broker set up on the same database gathering schema changes but i didn't see any conflict.

Thanks in advance

D-SQL SSCrazy Points: 2265 More actions · Answer 4

This is great, I am new to service broker and wanted to get some questions answered. I set this up and was trying to configure it to run on all the databases. I downloaded all the material and saw the job. I'm assuming in the 2nd step is where I would at the syntax for all databases. Is that correct or does anything else need modified. Thanks

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

D-SQL (5/20/2014)
This is great, I am new to service broker and wanted to get some questions answered. I set this up and was trying to configure it to run on all the databases. I downloaded all the material and saw the job. I'm assuming in the 2nd step is where I would at the syntax for all databases. Is that correct or does anything else need modified. Thanks

Hi, sorry it has taken so long for me to respond.

The 1st step in the job - script "03_JOB_dba-updStats-ServiceBroker.sql" in the download - stores the records of all dbs, tables that are to be processed. In the example I was using only one db - SBdemo - but that can be easily extended to multiple dbs.

The 2nd step is where the actual work of service broker is being done.

Part 2 of this article goes in detail through the troubleshooting steps when this is not working.

Try going through that and let me know of any issues.

And thanks for trying this out! 🙂

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]