Post reply

Not able to remove the filegroup from db

  • March 13, 2009 at 2:44 am

    #155409

    Hi,

    I have removed the file from the db after I made it as empty.

    I can not remove the filegroup from db.

    I got the below error when I try to remove the filegroup from db.

    Server: Msg 5042, Level 16, State 8, Line 1

    The filegroup 'NEW' cannot be removed because it is not empty.

    Please suggest what is the best way to remove.

    Note: I dont want to work with system table to delete the filegroup.

    Thanks,

    Kindest Regards,

    karthik

  • March 13, 2009 at 2:50 am

    #958941

    karthikeyan (3/13/2009)

    Hi,

    I have removed the file from the db after I made it as empty.

    I can not remove the filegroup from db.

    I got the below error when I try to remove the filegroup from db.

    Server: Msg 5042, Level 16, State 8, Line 1

    The filegroup 'NEW' cannot be removed because it is not empty.

    Please suggest what is the best way to remove.

    Note: I dont want to work with system table to delete the filegroup.

    Thanks,

    Hi there,

    In order for us to get a more detailed understanding of your database configuration could you please post the results of the query:

    sp_helpdb 'DatabaseName'

    This will also confirm that the Filegroup that you are attempting to remove is not the PRIMARY Filegroup.

    Cheers,

    John Sansom (@sqlBrit) | www.johnsansom.com

  • March 13, 2009 at 3:01 am

    #958945

    I ran your command and it does not showed the filegroup.

    but the filegroup name is reflected in the sysfilegroup system table.

    Thanks,

    Kindest Regards,

    karthik

  • March 13, 2009 at 3:17 am

    #958951

    karthikeyan (3/13/2009)

    I ran your command and it does not showed the filegroup.

    Ah, perhaps you are not using SQL Server 2005.

    Example execution on my platform, although somewhat difficult to read.

    sp_helpdb 'SANDBOX'

    name db_size owner dbid created status compatibility_level

    SANDBOX 301.00 MB sa 6 Jan 23 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 90

    name fileid filename filegroup size maxsize growth usage

    SANDBOX 1 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SANDBOX.mdf PRIMARY 307200 KB Unlimited 102400 KB data only

    SANDBOX_log 2 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SANDBOX_log.ldf NULL 1024 KB 2147483648 KB 10% log only

    What I am basically interested in is the details of your file structure.

    John Sansom (@sqlBrit) | www.johnsansom.com

  • March 17, 2009 at 5:58 pm

    #961510

    Why don't you try this: Create a new filegroup with same name "NEW" in your case under the same database and then try deletng it. Hopefully it should delete the entries from sysfilegroups.

    MJ

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

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