MS SQL Server - Removing a Filegroup

Author

Removing a Filegroup

David Caddick

2005-12-28, 8:23 pm

About 3 years ago we were getting index corruptions on our SQL server
database. MS and SAP indicated it was a H/W problem but HP after performing a
lot of tests suggested the problem was a S/W problem. So to get the problem
resolved MS and SAP recommended placing the two tables I was having problems
with on a second File group, but indicated that we need to move the files
back to the PRIMARY filegroup before performing a SAP upgrade.

I have successfully moved the files back to the PRIMARY frlegroup but am
unable to to remove the filegroup. I have run the following queries:

use MD3
GO
ALTER DATABASE MD3
REMOVE FILEGROUP ISO601
GO

The results are:

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

I have run the following check to see if there are any other files in the
file group:

I have run the SQL query:

use MD3
dbcc checkfilegroup ('ISO601')

DBCC results for 'MD3'.

CHECKFILEGROUP found 0 allocation errors and 0 consistency errors in
database 'MD3'.
DBCC execution completed. If DBCC printed error messages, contact
your system administrator.

Uri Dimant

2005-12-29, 3:23 am

David

You need probably use DBCC SHRINKFILE command to empty the file
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)

"David Caddick" < DavidCaddick@discuss
ions.microsoft.com> wrote in message
news:108BE929-D337-43CD-83EB- 9200D66277D5@microso
ft.com...
> About 3 years ago we were getting index corruptions on our SQL server
> database. MS and SAP indicated it was a H/W problem but HP after
> performing a
> lot of tests suggested the problem was a S/W problem. So to get the
> problem
> resolved MS and SAP recommended placing the two tables I was having
> problems
> with on a second File group, but indicated that we need to move the files
> back to the PRIMARY filegroup before performing a SAP upgrade.
>
> I have successfully moved the files back to the PRIMARY frlegroup but am
> unable to to remove the filegroup. I have run the following queries:
>
> use MD3
> GO
> ALTER DATABASE MD3
> REMOVE FILEGROUP ISO601
> GO
>
> The results are:
>
> The filegroup 'ISO601' cannot be removed because it is not empty.
>
> I have run the following check to see if there are any other files in the
> file group:
>
> I have run the SQL query:
>
> use MD3
> dbcc checkfilegroup ('ISO601')
>
> DBCC results for 'MD3'.
>
> CHECKFILEGROUP found 0 allocation errors and 0 consistency errors in
> database 'MD3'.
> DBCC execution completed. If DBCC printed error messages, contact
> your system administrator.
>
>

Amish Shah

2005-12-29, 3:23 am

Try DBCC Shrinkfile with emptyonly
Look in books online for more details.

Regards
Amish

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links