August 30, 2007 at 9:24 am
Hi
I need to move indexes back into the mdf files. My predecessor did this to improve performance but the plonker used the same physical disk. My Database is called WLR_England_15 and my index WLREngland_index.
I've tried DBCC SHRINKFILE(WLREngland_Index, EMPTYFILE) and it seems to work with no errors. but when I try to remove the file it says not empty. I've also tried a restore with the move option for the index file back into the mdf file with the following command
restore database WLR_England_15
from disk ='E:\temp\wlr15.bak' with
move 'WLREngland_index' to 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\WLR_England_15\WLR_England_15.mdf'
but I get
File 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\WLR_England_15\WLR_England_15.mdf' is claimed by 'WLREngland_Index'(3) and 'WLREngland_Data'(1). The WITH MOVE clause can be used to relocate one or more files.
Any one any ideas
Thanks
keith
August 30, 2007 at 10:18 am
I think what you want to do first is identify all the indexes on the secondary data file and rebuild them onto the primary data file. Then you should be able to drop the secondary file. The problem is that while it has database objects still on it, it can't be dropped.
August 31, 2007 at 2:53 pm
There is no way to tell which index is in which data file if the tables and indexes are in the same filegroup. However, if the indexes are in a different filegoup, you can identify them with this script:
select o.Name as TableName, fg.groupname as FileGroupName
from sysobjects o
inner join sysindexes i
on i.id = o.id
inner join sysfilegroups fg
on i.groupid = fg.groupid
where type = 'U'
and i.indid in (0,1)
order by tablename asc
Otherwise you should be able to use:
USE WLR_England_15
GO
DBCC SHRINKFILE ('WLREngland_Index',EMPTYFILE)
GO
ALTER DATABASE WLR_England_15
REMOVE FILE WLREngland_Index
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply