strange setup for a datawarehouse server?
- From: Eric Bragas <ericbragas@xxxxxxxxx>
- Date: Tue, 4 Dec 2007 06:31:15 -0800 (PST)
Hi Everybody,
At the company I work, the data warehouse administrators set up a
datamart server. However, I'm very skeptical about the setup
configuration. In essence, it's our databases' tables, views, and
proc's that are important, but not the data stored within those tables
because we can always re-run procedures to re-import the data from the
data warehouse.
First of all, shouldn't we be using Simple transaction mode instead of
Full? Again, we're not concerned about data loss. The only time we
would ever restore a database would be to get an object back, like a
dropped view or stored procedure. Our transaction logs have grown to
sizes I've never seen before--80 GB and higher in some cases. Our
main staging database has a transaction log over 220GB in size, with
data files growing to approximately the same size.
Second, how many LDF and MDF files should our database(s) have? In
the staging database, we're sure to be storing over 50GB of data in
any given database, but I don't understand why these databases were
set up with [on average] 4 MDF files and 4 LDF files. To top it off,
they're not consistent; one database can have MDF files on 3 different
local drives, and same for LDF files! I just don't understand why
somebody would do this. I understand we may want all LDF files on one
hard drive and all MDF files on another hard drive but what is the
advantage [if any] to having (1) multiple MDF and LDF files, and (2)
MDF and LDF files spread over various hard drives?
What advice could somebody give based on the few specs I've listed
here? I'm assuming we want to allow the datamart staging database to
grow automatically, without restriction, but should it also be set to
"auto-shrink"? And if our main staging database has approx. 200 GB of
data, how many MDF files and LDF files should it have?
I know there's a lot of theory I'm asking about here, so if somebody
could point me to a location where I could read and find my own
answers to any of these questions, that too would be greatly
appreciated. I've read BO's backing up the transaction log and
truncating the transaction log, but we should only be doing this
rarely (I would think).
Thanks,
Eric Bragas
.
- Prev by Date: split DB into two related files?
- Next by Date: Re: split DB into two related files?
- Previous by thread: split DB into two related files?
- Next by thread: integrating SP2
- Index(es):
Relevant Pages
|
