Removing a Filegroup

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.

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.
>
>

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

Regards
Amish

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