FIX: SQL Server 2000 Service Pack 1 Setup May Fail when Applied to a Server with Non-writable Databases (300919)

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:

In order to begin an upgrade, databases and filegroups must be writable and you must have permission to access the databases. Correct the problem and restart the setup.

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.

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