Why we cannot put transaction log file in a FileGroup?

  • Hi,

    Can anyone tell me why we cannot put transaction log file in a FileGroup?

    Regards

    Rama Sankar

  • Why would you want to?

    Filegroups are used to store and group data for backup, logic, security and performance purposes. Log files dont need to be in the same position.

    They are written to and read from sequentially and its pretty unusual to see more than one ldf for a database. They cant be read only or used for different purposes. They dont fit into the reasoning for filegroups.

  • Filegroups group data files together for the purpose of table storage. Log files don't store tables or indexes, so they can't logically be part of a filegroup

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sankar276 (3/3/2012)


    Hi,

    Can anyone tell me why we cannot put transaction log file in a FileGroup?

    A short trip to SQL Server Books Online would have enlightened you as follows;

    SQL Server Books Online


    Database Filegroups

    Database objects and files can be grouped together in filegroups for allocation and administration purposes. There are two types of filegroups:

    Primary

    The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. All pages for the system tables are allocated in the primary filegroup.

    User-defined

    User-defined filegroups are any filegroups that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

    Log files are never part of a filegroup. Log space is managed separately from data space.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks all

Viewing 5 posts - 1 through 4 (of 4 total)

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