Jeff Moden (4/19/2010)
I didn't mean for it to sound harsh on my part. Let me ask though simply because I lead a life sheltered by large batch jobs where only staging tables are in a DB with the SIMPLE recovery mode... would you say that most production databases that folks build are in the SIMPLE recovery mode or BULK INSERT/ FULL recovery mode?
It didn't sound harsh at all Jeff, just as if you had been sheltered from some of the rather nasty things that exist out there in the wild (eg CISCO used SQL Server Desktop Engine using simple recovery model in their BBSM product, which was typically installed in situations where the database was updated throughout the day and night so that without full recovery model data loss was guaranteed any time the server went down other than in a tidy controlled shutdown) and from the practise of using small databases to describe things like availability of features, menu structures, and customisations in other products delivered to many people (those small databases can be replaced by new releases, but other than that they are read only so there's no use to full recovery model).
I think that if someone builds their own production databases they will usually want full recovery model - I certainly don't disagree with you on that one - although history databases (see below) are an exception. Even the small read-only databases mentioned above have master copies at the product suppliers site which are writable (how else do new versions get generated) and these copies may well use full recovery model. And of course the "nasty" cases ought to have had full recovery model even though they actually don't. So maybe I overstated my disagreement with your statement.
I suspect that if we both were looking at the same database and the same use of it we would agree on which recovery model to use. All my writeable production databases, except some history databases (see below), have had full recovery model. It's just that I've come across a large number of DBs that don't have full recovery model, some for good reasons and some for bad reasons, and I read you as suggesting that databases without full recovery model would be very rare.
History databases (I don't want to call them DW, because the data isn't massaged in any way, it's just a direct copy of information from the source databases) which are designed to be able to use simple recovery model are quite rare, mainly because people want to do one or more of (a) massage the source data (b) handle data for which it's hard to see what has and what hasn't already been imported (c) do computation and store precalculated partial results of MIS queries in the history DB instead of in a separate MIS DB. In many systems (b) is an inevitable consequence of the requirements (or, sometimes, of the fact that the source databases weren't designed with row-versioning in mind, or that the source databases aren't allowed to talk to each-other). In others (a) is needed to get the cost of the MIS queries down to an acceptable level. Once (a) or (b) is happening, there's little point in keeping the two DBs separate (but I try to educate people who want to merge the two databases when neither (a) nor (b) applies, since having the history database designed to use simple recovery can save an awful lot of disc space and backup store).