none
how can i to determine a file or filegroup for storing table data?

    Frage

  • i want to determine a specific file or file group for saving a specific table in a database. how can i do this?

    Donnerstag, 12. April 2012 16:11

Antworten

  • Hi iman rahiminia,

    For a table, there can be three types of objects: clustered index, non-clustered index, heap (a table without clustered index). When you say a file saving a specific table, it refers to where the objects belonged to that table stored. Please pay attention to this blog about this topic: SQL SERVER – List All Objects Created on All Filegroups in Database.


    Stephanie Lv

    TechNet Community Support

    • Als Antwort markiert Stephanie Lv Dienstag, 24. April 2012 08:20
    Montag, 16. April 2012 03:17
  • In addition tom Louis  reply

    create database test
      on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
    filegroup user_fg
      (name = 'datafile2', filename = 'c:\temp\datafile2')
    log on
      (name = 'logfile1', filename = 'c:\temp\logfile1')
    go

    use test
    go

    create table t1(col1 int)
    create table t2(col1 int) on [primary]
    create table t3(col1 int) on user_fg

    select
      object_name(i.id) as table_name,
      groupname as [filegroup]
    from sysfilegroups s, sysindexes i
    where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
     and i.indid < 2
     and i.groupid = s.groupid

    table_name filegroup
    ---------- ---------
    t1         PRIMARY
    t2         PRIMARY
    t3         user_fg

    alter database test modify filegroup user_fg default
    create table t4(col1 int)

    select
      object_name(i.id) as table_name,
      groupname as [filegroup]
    from sysfilegroups s, sysindexes i
    where i.id in (object_id('t1'), object_id('t2'), object_id('t3'),object_id('t4'))
     and i.indid < 2
     and i.groupid = s.groupid

    table_name filegroup
    ---------- ---------
    t1         PRIMARY
    t2         PRIMARY
    t3         user_fg
    drop database test


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Als Antwort markiert Stephanie Lv Dienstag, 24. April 2012 08:20
    Montag, 16. April 2012 05:20

Alle Antworten

  • So you are going to need to be more specific.  You can get a list of filegroups in the database along with the logical files from sys.master_files and sys.filegroups.  Here is my query, which includes logs and totals (but not how much space is available, which you may be asking):

    select  case when grouping(db_name(database_id)) = 1 
       then '--TOTAL--' 
       else db_name(database_id) end AS database_name, 
     case when grouping(SUBSTRING(database_files.physical_name,1,1)) = 1 
       then '--TOTAL--' 
       else SUBSTRING(database_files.physical_name,1,1) end as driveLetter,
        case when grouping(filegroups.name) = 1 Then '--TOTAL--'
       when filegroups.name is null then '--LOGS--'
       else filegroups.name end as filegroup_name, 
        case when grouping(database_files.name) = 1 
       then '--TOTAL--' 
       else database_files.name end as database_file_name,
        sum(database_files.size * 8 )   as sizeInK
    from sys.master_files as database_files
      left outer join sys.filegroups
       on database_files.data_space_id = filegroups.data_space_id
    where database_id = db_id()
    group by db_name(database_id), SUBSTRING(database_files.physical_name,1,1), filegroups.name, database_files.name with rollup

    Returns on one of my test databases with one filegroup:

    database_name     driveLetter filegroup_name   database_file_name  sizeInK
    ----------------- ----------- ---------------- -------------------- -----------
    SequenceDemos     C           --LOGS--         SequenceDemos_log    512000
    SequenceDemos     C           --LOGS--         --TOTAL--            512000
    SequenceDemos     C           PRIMARY          SequenceDemos        2048000
    SequenceDemos     C           PRIMARY          --TOTAL--            2048000
    SequenceDemos     C           --TOTAL--        --TOTAL--            2560000
    SequenceDemos     --TOTAL--   --TOTAL--        --TOTAL--            2560000
    --TOTAL--         --TOTAL--   --TOTAL--        --TOTAL--            2560000
    
    So that will tell you what groups exist, is that what you wanted?

    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Als Antwort vorgeschlagen Rohit-K Freitag, 13. April 2012 05:41
    Donnerstag, 12. April 2012 20:05
  • Hi iman rahiminia,

    For a table, there can be three types of objects: clustered index, non-clustered index, heap (a table without clustered index). When you say a file saving a specific table, it refers to where the objects belonged to that table stored. Please pay attention to this blog about this topic: SQL SERVER – List All Objects Created on All Filegroups in Database.


    Stephanie Lv

    TechNet Community Support

    • Als Antwort markiert Stephanie Lv Dienstag, 24. April 2012 08:20
    Montag, 16. April 2012 03:17
  • In addition tom Louis  reply

    create database test
      on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
    filegroup user_fg
      (name = 'datafile2', filename = 'c:\temp\datafile2')
    log on
      (name = 'logfile1', filename = 'c:\temp\logfile1')
    go

    use test
    go

    create table t1(col1 int)
    create table t2(col1 int) on [primary]
    create table t3(col1 int) on user_fg

    select
      object_name(i.id) as table_name,
      groupname as [filegroup]
    from sysfilegroups s, sysindexes i
    where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
     and i.indid < 2
     and i.groupid = s.groupid

    table_name filegroup
    ---------- ---------
    t1         PRIMARY
    t2         PRIMARY
    t3         user_fg

    alter database test modify filegroup user_fg default
    create table t4(col1 int)

    select
      object_name(i.id) as table_name,
      groupname as [filegroup]
    from sysfilegroups s, sysindexes i
    where i.id in (object_id('t1'), object_id('t2'), object_id('t3'),object_id('t4'))
     and i.indid < 2
     and i.groupid = s.groupid

    table_name filegroup
    ---------- ---------
    t1         PRIMARY
    t2         PRIMARY
    t3         user_fg
    drop database test


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Als Antwort markiert Stephanie Lv Dienstag, 24. April 2012 08:20
    Montag, 16. April 2012 05:20