February 27, 2015 at 9:29 pm
I have a SQL Server 2008 R2 Cluster. I have moved a file to another drive by adding a new file on the drive, doing a shrinkfile with emptyfile. I have removed the old file and when I try to rename the new file to the old files old name, I get an error.
ALTER DATABASE mydb REMOVE FILE myfilename
Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.
ALTER DATABASE mydb
modify FILE ( NAME = temp_filename
, NEWNAME = myfilename
)
Msg 1828, Level 16, State 3, Line 4
The logical file name "myfilename" is already in use. Choose a different name.
I have completed a failover, thinking the name was being held in cache. I've also renamed to something completely different then tried to rename to the old filename.
Thanks in advance.
Pete
February 28, 2015 at 12:12 am
petemill66 (2/27/2015)
I have a SQL Server 2008 R2 Cluster. I have moved a file to another drive by adding a new file on the drive, doing a shrinkfile with emptyfile. I have removed the old file and when I try to rename the new file to the old files old name, I get an error.ALTER DATABASE mydb REMOVE FILE myfilename
Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.
ALTER DATABASE mydb
modify FILE ( NAME = temp_filename
, NEWNAME = myfilename
)
Msg 1828, Level 16, State 3, Line 4
The logical file name "myfilename" is already in use. Choose a different name.
I have completed a failover, thinking the name was being held in cache. I've also renamed to something completely different then tried to rename to the old filename.
Thanks in advance.
Pete
Quick question, it looks to me that you removed the file from the file system before altering the database? If so, any attempts to remove or alter the file properties will fail.
😎
February 28, 2015 at 8:10 am
HA! I understand your confusion. I put that piece of code in there to show the file doesn't exist. I wish it were that easy! 🙂
February 28, 2015 at 8:37 am
Double checked it doesn't exist on either of the nodes?
😎
February 28, 2015 at 1:03 pm
Nope.. Does not exist. The datafile is on a shared drive. It's driving me crazy. :crazy:
March 2, 2015 at 8:43 am
The other datafile changes I've made the same thing happened. I just had to wait until the next day to change the file. No such luck with this one.
March 2, 2015 at 9:24 am
what is the recovery model set to on this database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 2, 2015 at 9:49 am
Its in FULL recovery. There have been several backups since we've updated the file structure. We are still unable to rename the file back to the original name.
March 2, 2015 at 10:31 am
do you have log backups running on this database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 2, 2015 at 10:56 am
Yep.. Log files every 15. Nightly full backups.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply