undo dbcc shrinkfile emptyfile?

Question

Post reply

undo dbcc shrinkfile emptyfile?

hoo-t

SSChampion

Points: 12713
More actions
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

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Restore your latest known good backup?

There is a backup, right..?

/Kenneth

hoo-t SSChampion Points: 12713 More actions · Answer 2

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

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

hoo-t SSChampion Points: 12713 More actions · Answer 4

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

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

Tris Grasshopper Points: 22 More actions · Answer 6

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

hoo-t SSChampion Points: 12713 More actions · Answer 7

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

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

/Kenneth