DBCC SHRINKFILE (''filename'',EMPTYFILE)

  • 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

  • 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.

  • 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