Not able to remove the filegroup from db

• karthikeyan-146504

  Ten Centuries

  Points: 1055

  More actions

  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
  

• John.Sansom

  SSCertifiable

  Points: 7335

  More actions

  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

• karthikeyan-146504

  Ten Centuries

  Points: 1055

  More actions

  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
  

• John.Sansom

  SSCertifiable

  Points: 7335

  More actions

  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'

  namedb_sizeownerdbidcreatedstatuscompatibility_level

  SANDBOX 301.00 MBsa6Jan 23 2009Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics90

  namefileidfilenamefilegroupsizemaxsizegrowthusage

  SANDBOX1C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SANDBOX.mdfPRIMARY307200 KBUnlimited102400 KBdata only

  SANDBOX_log2C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SANDBOX_log.ldfNULL1024 KB2147483648 KB10%log only

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

  ---

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

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  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