Post reply

Anybody using Filegroups/Tablespaces to seperate System Tables and User Data???

  • April 19, 2004 at 7:29 am

    #101966

    Hello,

    ... MSSQL 7/SP2 and MSSQL 2000/SP3

    I am interested to know if anybody is using fiegroups? ORACLE calls them tablespaces. Are there any disadvantages for backup & recover purposes ie: restoring .mdf, .ldf and .ndf files for same database?

    Additionally, in ORACLE, the creation of tablespaces for user objects is heavily encouraged to seperate system tables and user tables/objects from residing in the same tablespace/datafile? Does this hold true in the MSSQL environment?

    (I found that there is very little documentation/reference in MSSQL about filegroups except for mimicking RAID performance). 

    Many thanks. Jeff

  • April 19, 2004 at 7:38 am

    #503326

    It depends.

    Filgroups is a three-edged sword (as the Vorlon say)

    They may have some effect on performance, but mostly they have an effect on your backup strategy. Usually it's not warranted to go there if there aren't any specific need to do so.

    About system objects vs user objects:

    Yes, this is true on SS 7.0, but not 2k, if....

    ... your recovery strategy requires you to be able to perform point-in-time restore.

    If this is required, then for any 7.0, it's required that the system catalog is available in order to be able to perform a BACKUP LOG WITH NO_TRUNCATE (which is the first thing you want to do if you loose your user db but still have access to the log)

    If point-in-time restore is not required, then there is no immediate need to separate system objects from user objects in your database.

    =;o)

    /Kenneth

  • April 20, 2004 at 7:56 pm

    #503560

    Filegroups can be useful in the following scenarios:

    - You want to separate large tables to its own filegroups. This can help you run dbcc on filegroups instead of the entire database.

    - Manage growth of tables by having them in its own filegroups

    - For VLDB's can plan filegroup backups instead of entire database which can be time consuming.

    - Can help performance if the filegroups can be placed in a separate disk with its own controller.

    - Can defrag index if the clustered index is in its own filegroup for large tables

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply