Post reply

File groups and Data files

  • November 22, 2006 at 6:13 am

    #61014

    I have a Primary File Group, with 2 datafiles assigned to it. I have a bunch of tables in the database.

    Is there a way to identify which filegroup has the data for a particular table?

    How would SQL Server handle this situation while dividing the data in the 2 data files?

    TIA

  • November 22, 2006 at 6:31 am

    #673430

    if a table is assigned to a filegroup, it stays within that file group.

    if the filegroup is comprised of more than one physical file, when inserts occur, the data is round robin'ed into the multiple files, so no single file would contain a specific table.

    so if you create multiple file groups, with a 1:1 relationship to the file it uses, only then can you know which file contains a table.

    an undocumented proc that sp_help uses was the key:   sp_objectfilegroup

    select sysobjects.name as TableName,

          s.groupname as Data_located_on_filegroup

       from sysobjects, sysfilegroups s, sysindexes i 

       where

         sysobjects.id = i.id   

        and i.indid < 2 

        and i.groupid = s.groupid 

    here's an indepth link about how it gets round robin'ed:

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/17/948.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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