locked
File group full RRS feed

  • Question

  • Hi Team,

    We having 1 primary file group and another seconday file group.  (It is Sql server 2008)

    But Secondary file group is full. as per my knowledge if secondary file group is full it should automatically take space from primary file group.

    We having 2 TB free space available.

    Please help me why it is not taking the space from primary file group?


    • Edited by VijayKSQL Thursday, July 13, 2017 9:25 PM add
    Thursday, July 13, 2017 9:23 PM

Answers

All replies

  • Hi,

    If my secondary file group is full then what happens, does it automatically go to Primary file group?

    Thursday, July 13, 2017 10:14 PM
  • if objects created on the secondary file group fill up the secondary file group you will be unable to add more data or objects to it.

    It will not start to use the primary file group.

    Thursday, July 13, 2017 10:48 PM
  • Oh!  I knew that we can't add addition files or objects then what abou existing data file will it use free space from primary group.

    Because we are getting SQL database file groups files  are running out of disk space from secondary file group.

    when i check free space available is showing aroud 2 TB.


    • Edited by VijayKSQL Thursday, July 13, 2017 11:06 PM add
    Thursday, July 13, 2017 11:00 PM
  • I am not sure where you are seeing this.

    What does this return?

    with s as (
    select df.[type] as [type],
    df.name collate database_default as name,
    df.drop_lsn as drop_lsn,
    dtb.database_id as database_id, 
    df.[file_id] as [file_id],
    df.max_size as max_size, 
    df.growth as growth,
    df.is_percent_growth as is_percent_growth,
    df.is_media_read_only as is_media_read_only,
    df.is_read_only as is_read_only,
    df.[state] as [state],
    df.is_sparse as is_sparse,
    df.data_space_id as data_space_id,
    df.physical_name collate database_default as physical_name,
    df.size as size
        from sys.database_files as df inner join sys.databases as dtb 
             on (db_id() = dtb.database_id) 
                  and (df.type = 2 or df.type = 0) 
                  and (df.drop_lsn is null) 
             where
                  (dtb.source_database_id is null)
    union
    select 
    mf.[type] as [type],
    mf.name collate database_default as name,
    mf.drop_lsn as drop_lsn,
    mf.database_id as database_id,
    mf.[file_id] as [file_id],
    mf.max_size as max_size,
    mf.growth as growth,
    mf.is_percent_growth as is_percent_growth,
    mf.is_media_read_only as is_media_read_only,
    mf.is_read_only as is_read_only,
    mf.[state] as [state],
    mf.is_sparse as is_sparse,
    mf.data_space_id as data_space_id,
    mf.physical_name collate database_default as physical_name,
    mf.size as size 
        from sys.master_files as mf inner join sys.databases as db
           on (mf.database_id = db.database_id)
              and (mf.type = 2 or mf.type = 0) 
              and (mf.drop_lsn is null)           
              and (db.source_database_id is not null)
           where mf.database_id = db_id()) 
                
    
    
    SELECT
    s.name, type_desc, max_size, growth, is_percent_growth, size FROM
    sys.filegroups AS g
    INNER JOIN s ON s.data_space_id=g.data_space_id
    --'AdventureWorks2016CTP3_mod'

    Thursday, July 13, 2017 11:21 PM
  • name type_desc max_size growth is_percent_growth size
    test1234_20_data ROWS_FILEGROUP -1 0 0 19795072
    test123440_1_Data ROWS_FILEGROUP -1 0 0 6400000
    test123440_11_Data ROWS_FILEGROUP -1 100 1 12800000
    test123440_12_Data ROWS_FILEGROUP -1 0 0 12800000
    test123440_13_Data ROWS_FILEGROUP -1 0 0 12800000
    test123440_14_Data ROWS_FILEGROUP -1 0 0 3840000
    test123440_15_Data ROWS_FILEGROUP 10240000 0 0 10240000
    test123440_18_Data ROWS_FILEGROUP -1 0 1 3840000
    test123440_19_Data ROWS_FILEGROUP -1 0 1 2560000
    test123440_2_Data ROWS_FILEGROUP -1 0 1 13440000
    test123440_3_Data ROWS_FILEGROUP -1 0 0 1905280
    test123440_4_Data ROWS_FILEGROUP -1 0 0 1280000
    test123440_7_Data ROWS_FILEGROUP -1 0 0 1920000
    test123440_8_Data ROWS_FILEGROUP -1 0 0 6400000
    test123440_9_Data ROWS_FILEGROUP -1 0 0 12800000
    test123440_Data ROWS_FILEGROUP -1 0 0 55040000
    test123441_25_data ROWS_FILEGROUP -1 0 0 17881600
    test123441_26_data ROWS_FILEGROUP -1 0 0 30497280
    test123441_27_Data ROWS_FILEGROUP -1 0 0 10240000
    test123441_28_Data ROWS_FILEGROUP -1 0 0 12800000
    test123441_29_Data ROWS_FILEGROUP -1 0 0 2816000
    test123441_30_Data ROWS_FILEGROUP -1 0 0 9777792
    test123441_31_Data ROWS_FILEGROUP -1 0 0 23040000
    test123441_32_Data ROWS_FILEGROUP -1 0 0 12806400
    test123441_33_Data ROWS_FILEGROUP -1 0 0 12806400
    test123441_34_Data ROWS_FILEGROUP -1 0 0 12806400
    test123441_35_Data ROWS_FILEGROUP -1 0 0 12806400
    test123441_36_Data ROWS_FILEGROUP -1 0 0 12800000
    test123441_37_Data ROWS_FILEGROUP -1 0 0 12800000
    test123441_38_Data ROWS_FILEGROUP -1 0 0 12800000
    test123441_39_Data ROWS_FILEGROUP -1 0 0 12800000
    test123441_40_Data ROWS_FILEGROUP -1 6400 0 12806400
    test123441_88_Data ROWS_FILEGROUP -1 128 0 1283200
    test123441_Index1 ROWS_FILEGROUP -1 0 0 13287296
    test123441_Index2 ROWS_FILEGROUP -1 0 0 65536000
    test123441_Index3 ROWS_FILEGROUP -1 0 0 39321600
    test123441_Index4 ROWS_FILEGROUP -1 0 0 64000000
    test123441_Index5 ROWS_FILEGROUP -1 0 0 27108992
    test123441_Index6 ROWS_FILEGROUP -1 0 0 50322560
    test123441_Index7 ROWS_FILEGROUP -1 0 0 96030720
    test123441_Index8 ROWS_FILEGROUP -1 0 0 70584320
    Friday, July 14, 2017 12:01 AM
  • Please avoid such double Posts: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f906eda7-a245-4c1f-a93f-0916225ac560/allocate-space-to-filegroup?forum=sqldataaccess

    No, it don't take space form an other file group.

    You have to move tables/index to an other file Group to get free space in the source file (group).


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by VijayKSQL Friday, July 14, 2017 9:15 PM
    Friday, July 14, 2017 5:57 AM
  • Make PRIMARY filegrououp as your DEFAULT on by checking the box in DBProperties->Filegroup

    Or Specify PRIMARY filegroup while  creating Objects.


    Dilip


    • Edited by SQL006 Friday, July 14, 2017 7:12 AM
    Friday, July 14, 2017 7:12 AM