Article ID: 300919
Article Last Modified on 10/15/2002
APPLIES TO
- Microsoft SQL Server 2000 Service Pack 1
This article was previously published under Q300919
BUG #: 353868 (SHILOH_BUGS)
SYMPTOMS
The SQL Server 2000 Service Pack 1 (SP1) setup may fail with the following error message if any of the databases on the server on which the service pack is installed, are in a non-writable state:
A database is considered to be in a non-writable state if the database status is:
- SUSPECT
- RECOVERING
- OFFLINE
- EMERGENCY MODE
- READ-ONLY
- LOADING
The error message may also occur on servers that are configured to participate in log shipping as standby servers. Because the secondary databases are either in LOADING or READ-ONLY state, the service pack setup fails on these servers, with the error.
CAUSE
SQL Server 2000 Service Pack 1 setup requires that all the databases on the server be accessible and writable.
RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
WORKAROUND
The following table outlines some possible resolutions for databases that are in the listed states:
| STATE | ACTION |
|---|---|
| SUSPECT | Investigate and correct the problem that caused the database to be marked SUSPECT. Refer to the "More Information" section for references. |
| RECOVERING | A database in this state is being examined by SQL Server for any incomplete or uncommitted transactions. If SQL Server finds any such transactions, it rolls back any changes that these transactions might have made. It is best to leave the database in this state until the recovery operation completes. Refer to SQL Server Books Online topic "Transaction Recovery" for more information. |
| OFFLINE | Use the ALTER DATABASE Transact-SQL command to bring the database online. |
| READ-ONLY | A database in this state can either be recovered, if it is in a stand-by mode, or the database status can be changed by using the ALTER DATABASE Transact-SQL command. The use of the ALTER DATABASE command is applicable ONLY if the database was put into the READ-ONLY state by using the same command. |
| LOADING | Investigate the reason for the database to be in this state. If needed, recover the database from this state by using the RESTORE DATABASE command. Refer to the SQL Server Books Online topic "How to recover a database without restoring (Transact-SQL)" for more information. |
After the databases are in writable state, re-run the SQL Server 2000 Service Pack 1 setup.
If the server is participating in log shipping as a standby or secondary server:
- Remove log shipping.
- Recover the databases by using the information provided in the SQL Server Books Online topic "Recovering a Database Without Restoring".
- Install SQL Server 2000 Service Pack 1.
- Re-establish log shipping after the service pack setup completes successfully.
STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.
REFERENCES
Refer to Section 3.0 of the Sp1readme.htm file included with the Service Pack 1 installation files for more information about this problem.
For additional information about removing log shipping when a database name contains a quote character, click the article number below to view the article in the Microsoft Knowledge Base:
295936 FIX: Error Removing Log Shipping on Secondary Database When Database Name Has a Quote
For more information about databases in a SUSPECT state, refer to the following Microsoft Knowledge Base articles:
180500 PRB: Missing Device Causes Database to Be Marked Suspect
257852 INF: How to Recover from a "File in Use" Situation on SQL Server Startup
165918 INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH NO_LOG
288809 PRB: How to Recover SQL Server After a Tempdb Database is Marked Suspect
Additional query words: log shipping standby readonly service pack setup fails
Keywords: kbbug kbfix KB300919
