RE: SQL Server failing to start (directly effecting sales!!!) Error: 823, Severity:
From: Rob Beene [MSFT] (rbeene_at_online.microsoft.com)
Date: 02/22/04
- Next message: Rob Beene [MSFT]: "RE: Fail to install MSDE (log attached)"
- Previous message: Johnny: "Upgrading MSDE"
- In reply to: eric: "SQL Server failing to start (directly effecting sales!!!) Error: 823, Severity:"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 22 Feb 2004 04:23:46 GMT
Hey Eric,
You should consider calling Microsoft Technical Support as your master
database is corrupted. As you will see below, there are several steps and
decisions that have to be made to get your server running again. If this
is truly an urgent issue (effecting sales), it is more timely and
beneficial to contact support. They will want your SQL Server Errorlogs
that are in the LOG folder where MSDE is installed, your System and
Application Event logs saved in text format, and what backups you have of
your databases. If you do not have backups of master or your user
databases, it could take some time to get your server running again as you
will see from the steps that follow. Regretably, I have to let you know
that there could also be a potential for data loss.
However, if you want to try and resolve the issue without Technical
Support, The first thing you should do is examine your System event log for
potential hardware problems. An 823 error generally speaking can indicate
that the machine with the database has hardware that is failing. In this
case where the 823 error is occurring in Master, the only way to get SQL
Server going again is to rebuild master (Sometimes you can use a master
from a server that isn't having the same problem, but that in itself has
some potential issues and isn't really advisable unless it meets certain
criteria). If your release of MSDE includes the rebuildm.exe utility, you
can use it to rebuild master. If it does not include rebuildm.exe, you
will either have to uninstall and reinstall MSDE or if you have a full
version of SQL Server that includes the client Tools, you can use
Rebuildm.exe from there.
One requirement for using Rebuildm.exe is that it is going to ask you for
the template files. MSDE uses cab files so you will have to extract those
files (Master.mdf, Mastlog.ldf, MSDBData.mdf, MSDBLog.ldf, and so on).
IMPORTANT: Rebuildm.exe rebuilds all of the system databases and not just
master, so before you run this utility, make a backup copy of your data
folder. Even if you are planning to reinstall you should make a backup copy
of your data folder before proceeding. A reinstall will for sure rebuild
all your system databases.
Also, if your master database is experiencing 823 errors, it's very likely
that other databases will also have errors. Once you have rebuilt master or
reinstalled, your MSDE installation will not be aware of the databases that
previously existed. You have two options at this point. Restore your
master database from backup or if you do not have a backup, then you will
have to reattach your databases as the files will still be on the disk, but
the server will not be aware of them.
YES, I HAVE A BACKUP OF MASTER:
==================================
To restore master, you have to start the server in Single User mode. To do
that, use a command prompt and navigate to the binn folder under the
directory where you installed MSDE, then run the following:
SQLServr.exe -c -m
You will not get a command prompt back. If you do, this means that SQL
Server cannot start which is what will happen until you successfully
rebuild your master database. When it successfully starts, You should see
the screen roll text by and come to a cursor. This means SQL Server is
running. Once it is running, you can connect to it using either OSQL or
Query Analyzer and then run the following command to restore your master
backup:
RESTORE DATABASE MASTER FROM Disk='path and filename of your backup.bak'
Once the master database is successfully restored, you should run DBCC
Checkdb against all databases using OSQL or Query Analyzer to make sure
they are free of errors. If errors are found, you should contact Technical
Support or restore the databases from backup.
NO, I UNFORTUNATELY DO NOT HAVE A BACKUP OF MASTER:
==========================================================
If you do not have a backup of Master, you have to attach the databases,
what you can do is use OSQL (or Enterprise Manager or Query Analyzer if you
have the Client Tools) and reattach the databases using sp_attach_db. The
syntax is as follows:
sp_attach_db 'dbname','path and filename of .mdf file', 'path and filename
of .ldf file'
For example, if your database is called Finance and your files are called
Finance_data.mdf and Finance_log.ldf and they are located in the default
location, it would look like:
sp_attach_db 'Finance','c:\program files\Microsoft SQL
Server\MSSQL\Data\Finance_data.mdf','c:\program files\Microsoft SQL
Server\MSSQL\Data\Finance_log.ldf'
If your databases fail to attach and you have verified the syntax is
absolutely correct including the paths and filenames, then you should call
tech support.
Once you have successfully reattached your user databases, you should run
DBCC Checkdb using either OSQL (Of Query Analyzer if you have the client
tools) and verify there are no errors in your user databases as well. If
there are errors, you should contact Microsoft Technical Support for
assistance or optionally, restore your databases from backup.
Lastly, rebuilding master will also eliminate any logins you previously
were using, so you will have to readd those. Once you do, you should run
the following in OSQL (or Query Analyzer if you have the client tools) to
verify all your logins are matching up to the logins in each database. You
should run this against every user database:
sp_change_users_login 'Report'
If no records are returned, this means that for every login in the database
there is a matching login for the server. As you may know, security is
stored in two places. The master database keeps track of what users can log
into MSDE and the database stores what permissions or users can use that
specific database.
If sp_change_users_login 'Report' returns rows, then this means the logins
in the database did not find a matching login on the server. You will
either have to add the login, or if the login really does exist already and
needs to be matched up, you can run the following:
sp_change_users_login 'Auto_fix','loginname'
This should resync the database login to the matching server login.
I know this is a lot of information for resolving this problem.
Regretably, a corrupt master is not quickly resolvable unless you have
ready the ability to rebuild the master and restore it from backup.
I hope this helps...
Sincerely,
Rob Beene, MCSD, MCDBA
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
- Next message: Rob Beene [MSFT]: "RE: Fail to install MSDE (log attached)"
- Previous message: Johnny: "Upgrading MSDE"
- In reply to: eric: "SQL Server failing to start (directly effecting sales!!!) Error: 823, Severity:"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
