May 27, 2005 at 8:40 am
Hi,
I have a database which has one .mdf file and 3 .ndf files and 3 .log files.
I want to transfer all the data from the 3 .ndf files to one .mdf file.
Also I want to have only one .log file from 3 .log files.
Is there a way of doing it?
Please help.
--Kishore
May 27, 2005 at 9:34 am
DBCC SHRINKFILE
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Microsoft® SQL Server™ no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement.
-->Shrink the first file delete it using Alter Database, shrink the second file delete it and so on.
May 30, 2005 at 2:34 am
You first need to move tables and indexes from the *.ndf files back to the *.mdf file. For tables this is fairly straightforward, just (re)create the clustered index WITH DROP EXISTING in the PRIMARY filegroup. This query might help you detect which object are where
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 IN(0,1) AND sysObj.xtype = 'U'
ORDER BY
sysFile.groupname, sysObj.ObjectName
In case you have indexes created on the other filegroups, you need to drop and recreate them in the PRIMARY filegroup. Once you have done this, you can use the above statement to empty the file. After that use ALTER DATABASE to drop the file(s).
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 30, 2005 at 7:47 am
So, we come to 2 conclusions :
If I use Emptyfile, the data will be moved to another file in the same filegrp. So if I have 1.ndf, 2.ndf and 3.ndf on Filegrp_2 and if i run shrinkfile on 2.ndf and 3.ndf, the data will be moved to 1.ndf. But however if I want to move the 1.ndf to *.mdf, then I will have to move tables to Primary filegrp and then only i can delete the .ndf file.
Anyways, thanks for the responses.
--Kishore
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply