Thanks for the help patrickmcginnis59 10839.

You my not have given a complete answer, but I learned a lot for the links and insights you provided.

My colleague and I also devised...

I was able to speed up the query by reducing the intersect results by correcting the geography orientation.

In the end a VIEW was sufficient as only a small portion of...

Even completely removing the order by doesn't help.:(

Thanks for the link Patrick, was a good read.

We use the sectors technique for other purposes, but our sectors are 6m x 6m. Unfortunately even sectors this small are not...

Sean Lange (11/24/2014)

---

DennisPost (11/24/2014)

---

Thanks for the tip Sean. No .sql available, had to make a choice.

Here it is again as .doc and .zip.

Can also do .txt or post all 1130...

Thanks for the tip Sean. No .sql available, had to make a choice.

Here it is again as .doc and .zip.

Can also do .txt or post all 1130 lines of code...

Still dealing with the bloated TempDBs : http://www.sqlservercentral.com/Forums/Topic1607443-1550-1.aspx?Update=1

Turns out Service Broker was the culprit.

Having ended all the disconnected conversations, the TempDB did NOT release internally reserved pages.

http://www.sqlservercentral.com/Forums/Topic1599547-1550-1.aspx?Update=1

Does anyone know how I can force SQL to release the...

I ended up using the code below to end the conversations.

Cicra 3.5 million conversations per 30 mins.

You might have to rinse and repeat to deal with all the conversations.

SET NOCOUNT...

I used ALTER DATABASE <DBName> NEW_BROKER WITH ROLLBACK IMMEDIATE to clean up a couple of databases with 10's millions open conversations with state "DI" (Disconnected Inbound).

The biggest one...

Perhaps this MS Connect issue will help.

Turn off automatic updates 😛

I'm not sure if the queries I am using are accurate.

Can anyone vouch for them?

EXEC sp_SpaceUsed

[database_name][database_size][unallocated space]

tempdb128801.31 MB127796.11 MB

[reserved][data][index_size][unused]

6984 KB3632 KB1576 KB1776 KB

Judging by these results, I should be...

Resolved.

Daily job saves all indexed views into a table.

DDL trigger checks if the view to be alter is in the IndexedView table.

If so, raise error & rollback.

To alter the view,...

No. Reporting is offloaded onto the subscriber. It uses different indexes.

Might take a while to setup replication to the...

All good again.

Just a DBCC SHRINKFILE removed the clunky VLFs.

(Ran after full backup and a few log backups. Then diff backup when VLFs returned to normal to ensure the backup...