November 30, 2006 at 10:24 am
I have a database that currently has 1 large data file (60GB). I am thinking about creating some other files and using the DBCC SHRINKFILE ('filename',EMPTYFILE) command to move some of this data to some other files to make restoring this database on to other servers that don't have enough continuous disk space on a drive to facilitate it.
Has anyone done this before? Has anyone run into problems doing this?
Thanks
December 1, 2006 at 3:30 am
Hi, creating some other files is a good idea if you have to restore your db on a server with less diskspace on one partition, but the dbcc shrinkfile (I think) will only shrink the physical file you specified, I use this command generally when the transaction log is huge and when the backup of the DB is done.
December 1, 2006 at 8:18 am
You can move the indicies using the DROP_EXISTING option for create index. The data is moved by moving the clustered index. If the table does not have a clustered index, then create an new index that is clustered specifing the target file group and then drop the index.
For primary key and uniqueness constraints, just move the underlying index.
use northwind
go
exec sp_helpfile
go
ALTER DATABASE northwind ADD FILEGROUP Orders
GO
ALTER DATABASE northwind ADD FILE
( NAME = Orders
, FILENAME = 'F:\SQL2KMSDE\MSSQL\Data\northwind_orders.ndf'
, SIZE = 2MB, MAXSIZE = 100MB, FILEGROWTH = 5MB
)
TO FILEGROUP Orders
GO
-- index is actually the primary key constraint
create unique clustered index PK_Orders on Orders (OrderID)
with DROP_EXISTING
on Orders
GO
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply