• andre.quitta (11/17/2014)


    I had my infrastructure manager recently ask me why the system databases are in a different directory than the user databases.

    Contrary to what the others have said and except for TempDB, mine aren't. When I do an install of a new instance, I put the MDFs for Master, Model, and MSDB on the same "drive" as the user MDFs (Drive M:). I do the same with the LDFs, which are on a separate "drive" (Drive L:) from the MDFs/NDFs. To be sure, none of that is on the C: drive, which I relegate to only the operating system except for the PAGEFILE (Drive P:). There are some MDFs/LDFs that I don't keep on those two user DB drives. The "Archive" database MDFs and LDFs are located are located on Drive K: ("K" for "Keeper"). TempDB MDF/NDFs are on Drive T: and TempDB LDF is on Drive U:. We have "scratch pad" databases and the MDF/LDF/NDF files for those live on Drive S:.

    Backups live in a whole 'nuther world because if the SAN catches fire or takes some other kind of hit, I don't want my backups to be there. It takes more time and a bit more care to keep backup performance up but I use NAS for my backups. I've also figured out a way to map the UNC (varies from one environment to another) of the NAS to Drive R: (for "Restore") so that I don't need to worry so much about where the backups actually are. I just reference Drive R: for the backups.

    His other question, which I was totally clueless on, was why the system database log files (LDFs) are in the same directory as the MDFs, which our user databases have them in different directories.

    As the others have stated, that's simply what the defaults are. In theory, you can have a desktop box with just a single Drive C: for SQL Server and that's the minimum that they built the default around. With SANs and the ridiculously large sizes of drives nowadays, it's a bit of a chore to actually put things on "separate spindles" like they should be without wasting an absolute shedload of disk space especially since RAID uses multiple incredibly large disks. MDFs should be on separate spindles from LDFs (hardly ever happens in reality because of what I just said) so that if one or the other goes haywire, you either still have an ACID MDF or you can do a "TailLog" backup to recover but would also be crazy to dedicate a single large drive to the very small sizes of the 3 "M" system databases. So we just settle for separation by "logical" drives according to how the SAN was configured and hope the SAN gods accept our burnt offerings. :w00t:

    I've been unable to find any URLs as to the Why's. If you can forward me an article as to why that is, it would help a lot.

    I've never actually looked for the reasons WHY on the default installations. I've always just taken it for granted and have always changed it (to keep Drive C: "pure" as the operating system disk), so I can't actually point you to a decent URL on the subject other than the installation guide which explains the defaults but not the reason WHY for the defaults. It's just assumed that every system is going to have a Drive C:.

    I don't know if any of that answers your questions the way you thought they should be answered, but that's my reasoning and my recommendations. Your mileage may vary. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)