October 20, 2011 at 8:50 am
Stefan Krzywicki (10/20/2011)
If you backup the transaction log and change the Recovery Mode to Simple you should be able to delete the second file. Then you can change it back to Full.Or have you tried that already? That's just how I was able to get rid of my secondary transaction log recently. It helped that we were switching to Simple mode anyway.
I have not tried that yet. I can test in Dev, see how it works. If it does, then I should be able to work it in Production at the wee hours of the morning. (I hope).
October 20, 2011 at 8:52 am
Brandie Tarvin (10/20/2011)
Stefan Krzywicki (10/20/2011)
If you backup the transaction log and change the Recovery Mode to Simple you should be able to delete the second file. Then you can change it back to Full.Or have you tried that already? That's just how I was able to get rid of my secondary transaction log recently. It helped that we were switching to Simple mode anyway.
I have not tried that yet. I can test in Dev, see how it works. If it does, then I should be able to work it in Production at the wee hours of the morning. (I hope).
Good luck! I hope it works.
--------------------------------------
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 20, 2011 at 8:58 am
Stefan Krzywicki (10/20/2011)
If you backup the transaction log and change the Recovery Mode to Simple you should be able to delete the second file. Then you can change it back to Full.Or have you tried that already? That's just how I was able to get rid of my secondary transaction log recently. It helped that we were switching to Simple mode anyway.
I tried this in Dev. It did not work. Next step is to fight with corporate to get the DAC password so I can see if these DAC readable only tables have any more information than I can get with just sysadmin access.
October 20, 2011 at 9:07 am
george sibbald (10/20/2011)
If you have a full backup I would be interested to see which files are returned by restore filelistonly.
DAMN. I forgot we're using non-SQL native DeDup backup technology now. RESTORE FILELISTONLY doesn't work because I don't have a SQL media to point it to.
So much for checking this one.
October 20, 2011 at 9:36 am
The only time I've seen this is with tempdb and a SQL Server Service restart fixes that.
A restart might fix this as well. This may not be an option in this case though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2011 at 9:42 am
Jack Corbett (10/20/2011)
The only time I've seen this is with tempdb and a SQL Server Service restart fixes that.A restart might fix this as well. This may not be an option in this case though.
I may be able to test this in Dev, just to check.
I did come up with another idea to check the RESTORE FILELISTONLY. I'll post updates in a bit.
October 20, 2011 at 9:49 am
Brandie Tarvin (10/20/2011)
george sibbald (10/20/2011)
If you have a full backup I would be interested to see which files are returned by restore filelistonly.DAMN. I forgot we're using non-SQL native DeDup backup technology now. RESTORE FILELISTONLY doesn't work because I don't have a SQL media to point it to.
So much for checking this one.
Note to self. RESTORE FILELISTONLY has a "DISK" option. DUH.
Note to George: Guess what? This also works on the differential backups, not just the full backups. I did not know that until today.
Both the full and the diff backups listed all three files, George. 1 data, 2 log files.
October 20, 2011 at 9:53 am
Brandie Tarvin (10/20/2011)
Jack Corbett (10/20/2011)
The only time I've seen this is with tempdb and a SQL Server Service restart fixes that.A restart might fix this as well. This may not be an option in this case though.
I may be able to test this in Dev, just to check.
Jack,
It occurs to me that we have stopped and restarted the SQL Server Service a number of times since we added this log file. We only noticed the problem with trying to remove it or stop autogrowth this morning. If the file "doesn't exist" and previous restarts haven't done anything to fix it, why should I expect a restart now to fix the problem when it didn't before?
I'm still going to test in Dev, but I'd love to hear your thoughts on this matter.
October 20, 2011 at 10:08 am
Ridiculous question, but when you check the location of the secondary log file with a query, is it reporting the correct location?
--------------------------------------
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 20, 2011 at 10:31 am
Brandie Tarvin (10/20/2011)
Brandie Tarvin (10/20/2011)
Jack Corbett (10/20/2011)
The only time I've seen this is with tempdb and a SQL Server Service restart fixes that.A restart might fix this as well. This may not be an option in this case though.
I may be able to test this in Dev, just to check.
Jack,
It occurs to me that we have stopped and restarted the SQL Server Service a number of times since we added this log file. We only noticed the problem with trying to remove it or stop autogrowth this morning. If the file "doesn't exist" and previous restarts haven't done anything to fix it, why should I expect a restart now to fix the problem when it didn't before?
I'm still going to test in Dev, but I'd love to hear your thoughts on this matter.
Even though the SQL Server Service ahs been restarted several times, do you know when this issue started? Perhaps after the last restart. I'm not really expecting it to fix it, but it might.
What happens if you restore one of those backups on another machine? Does the 3rd file get created and can you then remove it?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2011 at 11:28 am
Stefan Krzywicki (10/20/2011)
Ridiculous question, but when you check the location of the secondary log file with a query, is it reporting the correct location?
Not at all a ridiculous question.
It's reporting the same physical location as the other files. But since we've moved to new servers, I don't have the security permissions necessary to go out and eyeball that location myself. No one on my team does (we're sharing a corporate SQL box).
October 20, 2011 at 11:29 am
Jack Corbett (10/20/2011)
Even though the SQL Server Service ahs been restarted several times, do you know when this issue started? Perhaps after the last restart. I'm not really expecting it to fix it, but it might.
Good point. I don't know exactly when the issue started.
Jack Corbett (10/20/2011)
What happens if you restore one of those backups on another machine? Does the 3rd file get created and can you then remove it?
We restore quite regularly down to Dev, Test, & QC. This behavior is occurring environments and I am unable to remove or modify the file in any of them.
October 20, 2011 at 11:31 am
Brandie Tarvin (10/20/2011)
Stefan Krzywicki (10/20/2011)
Ridiculous question, but when you check the location of the secondary log file with a query, is it reporting the correct location?Not at all a ridiculous question.
It's reporting the same physical location as the other files. But since we've moved to new servers, I don't have the security permissions necessary to go out and eyeball that location myself. No one on my team does (we're sharing a corporate SQL box).
I was thinking (though I don't know if it is possible) that the file simply isn't there or is in the wrong physical place. SQL Server would just use the other transaction file as it can't find this one. Your attempts to drop it wouldn't work because SQL Server can't find it.
--------------------------------------
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 20, 2011 at 12:18 pm
The only other time I've seen an issue where a log file couldn't be found, and in this case it was the only log file, was when the location was on an iSCSI NAS/SAN and the iSCSI connection was shared between IO and public network and was being overwhelmed. It actually caused a cluster failover.
This doesn't sound like it is the issue here.
Can you get an archived backup from weeks ago, restore it, and remove the file? This is a really odd one. You may want to get PSS involved here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 24, 2011 at 6:29 am
It looks like I will have to get Microsoft involved in this one. I can't get the DAC account from corporate because I don't have remote server access. They have confirmed, however, that the file physically exists.
Sigh. I wish we still had our own servers.
EDIT: Thanks for the assist, everyone.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply