Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Turbocharge Your Database Maintenance With Service Broker: Part 1 Expand / Collapse
Author
Message
Posted Tuesday, July 24, 2012 4:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
Wandrag (7/23/2012)
Hi,
Thanks for this article!
I've changed some of our maintenance plans using SB.

Question: How can I limit ONLY the SB queries to use MaxDop=1?
I don't want to make this server wide...

I've tried using Resource governor, but that's not picking up service broker... :-(



Hi,

The only way I can think of is by wrapping the whole SB process in sp_configure statements, as shown below.
"UPDATE STATISTICS" does not take a MAXDOP hint, unfortunately, but "ALTER INDEX...REBUILD" does, so in the latter case you could add the hint in the SB code without having to do it instance-wide.

Of course, doing it instance-wide means that all activity for the duration of the SB process will run using the current MAXDOP setting, but I cannot think of a better alternative.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

<SB process goes here>

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO



__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1334349
Posted Monday, November 11, 2013 3:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 22, Visits: 428
Marios, I've setup the database and ran scripts in the order they appear in supplementary material, but receiving the following error when triggered the job "dba - updStats - ServiceBroker"....

Executed as user: xxxxx. Invalid object name '//UTILITY/UpdStats/InitiatorService'. [SQLSTATE 42S02] (Error 208) Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. [SQLSTATE 25000] (Error 266). The step failed.

Would you be able to assist me with this situation, please...!!
Post #1513282
Posted Tuesday, November 12, 2013 3:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:36 AM
Points: 2,840, Visits: 3,970
Why the database needs to be set "trust worthy" in case of SB ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1513400
Posted Tuesday, November 12, 2013 4:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
srinath.m (11/11/2013)
Marios, I've setup the database and ran scripts in the order they appear in supplementary material, but receiving the following error when triggered the job "dba - updStats - ServiceBroker"....

Executed as user: xxxxx. Invalid object name '//UTILITY/UpdStats/InitiatorService'. [SQLSTATE 42S02] (Error 208) Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. [SQLSTATE 25000] (Error 266). The step failed.

Would you be able to assist me with this situation, please...!!


Hi, it doesn't look like this object has been created in 02_ServiceBrokerObjects.sql:

USE UTILITY;
GO

CREATE SERVICE [//UTILITY/UpdStats/TargetService]
ON QUEUE UpdStats_TargetQueue ([//UTILITY/Contract]);
GO

Can you run the following code and send me the results?

USE [UTILITY];

SELECT
S.name AS [Service]
, Q.name AS [Queue]
, C.name AS [Contract]
FROM
sys.services AS S
INNER JOIN
sys.service_queues AS Q
ON
S.service_queue_id = Q.[object_id]
LEFT OUTER JOIN
sys.service_contract_usages AS U
ON
S.service_id = U.service_id
LEFT OUTER JOIN
sys.service_contracts AS C
ON
U.service_contract_id = C.service_contract_id;



__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1513416
Posted Tuesday, November 12, 2013 4:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
Bhuvnesh (11/12/2013)
Why the database needs to be set "trust worthy" in case of SB ?


Good question, I don't have a good answer to it.

I mention this briefly in Part 2.

You will get this error in sys.transmission_queue:
One or more messages could not be delivered to the local service targeted by this dialog.


It seems the db on which Service Broker is running needs to be trustworthy to be able to access objects on other databases through Service Broker.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1513418
Posted Tuesday, November 12, 2013 9:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 22, Visits: 428
Good morning, Marios!! Here is the result of the query:

Service Queue Contract
//UTILITY/UpdStats/InitiatorService UpdStats_InitiatorQueue NULL
//UTILITY/UpdStats/TargetService UpdStats_TargetQueue //UTILITY/Contract
http://schemas.microsoft.com/SQL/Notifications/EventNotificationService EventNotificationErrorsQueue NULL
http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService QueryNotificationErrorsQueue NULL
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker ServiceBrokerQueue http://schemas.microsoft.com/SQL/ServiceBroker/ServiceEcho
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker ServiceBrokerQueue http://schemas.microsoft.com/SQL/ServiceBroker/ServiceDiagnostic

Any clue, where did I go wrong :)
Post #1513519
Posted Tuesday, November 12, 2013 10:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
srinath.m (11/12/2013)
Good morning, Marios!! Here is the result of the query:

Service Queue Contract
//UTILITY/UpdStats/InitiatorService UpdStats_InitiatorQueue NULL
//UTILITY/UpdStats/TargetService UpdStats_TargetQueue //UTILITY/Contract
http://schemas.microsoft.com/SQL/Notifications/EventNotificationService EventNotificationErrorsQueue NULL
http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService QueryNotificationErrorsQueue NULL
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker ServiceBrokerQueue http://schemas.microsoft.com/SQL/ServiceBroker/ServiceEcho
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker ServiceBrokerQueue http://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 and see if you can identify the issue?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1513564
Posted Thursday, May 8, 2014 5:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 3:42 PM
Points: 82, Visits: 660
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
Post #1569114
Posted Tuesday, May 20, 2014 9:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 3:42 PM
Points: 82, Visits: 660
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
Post #1572748
Posted Thursday, May 22, 2014 5:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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!


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1573516
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse