Yes, SB use tempdb

"Service Broker explicitly uses tempdb for preserving existing dialog context that cannot stay in memory. The size is approximately 1 KB per dialog.

Also, Service Broker implicitly uses...

Run this 3 query

http://www.brentozar.com/archive/2011/11/how-tell-when-tempdb-problem-webcast-video/

and post results. With this informations maybe someone will be able to help.

Have you select "This server requires a secure connection (SSL)" for GMAIL account?

http://www.sqlservercentral.com/blogs/databaseexpertisecom/2010/05/12/database-mail-set-up-in-sql-server-2008/

It depends, you did not give tables definition and actual execution plan, but this query looks like a good candidate to try indexed views.

But then if DR site goes down: "Loss of the mirror and the witness

Assume...

Russell101 (5/12/2013)

---

Thanks for the info Perry.

So in the event of a power outage at the primary site where the secondary server will lose communication with both the primay and witness...

No, service broker is for asynchronous processing. But you probably could put a message to the SB from DML trigger or an audit SP.

What do you want to achieve?

Why the service broker?

You should use DML Triggers http://msdn.microsoft.com/en-us/library/ms191524%28v=sql.105%29.aspx

Using PowerShell if installed and WMI if not, VBS and WMI.

You have a two way of upgrade Side-by-Side Upgrade or In-Place Upgrade.

First you should run the Upgrade Advisor http://www.microsoft.com/en-us/download/details.aspx?id=11455

Then you should read this

http://download.microsoft.com/download/5/3/D/53D72434-7BD5-41C6-A806-8212C1B0DCA1/SQL%20Server%202005%20to%202008%20Upgrade%20White%20Paper.docx

select ... FOR XML http://msdn.microsoft.com/en-us/library/ms178107%28v=sql.105%29.aspx

exec...

What has changed except the SQL Server Engine version?

Check this, maybe this is an issue: http://www.benjaminnevarez.com/2011/05/optimizer-statistics-on-linked-servers/

http://technet.microsoft.com/en-us/library/ms175191.aspx

You need a running instance of SQL Server, so at least a SQL Server service and a SQL Server Browser if witness is on named instance and you connect to...

No, won't be, and transactions will commit without any errors.

Without a witness, you must failover manually and what is worse even in safety FULL...

Same as taking the db to offline. The only way, restart service.