Friday, February 3, 2012

SQL Server 2005/2008 : Remove data or log file from a database

If you want to remove any data or any log file from a database then you can use the EMPTYFILE option with DBCC SHRINKFILE to first empty it and then remove it.

1) Make sure that there is at least one additional file in that filegroup, from where you are trying to remove  a file. Also make sure that the remaining files in the filegroup have enough space for you to move the contents to them.

    Use the following command to empty the file
    DBCC SHRINKFILE (N'AdventureWorks_data2' , EMPTYFILE)

    Then remove the particular file using the following command

    ALTER DATABASE [AdventureWorks_new]  REMOVE FILE [AdventureWorks_data2]

2) You can also use the GUI to do this. Right click on the database Go to Tasks > Shrink > Files. Then choose the filegroup and then the file and below pick the option to 'Empty file by migrating the data to other files in the same filegroup'. Then click OK. Later open the database properties pick the file that you want to delete and REMOVE it.


1 comment:

  1. And you must take T log backup otherwise you will see the file (offline) when you query Sys.database_files.