Post reply

Problem Deleting a File from Primary Filegroup

  • November 12, 2008 at 12:53 pm

    #201332

    I had database that was using two data files in the Primary Filegroup. One file on drive E: and one on drive F: After a year or two, drive E: was getting tight on space, but drive F: had a lot of free space. I decided to try to consolidate the two file to a single file on drive F: and free up space on E: The log file is on drive D: and does not have any problems.

    I went into Enterprise Manager, Shrink File task, Files option and chose Shrink action of "Empty the file (data will migrate to other files in the file group)

    Once that finished, I opened Database Properties, Data Files selected the drive E: file and tried to delete it. After confirming I want to delete it, I got

    "Error 5020: The primary data or log file cannot be removed from a database."

    And I noticed to my horror that my data file now resided in the E: recycler directory

    Any suggestions on what to try next would be greatly appreciated. No operational problems have been reported by the users.

  • November 12, 2008 at 1:04 pm

    #897744

    What do you see if you "select * from sysfiles" when connected to the database in question?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • November 12, 2008 at 1:14 pm

    #897750

    Here's the results of select * from sysfiles. Sorry, I can't find a way of making it format better, but bottom line, it shows my data file in the recycle bin also

    fileidgroupidsizemaxsizegrowthstatusperfnamefilename

    112423512-11010813460OPPS_2006_Data E:\RECYCLER\OPPS_2006_Data.MDF

    20128-125600328340OPPS_2006_Log D:\OPPS_2006_Log.LDF

    312434992-11010813460OPPS_2006_1_Data F:\Sqldata\OPPS_2006_Data.MDF2

    (3 row(s) affected)

  • November 13, 2008 at 5:09 am

    #897994

    Massage is correct you cannot delete the primary data file from your database. In your case its file in E drive so you won't be able to drop it.

    MJ

  • November 13, 2008 at 9:36 am

    #898175

    So Enterprise Manager can move the file to the recycling bin (and has done so) , but cannot delete it from the database?

    I'm not questioning your statement, it matches what I'm seeing, but I am questioning the sanity of the Microsoft Development team.

    Guess it's time to build a new database in parallel and swap names.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply