locked
default filegroup and userdefined file created on the same hard drive .how Datbase engine recognizes? RRS feed

  • Question

  • Hi,

             I have created a database with one default filegroup and 2 userdefined whose physical locations are same.How Database recognizes teh two filegroups internally,since i didnt see any folder got created for the userdefined filegroup.please help me .

     

    regards

    Chaithanya M

    Friday, May 27, 2011 6:58 AM

Answers

  • Hi Chaithu559,

    Filegroup is used to group the database files for allocation and administration purposes. Every database file has a physical file name and a logical file name.  You can get the database file physical location by following syntax:

    select a.name, b.name as 'Logical filename', b.filename from sys.sysdatabases a inner join sys.sysaltfiles b on a.dbid = b.dbid where fileid = 1
    

    Please refer to: Physical Database Files and Filegroups.


    Best Regards,
    Stephanie Lv

    • Marked as answer by Stephanie Lv Sunday, June 5, 2011 2:52 AM
    Monday, May 30, 2011 9:31 AM

All replies

  • Can you please explain little more? Did you add files in the filegroup?

    Each File would have unique physical name and logical name.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Friday, May 27, 2011 7:12 AM
  • Hi,

         I have created a database with on primary filegroup where i added only one file with name MyDB_Prm.mdf and i created another filegroup called Mydb_FG1 to which i added two .ndf files.For both filegroups the physical location is same.My concern is,how database engine recognizes which file is in which filegroup if they are in same location.

     

    regards

    Chaithu

    Friday, May 27, 2011 7:21 AM
  • Both ndf files would have seperate physical name.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Friday, May 27, 2011 7:39 AM
  • Hi,

             So,You mean the database engine differentiates with the physical name given to the filegroup even though they  are created at the same physical location.?

     

    Regards

    Chaithanya M

     

    Friday, May 27, 2011 8:41 AM
  • Filegroup is logical grouping of files. There is no location of filegroup. May be I am not able to understand question.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Friday, May 27, 2011 12:46 PM
  • Hi Chaithu559,

    Filegroup is used to group the database files for allocation and administration purposes. Every database file has a physical file name and a logical file name.  You can get the database file physical location by following syntax:

    select a.name, b.name as 'Logical filename', b.filename from sys.sysdatabases a inner join sys.sysaltfiles b on a.dbid = b.dbid where fileid = 1
    

    Please refer to: Physical Database Files and Filegroups.


    Best Regards,
    Stephanie Lv

    • Marked as answer by Stephanie Lv Sunday, June 5, 2011 2:52 AM
    Monday, May 30, 2011 9:31 AM