August 2, 2010 at 9:18 am
Hi,
SQL 2000 SP3, MS 2003 server Std.
I was re-partitioning hard drive space on my SQL Server. I made a slight error.
I accidentially COPIED a partition over the partition containing my Sql log ldf files. :w00t: Whoops!
The server came back up, and when I went into Enterprice manager, I got an error that it could not be logged into.
I right clicked and entered "edit SQL server registration properties" and did a "SA blank pass" to get back in. I can't recall but I have an SQLADMIN user, but it would not take those credentials. I did log back in ok with SA.
Why would the log file debachery have any implication on loggin into the LOCAL SQL server?
Next, without ldf files where they should have been, it put ALL my DB's with log files that were on that partition in "SUSPECT" state.
When I realized the error, I restored the ldf files to their original location.
I assumed that restarting the server would remedy my issue as the server would *look* for the ldf files in the same location. Wishfull thinking on my part.
I had to restore from backup all my databases and was at work friday night till midnight. :crazy:
I tried the *update sysdatabases* lines, but this did not work. I could not get the DB's back up.
It seems to me that their should be an easy fix to this however, I did not corrupt anything, the ldf just went missing.
Was restoring my only option? Was I misisng an easy fix here?
(I hope SQL 2005 or 2008 has a better way to cope with such things? I do need to upgrade soon)
Why I ask is the same would happen if I lost a RAID array that my logs may have been on. (logs on different set of disks as best practice). I hope this would not be a cause for total DB restore?
Thanks,
MP
August 2, 2010 at 9:39 am
mrpush (8/2/2010)
Why would the log file debachery have any implication on loggin into the LOCAL SQL server?
Because the login that you used had a default database set to one of the DBs that couldn't be opened. Hence login failed with a reason of 'cannot open default database'. There are ways around that, specifically by explicitly requesting a different database in the login. Can't recall how to do that with EM.
I assumed that restarting the server would remedy my issue as the server would *look* for the ldf files in the same location. Wishfull thinking on my part.
It is possible that an offline/online or detach/attach would have fixed this. Possible, not certain.
It seems to me that their should be an easy fix to this however, I did not corrupt anything, the ldf just went missing.
My guess, the databases were not cleanly shut down, meaning that they could not be opened without the log file. Shouldn't have truly gone suspect, but I can't remember how SQL 2000 works with these things. On 2005 that would send the database RECOVERY_PENDING and, providing the correct log files can be located, is fixable.
Was restoring my only option? Was I misisng an easy fix here?
Only option - no. Best option - probably yes.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 11:42 am
GilaMonster (8/2/2010)
mrpush (8/2/2010)
Why would the log file debachery have any implication on loggin into the LOCAL SQL server?Because the login that you used had a default database set to one of the DBs that couldn't be opened. Hence login failed with a reason of 'cannot open default database'. There are ways around that, specifically by explicitly requesting a different database in the login. Can't recall how to do that with EM.
I see, that explains it
I assumed that restarting the server would remedy my issue as the server would *look* for the ldf files in the same location. Wishfull thinking on my part.
It is possible that an offline/online or detach/attach would have fixed this. Possible, not certain.
I did just try to do an offline/online on one of those DB's and got this:
Error 5181, could not restart database. Reverting back to old status. ALTER DATABASE statment failed. sp_dboption command failed
And now it's in the suspect\offline status.
Next, I took another DB and did the detach/attach:
Error 9003: The LSN (12:173:1) passed to log scan in database name is invalid
Can't re-attach that DB now
It seems to me that their should be an easy fix to this however, I did not corrupt anything, the ldf just went missing.
My guess, the databases were not cleanly shut down, meaning that they could not be opened without the log file. Shouldn't have truly gone suspect, but I can't remember how SQL 2000 works with these things. On 2005 that would send the database RECOVERY_PENDING and, providing the correct log files can be located, is fixable.
As for shutting down the DB's, I esentially just shut down the server as one would normally, start-shutdown. I would guess that would be as *clean* of a shutdown that can be performed?
Humm, now how do I restore a detached DB? It's not in the list anymore. :w00t:
Was restoring my only option? Was I misisng an easy fix here?
Only option - no. Best option - probably yes.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Thanks much,
MP
August 2, 2010 at 1:54 pm
Would you mind going back and editing that, and using the [ quote ] tag? It's hard to read where your replies are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2010 at 1:32 pm
Gail,
Did the tags, but they are same color as yours....I tried the color tags, but they did not work. Just showed HTML in post.
MP
August 4, 2010 at 3:22 pm
No, you want to end the quote before putting your reply in, not start another. Still hard to work out who said what. Like this:
GilaMonster (8/2/2010)
mrpush (8/2/2010)
Why would the log file debachery have any implication on loggin into the LOCAL SQL server?Because the login that you used had a default database set to one of the DBs that couldn't be opened. Hence login failed with a reason of 'cannot open default database'. There are ways around that, specifically by explicitly requesting a different database in the login. Can't recall how to do that with EM.
I see, that explains it
(quote the post to see how that works)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2010 at 8:47 am
GilaMonster (8/4/2010)
No, you want to end the quote before putting your reply in, not start another. Still hard to work out who said what. Like this:GilaMonster (8/2/2010)
mrpush (8/2/2010)
Why would the log file debachery have any implication on loggin into the LOCAL SQL server?Because the login that you used had a default database set to one of the DBs that couldn't be opened. Hence login failed with a reason of 'cannot open default database'. There are ways around that, specifically by explicitly requesting a different database in the login. Can't recall how to do that with EM.
I see, that explains it
(quote the post to see how that works)
Ok, let me tinker with this
August 5, 2010 at 8:49 am
mrpush (8/5/2010)
GilaMonster (8/4/2010)
No, you want to end the quote before putting your reply in, not start another. Still hard to work out who said what. Like this:GilaMonster (8/2/2010)
mrpush (8/2/2010)
Why would the log file debachery have any implication on loggin into the LOCAL SQL server?Because the login that you used had a default database set to one of the DBs that couldn't be opened. Hence login failed with a reason of 'cannot open default database'. There are ways around that, specifically by explicitly requesting a different database in the login. Can't recall how to do that with EM.
I see, that explains it
(quote the post to see how that works)
Ok, let me tinker with this
With no quote tag added by me
August 5, 2010 at 8:50 am
mrpush (8/5/2010)
mrpush (8/5/2010)
GilaMonster (8/4/2010)
No, you want to end the quote before putting your reply in, not start another. Still hard to work out who said what. Like this:GilaMonster (8/2/2010)
mrpush (8/2/2010)
Why would the log file debachery have any implication on loggin into the LOCAL SQL server?Because the login that you used had a default database set to one of the DBs that couldn't be opened. Hence login failed with a reason of 'cannot open default database'. There are ways around that, specifically by explicitly requesting a different database in the login. Can't recall how to do that with EM.
I see, that explains it
(quote the post to see how that works)
Ok, let me tinker with this
With no quote tag added by me
Gail, how did you reply to specific questions with them individually quoted in there?
August 5, 2010 at 8:54 am
mrpush (8/5/2010)
mrpush (8/5/2010)
mrpush (8/5/2010)
GilaMonster (8/4/2010)
No, you want to end the quote before putting your reply in, not start another. Still hard to work out who said what. Like this:GilaMonster (8/2/2010)
mrpush (8/2/2010)
Why would the log file debachery have any implication on loggin into the LOCAL SQL server?Because the login that you used had a default database set to one of the DBs that couldn't be opened. Hence login failed with a reason of 'cannot open default database'. There are ways around that, specifically by explicitly requesting a different database in the login. Can't recall how to do that with EM.
I see, that explains it
(quote the post to see how that works)
Ok, let me tinker with this
With no quote tag added by me
Gail, how did you reply to specific questions with them individually quoted in there?
This seems overly complicated to me....many blog type interfaces allow simple controls, and you don't have to know any html tags....I'm not pickin on it, I just don't think its easy or intuitive.
Is there a tutorial on this, sorry, I'm just not getting this layout.... and how to selectively QUOTE different portions without having to pick throuh all the text and *** tags....
August 5, 2010 at 10:01 am
mrpush (8/5/2010)
Gail, how did you reply to specific questions with them individually quoted in there?
By adding a [ /quote] at the end of the chunk I'm commenting on, adding my comment, then putting a [ quote]
Or by copying the text I want to quote, selecting it and clicking on the shortcut tag in the box to the left.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2010 at 10:08 am
Ok, just copy out, plain text, what pieces I said and what your replies were, wrap your replies in [ b ] and [ /b ] (or highlight and click the B button in the toolbar above the editing window.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2010 at 10:59 am
GilaMonster (8/5/2010)
mrpush (8/5/2010)
Gail, how did you reply to specific questions with them individually quoted in there?By adding a [ /quote] at the end of the chunk I'm commenting on
Ok, here is my add. I will bold the replies as you asked, sorry for all the mess.
adding my comment, then putting a [ quote]
Or by copying the text I want to quote, selecting it and clicking on the shortcut tag in the box to the left.
August 5, 2010 at 11:00 am
mrpush (8/5/2010)
GilaMonster (8/5/2010)
mrpush (8/5/2010)
Gail, how did you reply to specific questions with them individually quoted in there?By adding a [ /quote] at the end of the chunk I'm commenting on
Ok, here is my add. I will bold the replies as you asked, sorry for all the mess.
adding my comment, then putting a [ quote]
Or by copying the text I want to quote, selecting it
and clicking on the shortcut tag in the box to the left.
Ok, thanks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply