SQL Server Performance

Migrate data to other files in filegroup

Discussion in 'General DBA Questions' started by mhweiss2, Jul 19, 2006.

  1. mhweiss2 New Member

    Using SQL Server 2000 Enterprise Edition, I have migrated the data in one large data file to two other files in the same filegroup. After the operation completed successfully, I noticed that there remains approximately 1.5 Mb of "used" space in the file. My question is, can I delete this file now even though Sql Server says some of it is in use?
    Thank you,
  2. shashank sharma New Member

  3. FrankKalis Moderator

    I don't think SQL Server will let you use the file right now. There might be some statistics still stored in that file. If you're absolutely sure, there is no other data in that file you can use DBCC SHRINKFILE with the EMPTYFILE option and thereafter ALTER DATABASE ...REMOVE FILE...

    Frank Kalis
    Microsoft SQL Server MVP
    Heute schon gebloggt?http://www.insidesql.de/blogs
  4. mhweiss2 New Member

    Thank you... I am as sure as I can be that the file does not contain any data - I shrank the file with the empty file option on via Enterprise Manager and the process completed without error so I can only assume that the file is empty and whatever space is used in it is due to statistics, etc left over that perhaps Sql Server does not clear out?
    Would it be safer to detach the file first and see if any errors are generated before deleting it?
    Also, speaking of deleting files, in EM under the Files tab of the properties dialog for a database there is the option to delete a selected file. After using EM to migrate the data to the other files in the filegroup, how safe is it to use this method to remove the (hopefully!) empty file?
    Thank you,

Share This Page