Service broker is the messaging technology that db mail implements in order to send messages, as the error states without it db mail will only queue messages and not be...

Yep, the data in the BLOBs is data retained "just in case". It has nothing to do with the day to day application operations.

I'm definitely leaning towards creating a dedicated...

I've been looking at filestream. From what I can see we get a good payoff with SELECTs hitting the table but pay a small penalty with inserts and deletes (which...

Sorry for the late reply Steve. It's been a three day weekend over here in Ireland. 😀

The tables holding the data are ~200-300GB on average I'd say. Not huge but...

Thanks again Steve.

Most of the applications that we have log XML blobs into their respective database(s), the worst example has over 1TB of data stored as XML.

Andrew

Further update on this (if anyone's watching it).

I added a new filegroup to the database and migrated all the nonclustered indexes to it. I was worried I'd get the same...

No one out there using a Content Addressable Storage system for blobs?

Nail on the head right there, test, test and test again.

Thanks very much!

Thanks for the reply Steve. Are there any issues around performing point in time restores with filestream? I've read a couple of articles online telling me to be wary.

The main challenge I'm facing with these BLOBs is that currently they go into a partitioned table (by day) and are stored for three months. After that, a stored procedure...

I'd get rid of the shrink database task immediately and then look at removing the index and statistics maintenance task by using Ola Hallengren's scripts separately.

I'd recommend running the...

Yes you can install SQL 2014 next to SQL 2008 and then restore the database.

Just remember to run the SQL upgrade advisor to make sure there won't be any problems...

I dropped and re-created the index on Saturday but excluded the database from any index maintenance until today. Have just re-tried running the maintenance job but had to stop it...

I'm not able to replicate on my dev instance and the job has again failed today with the same error even though I excluded the database.

I tried to drop and...

Running the rebuild now on my dev instance. Hopefully I'll be able to replicate the issue