Post reply

undo dbcc shrinkfile emptyfile?

  • April 16, 2004 at 2:49 pm

    #84894

    Anyone know if it is possible to undo a dbcc shrinkfile with the emptyfile option?

    BOL states: "Microsoft® SQL Server™ no longer allows data to be placed on the file used with the EMPTYFILE option."

    I went about this ALL WRONG, but in trying to move the log on my database, I created a new log file in the desired location, and used emptyfile on the original.  When I attempted to delete the original file, SQL issued the error: 'The primary data or log file cannot be removed from a database.'  

    I can't empty the new file, because the original can't be used.  I can't use sp_attach_single_file_db, because it can't be used when there are multiple log files. 

    Unless I can figure out how to undo the emptyfile option, it appears that I am stuck with BOTH files. 

     

    Steve

  • April 19, 2004 at 7:45 am

    #503327

    Restore your latest known good backup?

    There is a backup, right..?

    /Kenneth

  • April 19, 2004 at 7:35 pm

    #503411

    Kenneth,

    Thanks.  This process was started months ago.  The server was 7.0.  I could never get rid of the extra file, so finally I quit worrying about it.  Now, we are moving the database to a new server (SQL 2000), and I would like to get rid of one of the 2 files.  So far, the only thing I've come up with is to create a new database and DTS everything over to it.

    Steve

  • April 20, 2004 at 12:19 am

    #503421

    How did you try to remove the logfile?

    Did you use ALTER DATABASE with the REMOVE FILE option?

    According to BOL this should be how it's done, assuming the file is truly empty.

    /Kenneth

  • April 21, 2004 at 11:00 am

    #503688

    Alter database with remove file is what I tried.  The response was that the primary file cannot be removed.

  • April 22, 2004 at 1:29 am

    #503761

    Seems like a catch 22 situation.

    As you've noted, the emptyfile option 'locks' the original logfile to be ever used again, and the fact that it's the first logfile created in the db prevents it from being deleted. Seems like the only way to go is to start over again, like you were thinking.. Data out - drop - recreate...

    ..just don't forget to backup beforehand just in case..

    /Kenneth

  • April 22, 2006 at 9:06 am

    #633653

    You could try a detach database and then attach single file option - this will just recreate a log file for you and you just pretend that you lost the old one - check in BOL for this but I know it works - just make sure you take a full backup and stop people from accessing it (single user/dbo mode while you do it)

    Job done

  • April 22, 2006 at 2:57 pm

    #633661

    Trimel,

    Great suggestion!  I don't think I tried that.  Unfortunately, this post was from 2 years ago.  I think I ended up recreating the database.  Now, the database is no longer with the company, and neither am I!

    Thanks for your suggestion!

    Steve

  • April 24, 2006 at 1:28 am

    #633701

    Did you notice that it was two years to the day, even?

    /Kenneth

Viewing 9 posts - 1 through 8 (of 8 total)

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