fruey
asked on
SQL Service will not start... please help
All was runnning fine, then... I get this sort of thing happening in the error logs:
[... snip ...]
2001-08-08 09:46:33.18 kernel SQL Server Assertion: File: <recovery.c>, line=2693
Failed Assertion = 'seenCkptEnd'.
2001-08-08 09:46:33.18 spid1 Location: recovery.c:2693
Expression: seenCkptEnd
SPID: 1
Process ID: 193
2001-08-08 09:46:33.27 spid1 Error: 3624, Severity: 20, State: 1.
2001-08-08 09:46:33.34 spid1 Error: 9004, Severity: 23, State: 5.
2001-08-08 09:46:33.37 spid1 Cannot recover the master database. Exiting.
[... snip ...]
I cannot connect to the SQL server at all because I can't start the service. I don't know what the best step is: I have tried checking authentication (I use Windows NT authentication on the DBs) but this appears to be all good.
Any help much appreciated.
[... snip ...]
2001-08-08 09:46:33.18 kernel SQL Server Assertion: File: <recovery.c>, line=2693
Failed Assertion = 'seenCkptEnd'.
2001-08-08 09:46:33.18 spid1 Location: recovery.c:2693
Expression: seenCkptEnd
SPID: 1
Process ID: 193
2001-08-08 09:46:33.27 spid1 Error: 3624, Severity: 20, State: 1.
2001-08-08 09:46:33.34 spid1 Error: 9004, Severity: 23, State: 5.
2001-08-08 09:46:33.37 spid1 Cannot recover the master database. Exiting.
[... snip ...]
I cannot connect to the SQL server at all because I can't start the service. I don't know what the best step is: I have tried checking authentication (I use Windows NT authentication on the DBs) but this appears to be all good.
Any help much appreciated.
Looks like you need to rebuild the master database the restore it from a backup.
ASKER
Arrrgh... I don't have a backup. At least not of the master db. I know that's dumb.
So... if I rebuild the master, I lose all my dbs right? I have the old data (.mdf) files. Anything I can do to get these back?
Cheers again
So... if I rebuild the master, I lose all my dbs right? I have the old data (.mdf) files. Anything I can do to get these back?
Cheers again
ASKER
Arrrgh... I don't have a backup. At least not of the master db. I know that's dumb.
So... if I rebuild the master, I lose all my dbs right? I have the old data (.mdf) files. Anything I can do to get these back?
Cheers again
So... if I rebuild the master, I lose all my dbs right? I have the old data (.mdf) files. Anything I can do to get these back?
Cheers again
After the rebuild you can restore databases from backups or attach them using sp_attach_db or sp_attach_single_file_db.
I would try restoring them on another server first though to see what you get.
At least make sure you have a copy of everything on the disc.
I would try restoring them on another server first though to see what you get.
At least make sure you have a copy of everything on the disc.
ASKER
OK... I don't have backups of all of the databases, so what you're saying is that using sp_attach_db I can get at the old .mdf files.
Confirm that this is the case and give me a bit of info on how to do sp_attach_db and the points are yours.
Thanks
Confirm that this is the case and give me a bit of info on how to do sp_attach_db and the points are yours.
Thanks
The use of sp_attach_db is as follows (taken from BOL)
This example attaches two files from pubs to the current server.
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\mssql7\data\pubs.mdf' ,
@filename2 = N'c:\mssql7\data\pubs_log. ldf'
You should make copies of the MDF and LDF files before you do anything at all. As nigelrivett said you should really attach these files to another server and run dbcc checkdb (but put the databases into single user mode first) I don't know what causes the error you have but it must have been quite dramatic !
This example attaches two files from pubs to the current server.
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\mssql7\data\pubs.mdf'
@filename2 = N'c:\mssql7\data\pubs_log.
You should make copies of the MDF and LDF files before you do anything at all. As nigelrivett said you should really attach these files to another server and run dbcc checkdb (but put the databases into single user mode first) I don't know what causes the error you have but it must have been quite dramatic !
ASKER
Well I don't know what caused the error myself... maybe the log file getting too big and some trucation error it seems.
I assume I have to use the Query tool to do the updates. Couple things:
What is the "N" for in the statements you gave?
How do I switch to single user?
I don't have another server which isn't a production server, and since the server with problems is completely broken I might as well do it on that. I have backups of the mdf /ldf files elsewhere so I can't go too wrong. Worst case scenario I'll re-install SQL 7 anyways.
If you can go the extra mile by answering the above, TheSpirit, then I'll give you the points. Otherwise I'll have to work out how to split the points since nigelrivett was there with the answer, just not the backup commands, already...
I assume I have to use the Query tool to do the updates. Couple things:
What is the "N" for in the statements you gave?
How do I switch to single user?
I don't have another server which isn't a production server, and since the server with problems is completely broken I might as well do it on that. I have backups of the mdf /ldf files elsewhere so I can't go too wrong. Worst case scenario I'll re-install SQL 7 anyways.
If you can go the extra mile by answering the above, TheSpirit, then I'll give you the points. Otherwise I'll have to work out how to split the points since nigelrivett was there with the answer, just not the backup commands, already...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had to reinstall the whole SQL in the end, since the query analyzer was broken.
The reason for the crash remains to be seen but I have strong suspicions that somewhere a tweak on the maintenance and 'allow to grow' properties was way out.
I'm sorry to the other user who replied quickly to start off with, I guess rebuilding and/or reinstalling were the procedures needed and you were right there; however, Spirit went that bit further and managed to respond quickly for me when I was getting into the nitty gritty.
So kudos to nigel and Points to Spirit. Cheers
The reason for the crash remains to be seen but I have strong suspicions that somewhere a tweak on the maintenance and 'allow to grow' properties was way out.
I'm sorry to the other user who replied quickly to start off with, I guess rebuilding and/or reinstalling were the procedures needed and you were right there; however, Spirit went that bit further and managed to respond quickly for me when I was getting into the nitty gritty.
So kudos to nigel and Points to Spirit. Cheers