October 24, 2011 at 7:15 am
Brandie i have seen similar to this before, what is the recovery model of the database, if full do you take transaction log backups of this database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2011 at 7:24 am
Perry Whittle (10/24/2011)
Brandie i have seen similar to this before, what is the recovery model of the database, if full do you take transaction log backups of this database?
Yes to both questions.
October 24, 2011 at 8:26 am
We recently had an issue where we created all of our log files in a location other than the default location. However, "someone" deleted the actual folder that the default was pointing to. Upon SQL restart, this caused errors all over the place. Once I created the empty folder in the default path, all of the errors stopped. I then changed the default path to the correct path and deleted the old one. Not sure if this even pertains to your issue, but thought MAYBE it may help.
Jared
Jared
CE - Microsoft
October 24, 2011 at 8:30 am
jared-709193 (10/24/2011)
We recently had an issue where we created all of our log files in a location other than the default location. However, "someone" deleted the actual folder that the default was pointing to. Upon SQL restart, this caused errors all over the place. Once I created the empty folder in the default path, all of the errors stopped. I then changed the default path to the correct path and deleted the old one. Not sure if this even pertains to your issue, but thought MAYBE it may help.Jared
You are correct that we aren't using the default folder, but the database is working, we've verified the files actually exist, and there are no errors during normal usage. Only when we try to modify the secondary log file (or remove it) do we receive the errors.
October 24, 2011 at 8:38 am
There is a way to get rid of the file, but it is a lot of work and I don't know if it is worth it. Create a new database with the files the way you want them, move all the data, drop the old database, move/rename the newly created database.
I don't know if your uptime requirements would even allow that and I'd only undertake the effort if you get no answers from Microsoft.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 24, 2011 at 8:41 am
Stefan Krzywicki (10/24/2011)
There is a way to get rid of the file, but it is a lot of work and I don't know if it is worth it. Create a new database with the files the way you want them, move all the data, drop the old database, move/rename the newly created database.I don't know if your uptime requirements would even allow that and I'd only undertake the effort if you get no answers from Microsoft.
She can do whatever she wants... except take it offline. Which is a little limiting IMHO :hehe:.
October 24, 2011 at 8:44 am
Ninja's_RGR'us (10/24/2011)
Stefan Krzywicki (10/24/2011)
There is a way to get rid of the file, but it is a lot of work and I don't know if it is worth it. Create a new database with the files the way you want them, move all the data, drop the old database, move/rename the newly created database.I don't know if your uptime requirements would even allow that and I'd only undertake the effort if you get no answers from Microsoft.
She can do whatever she wants... except take it offline. Whici is a little limiting IMHO :hehe:.
Yeah, a little bit.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 24, 2011 at 10:09 am
Hey, if I really didn't mind finding a little pink slip in my inbox, I could delete the silly thing and start over from scratch. @=)
June 4, 2013 at 12:21 pm
Hi Brandie,
I'm getting exact same issue as you on 2 databases.
I've restored databases into a new environment.
I restored them as they were which meant 2 log files each.
I did 2 log backups before I could get the logs cleared then I did
dbcc shrinkfile(xxx, emptyfile)
then in properties I could see 2nd log was very small and tried to remove it or at least disable autogrowth but then I get the same errors as you.
Physically, the second log files have actually gone it's just the database properties thinks it's still there.
I'm on SQL 2012 Enterprise SP1 using AlwaysOn.
Did you find a solution that did not require a change of employer? 😉
Cheers
Chris
June 5, 2013 at 4:52 am
Yes, actually, I did. I had to use the DAC to log into SQL Server (I think I did it through SSMS), add the file, then remove it.
Turns out the second log file actually had an .ndf extension which made it impossible for SQL to reconcile the removal / change.
I did indeed use the steps in this article to resolve the issue: http://connect.microsoft.com/SQLServer/feedback/details/482820/orphaned-log-file-can-not-delete-log-file-sysfiles1-duplicate-names
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply