single file in secondary filegroup cannot be removed ???
-
DBCC SHRINKFILE ( ALZDBA_Data_ObjectsDD20031130 , EMPTYFILE )
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
go
ALTER DATABASE [ALZDBA] remove FILE N'ALZDBA_Data_ObjectsDD20031130'
GO
-- Server: Msg 5042, Level 16, State 1, Line 1
-- The file 'ALZDBA_Data_ObjectsDD20031130' cannot be removed because it is not empty.
ALTER DATABASE [ALZDBA] remove FILEGROUP [ObjectsDD20031130]
GO
-- Server: Msg 5042, Level 16, State 7, Line 1
-- The filegroup 'ObjectsDD20031130' cannot be removed because it is not empty.
SELECT sysFile.groupid AS GroupID
, SUBSTRING(sysFile.groupname,1,30) AS FilegroupName
, SUBSTRING(sysObj.name,1,30) AS ObjectName
FROM sysobjects sysObj
INNER JOIN sysindexes sysIdx
ON sysObj.id = sysIdx.id
INNER JOIN sysfilegroups sysFile
ON sysIdx.groupid = sysFile.groupid
WHERE sysIdx.indid = 0
and sysFile.groupname = 'ObjectsDD20031130'
ORDER BY sysFile.groupname, sysObj.ObjectName
-- GroupID FilegroupName ObjectName
-- ------- ------------------------------ ------------------------------
--
-- (0 row(s) affected)
Any hints to get rid of this file and filegroup ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Check dbcc shrinkfile (EMPTYFILE parameter).
First you need to move all the data that is in the file to another file of the same filegroup. And then you will be able to drop the file.
-
I must have had a very week moment
The pitfall was the query !
SELECT sysFile.groupid AS GroupID
, SUBSTRING(sysFile.groupname,1,30) AS FilegroupName
, SUBSTRING(sysObj.name,1,30) AS ObjectName
FROM sysobjects sysObj
INNER JOIN sysindexes sysIdx
ON sysObj.id = sysIdx.id
INNER JOIN sysfilegroups sysFile
ON sysIdx.groupid = sysFile.groupid
-- ommitted the where-clause because indexes can reside in an other filegroup
-- than their table
-- WHERE sysIdx.indid = 0 -- heaps
-- or sysIdx.indid = 1 -- clustered indexes (was missing)
and sysFile.groupname = 'ObjectsDD20031130'
ORDER BY sysFile.groupname, sysObj.ObjectName
This still resulted in one table residing in that filegroup !
I moved the table and then I could drop the file and the filegroup.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply