December 3, 2010 at 11:23 am
Last night a hardware failure crashed everything and left me with a SUSPECT msdb on a SQL 2000 server. I've tired everything I can find to fix this and I can not get it to let me recreate the msdb database even when starting the server with the traceflag 3608 .
My backups are trashed for this server (long story) but it had no jobs or dts packages on it so thje MSDB was a plain old msdb and so I thought I had gotten lucky, but alast I have not.
I found several (a lot) posts referencing these instructions on what tod o if you have no backup of the MSDB database.
1) Start SQL Server with the tracflag 3608
2) Rename/Move your msdb data & log files
3) Locate and run the script instmsdb.sql that is located in the INSTALL folder of your SQL Server install
4) Shutdown & restart server without the 3608 trace flag
This doesn;t work for me because when I get to step 2 I get the eror that the fiel is in use and can not be renamed or moved. I have no idea why its locked. I started the server with the trace flag and confirmed that the SLQ Agent is NOT running.
Any input is greatly appreciated for I am out of idewas at this point.
Thanks
Kindest Regards,
Just say No to Facebook!December 3, 2010 at 11:43 am
Have you tried moving the files before starting it with the trace flag?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 3, 2010 at 11:53 am
David Benoit (12/3/2010)
Have you tried moving the files before starting it with the trace flag?
I actually got past that error and now am having trouble executing the sql file 'instmsdb.sq'. You can;t load query analyzer and so I tried running it via osql but it did not work. The contents of the output (where i specified -o c:\MyExecution.txt) is the following 2 lines :
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
Here is the syntax of my osql call, perhaps there's a syntax error somewhere I am not seeing? BTW I am using the instmsdb.sql file as is, no modifiactions.
This is the exact line icnluding the curent path I call OSQL from.
D:\MSSQL\Install\osql -E -n-1 -i D:\MSSQL\Install\instmsdb.sql -o C:\MyExecution.txt
I am not an OSQL guru so I may have misused it. Is osql.exe an executable that is picky about where it is called from? There was no osql.exe in the 'D:\MSSQL\Install' directory so I copied it to there because the sample instructions I found showed osql being called from teh same location as the sql file you are loading into it.
Kindest Regards,
Just say No to Facebook!December 3, 2010 at 12:05 pm
YSLGuru (12/3/2010)
D:\MSSQL\Install\osql -E -n-1 -i D:\MSSQL\Install\instmsdb.sql -o C:\MyExecution.txt
I am not an OSQL guru so I may have misused it. Is osql.exe an executable that is picky about where it is called from? There was no osql.exe in the 'D:\MSSQL\Install' directory so I copied it to there because the sample instructions I found showed osql being called from teh same location as the sql file you are loading into it.
Me either but I believe you need to remove the space after the -i and the -o. Try that and let me know what you get.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 3, 2010 at 12:17 pm
David Benoit (12/3/2010)
YSLGuru (12/3/2010)
D:\MSSQL\Install\osql -E -n-1 -i D:\MSSQL\Install\instmsdb.sql -o C:\MyExecution.txt
I am not an OSQL guru so I may have misused it. Is osql.exe an executable that is picky about where it is called from? There was no osql.exe in the 'D:\MSSQL\Install' directory so I copied it to there because the sample instructions I found showed osql being called from teh same location as the sql file you are loading into it.
Me either but I believe you need to remove the space after the -i and the -o. Try that and let me know what you get.
Thanks David but that did n;t work. Heck I can;t even get a staright up query like the below to work.
D:\MSSQL\Binn>osql -E -q"SELECT 1"
The above gives me the same error.
EVery single example posting of using the instmsdb.sql file to re-create the MSDD database all leave out any mention of whether you have to use osql verses isql or whether you need to run the command in the same window as you started the trace or what. I can't believe this is this difficult. UGGGHH
Kindest Regards,
Just say No to Facebook!December 3, 2010 at 12:21 pm
So I am assuming if you just try connecting to the server using the following that OSQL will not work?
D:\MSSQL\Binn>osql -SYourServer -E
If it does, can you run a query once you are connected?
Is there a reason you are not specifying the server in your string also?
I know that OSQL can be tricky with the syntax but once you get it, it usually works well. At this point I want to make sure it works without an input file / sql statement as you stated earlier that you had to copy osql to that location. Not sure how that is going to fly.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 3, 2010 at 12:25 pm
I had not listed my server because none of the osql samples showed it but I just tried what you provided and its not letting me connect. I am an admin although I am not RDP'd into the server as the adminstrator, just a domain user who is in the admin group. When this server is working I can connect with NT Authentciation with no issue.
Does it matter that the server was started with TRACEFLAG 3608? Thats what the postings for using instmsdb.sql all say to do. I'm at a loss.
Kindest Regards,
Just say No to Facebook!December 3, 2010 at 12:30 pm
David Benoit (12/3/2010)
So I am assuming if you just try connecting to the server using the following that OSQL will not work?D:\MSSQL\Binn>osql -SYourServer -E
If it does, can you run a query once you are connected?
Is there a reason you are not specifying the server in your string also?
I know that OSQL can be tricky with the syntax but once you get it, it usually works well. At this point I want to make sure it works without an input file / sql statement as you stated earlier that you had to copy osql to that location. Not sure how that is going to fly.
UPDATE: I just tried connecting to a different SQL Server on the domain using intergarted security and I did discover that the params are case sensative and so -sMyServer fails but -SMyServer works. That said I still can;t get connected to the server on the achine I have RDPd into which i started with the traceflag 3608.
Thanks for the help
Kindest Regards,
Just say No to Facebook!December 3, 2010 at 12:30 pm
Boy - chat would be nice right about now. 🙂
What was the error that you received? Let me know.
Sorry this is taking so long.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 3, 2010 at 12:33 pm
David Benoit (12/3/2010)
Boy - chat would be nice right about now. 🙂What was the error that you received? Let me know.
Sorry this is taking so long.
No need for you to apolagize
The error I get is the following 2 lines:
[Shared Memory]SQL Server does not exist or access is denied.
[Shared Memory]ConectionOpen (Connect()).
Its almost as if the instance that is running can't be accessed. I assume its running for I have not done anything since starting the thing with the 3608 traceflag. I haven't even closed the command window I used to start it with teh traceflag.
Thanks
Kindest Regards,
Just say No to Facebook!December 3, 2010 at 12:47 pm
UPDATE:
This finally worked. Let me finsih so the peopl can get backj to using this DB/server and I will follow up with more on what went wrong.
Thanks
Kindest Regards,
Just say No to Facebook!December 3, 2010 at 12:49 pm
Excellent! I'll be looking forward to the resolution.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply