Filegroup dropping problem
-
When trying to drop some filegroups with EM, I'm running into some trouble. Before the group can be dropped, the associated files it contains must be dropped, and any objects contained in those files must be dropped first.
Well, after going through the process of dropping the objects in a particular file, EM still complains that the file contains an object (so I can't drop the file). I've actually gone through my database and dropped every table, sproc, view and everything else I can think of that I've created, and it STILL thinks the file contains one object.
How can you find out what a particular file contains, 'objectwise'?
Thanks,
Chuck
-
What objects are still there? Is this the first filegroup? If so, you cannot drop it. It will have the system tables in it.
Steve Jones
-
That's just it, I don't know. I'm trying to drop the file itself associated within a particular filegroup, but EM doesn't say what object is still in it, just that there IS one.
So really what I'd like is some type of procedure to find out what objects belong in what filegroups/files. We tried iterating through sysobjects with a cursor, checking for particular fileid's, but that turned up nothing; EM still complains that an object is in the file/group.
Chuck
-
By the way, it's NOT the PRIMARY filegroup; I know you can't drop that one.
Chuck
-
Alright this will help you look at your tables to see which ones exist on which filegroups.
It uses an undocumented SP sp_objectfilegroup and sp_MSForEachTable to loop thru all the tables. The only thing it will not tell you is is a text/image/ntext item from a table has been assigned to a specific group.
Try this
EXEC sp_MSForEachTable '
DECLARE @tbl VARCHAR(255)
DECLARE @id INT
SET @tbl = REPLACE(''?'',''[dbo].'','''')
SET @id = OBJECT_ID(@tbl)
PRINT @tbl
EXEC sp_objectfilegroup @id'
You will get a lot of output but it will be similar to this
tblname
colid --for recordset output from sp_objectfilegroup
---------------------------------------------------------------
filegrouptableison
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
select a.name, filegroup_name(b.groupid) from sysobjects a, SYSINDEXES b where a.id = b.id
and b.groupid = filegroup_id('Primary')
Try this to find out objects in any file group.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA -
OK, neither one of those shows what I'm looking for (they all display that everything belongs to PRIMARY).
Let me provide some additional info. We create separate file groups for each week of data. We do this in order to easily drop off old data without having to execute DELETE statements and eating up a lot of time (massive volume).
Basically, in addition to the PRIMARY group, we create groups like GROUP_200216, GROUP_200217, etc. This corresponds to the year and 'week of year'. Each file group only has one associated file (no striping).
Of course, certain tables and indexes are created in each of the groups as well.
Then, to get rid of the groups, we drop all the tables from each of the groups, then we try to drop the file associated with a particular filegroup. This seemed to work just fine on most of the groups, but for some reason we get an error on two particular groups. The error is as follows:
Error 5042: The file 'GROUP_200216' cannot be removed because it is not empty.
This is the logical name of the file. The actual filegroup it belongs to is also called GROUP_200216. The file is 'E:\ImageClear\Database\ImageClear_200216_Data.ndf'
Running either of the above scripts shows only objects belonging to the PRIMARY group.
OR, does the file not being empty imply something else?
Chuck
-
What version of SQL are you running? Also take a look here, may be in some way related if you have text or image data in these filegroups. http://support.microsoft.com/default.aspx?scid=kb;en-us;Q254253
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Gee, I got all excited for a bit, but alas, I'm running SQL Server 2000, with all the latest fixpacks.
When trying to drop a file, how does SQL 2000 know if it's 'empty'? What establishes a file as 'empty', since it obviously has a non-zero byte size?
Thanks for the great help.
Chuck
-
Ok here is a thought as seems to be something I cannot think of off hand. Launch Profiler and do a trace of TSQL Stmt:Completed items for this database. At minimum show text in columns. Start trace and run drop of filegroup and see if it does anything specific that will point us to what you shoudl be looking for. If you have troubles feel free to save to a file and email me but I won't promise anything time wise as I am pretty busy at work right now with a new application I have to write.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Try this one...
set nocount on
create table #id (ndx int primary key identity,ob int)
create table #fg (ndx int primary key identity,fg varchar(255))
declare id_cursor cursor local read_only for
select
[id]
from
sysobjects
declare @id int
open id_cursor
fetch next from id_cursor into @id
while @@fetch_status=0
begin
insert into #id values (@id)
insert into #fg exec sp_objectfilegroup @id
fetch next from id_cursor into @id
end
close id_cursor
deallocate id_cursor
set nocount off
select
i.ob as ObjectId,
convert(char(32),f.fg) as FileGroup,
convert(char(32),s.name) as ObjectName
from
#id as i
join
#fg as f on f.ndx=i.ndx
join
sysobjects as s on s.id=i.ob
where
f.fg<>'not applicable'
order by
i.ob asc
drop table #id
drop table #fg
-
Add "WHERE xtype='U'" to the cursor for user tables only.
-
[I'm obsessing on this because I have exactly the same problem.]
After using my script (see above) to list the tables on the filegroup I want to drop, I still get a "not-empty" error for the DBCC SHRINKFILE(filegroup,EMPTYFILE) command.
My problem is that I still have a bunch of indices in the filegroup. You might want to check that on one of the offending tables!
-
I run these script to vreate filegroup
ALTER DATABASE CAPMANUAT ADD FILEGROUP FILEGROUP4
ALTER DATABASE CAPMANUAT ADD FILE (NAME = DAT1_FILEGROUP4, FILENAME = 'Y:\FL1_FILEGROUP4.NDF', SIZE = 500KB, FILEGROWTH = 10%) TO FILEGROUP FILEGROUP4
Then add some tables on this file group and drop them
Then try to drop datafile and filegroup by this command
ALTER DATABASE CAPMANUAT REMOVE FILE DAT1_FILEGROUP4
ALTER DATABASE CAPMANUAT REMOVE FILEGROUP FILEGROUP4
I got the same 5012 error file not empty
Then I run this query to find out anything exists on that file group
select name from sysindexes where groupid = filegroup_id('FILEGROUP4')
No records returned
Now I ran this command
DBCC SHRINKFILE(DAT1_FILEGROUP4,EMPTYFILE)
it works without errors.
Then try to drop datafile and filegroup by this command
ALTER DATABASE CAPMANUAT REMOVE FILE DAT1_FILEGROUP4
ALTER DATABASE CAPMANUAT REMOVE FILEGROUP FILEGROUP4
This time it works.....
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA -
The DBCC SHRINKFILE(file,EMPTYFILE) is an essential step. You cannot remove a file unless you do this because this is what tells SQL to deallocate the pages in the file.
It fails when there are objects in the file. This could be tables or indices. What is needed is a good "sp_helpfilegroupobjects" script to list stuff that you need to move.
Plus, doesn't anyone think that it's bizarre that you can move tables & indices between filegroups from EM, but that there's no TSQL suggested to do it in Books-on-Line?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply