One of our corporate standards to is have a separate filegroup/file for user tables/indexes. This is set as the default so no need to qualify CREATE TABLE statements.

So it looks like this

  • fileid 1 = system tables, MDF
  • fileid 2 = t-log = LDF
  • fileid 3 = user stuff = NDF

Can anyone here help me understand the original justification why this was mandated?

I'll come clean and state I think it's voodoo. Am I wrong...?

Edit: I am aware of how to use filegroups for separation of indexes/partitions/archives, as well as how to restore piecemeal. This question is about the use of a separate filegroup on the same volume for system tables only.


Microsoft's 70-432 training book says "The main reason not to place any of your objects on the primary file group is to provide as much isolation in the I/O as possible. The data in the system objects does not change as frequently as data in your objects. By minimizing the write activity to the primary data file, you reduce the possibility of introducing corruption due to hardware failures. In addition, because the state of the primary filegroup also determines the state of the database, you can increase the availability of the database my minimizing the changes made to the primary filegroup."

So, take that as you will. Others say this is not necessary in certain circumstances and of course is more to maintain. Just thought I'd provide Microsoft's reasoning.

  • Reasonable, some written justification for it. I'll accept this
    – gbn
    Feb 1 '12 at 21:36
  • 1
    Another reason is that a PARTIAL database restore allows the restoration of the PRIMARY filegroup plus selected other filegroups allowing quicker recovery of a correctly designed VLDB. Allowing the archived/secondary filegroups to be restored later.
    – MartinC
    Feb 2 '12 at 0:15
  • @MartinC: I know about partial restores etc, but I never understood the logic of explicitly separating the system tables. Filegroups for performamce, archiving, maintenance, partitioning etc. But system tables? Jared offered the best explanation so far..
    – gbn
    Feb 2 '12 at 5:44
  • If the database as a whole is very large, the primary filegroup could have a more regular backup that the main data. The restore would only require a tail log backup and restore of the primary filegroup and transaction logs since the filegroup backup plus the tail. As the system tables are small this would be a quicker recovery process vs doing this for the whole database so it can reduce downtime in the event of an issue.
    – MartinC
    Feb 2 '12 at 8:31

The isn't a performance gain to this, there is a recoverabily gain to be made. If file corruption happens in the system tables then the database is lost. If you keep the user data in a separate file group (or groups) then you can restore just those files keeping the rest of the database online during the restore (assuming Enterprise Edition here).

If this is why they state this, I can't say, but this would be a benefit of having multiple file groups with just the system objects in the PRIMARY filegroup.

You should however kick then in the junk for saying that AutoShrink should be enabled.

  • To learn more about this, you can search for Online Piecemeal Restore in Books Online.
    – Brent Ozar
    Feb 20 '11 at 23:24
  • 1
    I've always thought this is voodoo too given the 2 filegroups are on the same volume (on a SAN). Is the risk of corruption so high? (The actual operational DBAs set AutoShrink false)
    – gbn
    Feb 21 '11 at 5:21
  • Odds are if there is corruption it'll be a single page within a single file as the storage will hickup on writing the page to disk. Something like 99.9999% of database corruption is a storage problem. 1/2 of the rest of the problems are bad memory, the rest are SQL bugs. As the databases get larger (multi-TB) this becomes more important, as restoring a multi-TB database will take several days.
    – mrdenny
    Feb 21 '11 at 22:49
  • Wouldn't I be right in thinking that if the system objects are only in the primary filegroup should you need to in future you will be able to do the following. Create x additional files in another filegroup. Proportionally fill these files by migrating your data from your your existing data file? May 17 '12 at 8:30

Not sure I understand, are you asking for someone to justify your corporate standard? I would think that whoever wrote that standards doc for your company would be able to shed some light as to why this would be done.

That being said, it is not unusual for some shops to want to break out system data from user data. And if used in conjunction with dedicated sets of disks, you could reap some performance gains.

  • Thanks. Not justify it, but explain it. This is the same DB Engineering team who say AutoShrink on. Given system tables occupy a few MBs and will be in memory anyway, do you believe in any performance gain?
    – gbn
    Feb 20 '11 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.