Jeffrey Williams wrote:
The best I can find is that it is controlled by the service and not externally managed.
Either way, if I had this issue on my servers I would create a custom procedure. The standard code is just wrong.
Totally agreed on the custom procedure being the best choice.
Shifiting gears a bit... I wish MS would stop mandating what needs to be done with no option for override. Another example of such is thing is that, while every DBA that's worth their salt understands the importance of "balanced files" (same size files) in TempDB, not every DBA or MVP understands that there are exceptions to every rule even for that general supposed "Best Practice" that previously required the invocation of TF 1117 to be realized in TempDB . MS did a great thing by making override options by database except they didn't provide an override for TempDB and the ARE extraordinary circumstances where at least a temporary override is absolutely necessary.
Don't even get me started about the automatic windows updates and related reboots in Windows 10. They did provide some relief in being able to schedule it but it doesn't match MY ever-changing schedule for doing such things.
Having this SSRS cleanup thing be under the control of a user-immutable service just pisses me off and is yet another reason why I say that SSRS is a four letter word. 😀
Jeffrey Williams wrote:
And they are still using ntext for columns...why?
It's the same old story that MS has followed forever... "Do as we say and not as we do". It's like the semi-colon usage mandate... if you look at the SQL in system objects, they severely violate that and a hundred true "Best Practices".
It may also be because of the problem they know they built into the newer LOB datatypes that few realize... the newer LOB datatypes default to in-row where the old ones defaulted to out-of-row. Why is that a problem? I have a whole presentation on the problems that causes including unnecessarily bloated Clustered Indexes that slow down all queries (not just CI scans) that use the clustered index. Severe and totally unnecessary massive fragmentation due to in-row LOB expansive updates. Same holds true now even if you know how to force the newer LOB datatypes out of row because of the now unreserved fixed size for the pointers (I have a fix for that, as well). And then there's the problem of incredibly low page densities both due to the size of rows with in-row lobs and a thing that I call "Trapped Short Rows", which can result in huge numbers of pages having an immutable page density of only a couple percent of page fullness.
Proof positive that MS is the poster child for the old adage of "Change is inevitable... change for the better is not".
Heh... sorry for the rant but Microsoft's improvements have come at a great cost to me, the data I manage, and the servers it lives on in many areas and the thought of a user-immutable service being "in-charge" of a cleanup schedule just added fuel to my fire. Ranting about it keeps me from driving to Washington state with a truck-load of frozen pork chops. 😀
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)