PRB: Insufficient Room for Growth in Master or Model Databases May Corrupt the sysdepends Table in the Model Database (289872)


The information in this article applies to:

  • Microsoft SQL Server 2000 Enterprise Edition
This article was previously published under Q289872

SYMPTOMS

Failure to provide sufficient disk space for database growth during the SQL Server 2000 upgrade process may corrupt the sysdepends table in the model database or may cause the process to stop responding (hang) without any informational message.

CAUSE

If the data file is almost full (only a couple pages are free) and the file growth on the data file is set to 0 percent (%), the upgrade process does not have the space required to complete the upgrade of the database and the database is left in a partially upgraded but corrupt state.

WORKAROUND

To work around this problem, use these steps:
  1. Replace the model database. To replace the model database:

    1. Restore a known good copy of the SQL Server 2000 model database to the affected server.

      An error message regarding the msdb database occurs, but you can ignore the error message.
    2. Copy in a model database from a new SQL Server installation. Copying in a new model database results in the loss of any new additions you may have made to your previous model database.
    3. Restore a SQL Server 7.0 model database to a SQL Server 7.0 server and increase the database size or enable the AutoGrow option with a value greater than 0. Add the new model database to the SQL Server 2000 installation.
    In each of these options, if a "fixed" model database is restored to the affected server, you must start the server with the -f option to enable the restore. Otherwise, the server will not start.
  2. Re-run the upgrade.

MORE INFORMATION

Preventative Actions

To ensure that your upgrade is not affected by this problem, use these steps:
  1. Verify that you have a complete, known good backup of all data.
  2. Verify that sufficient disk space is available on the drive that contains the databases.

    NOTE: Microsoft recommends that you set the AutoGrow function on for both the master and model databases, with a value to grow of greater than 0 or based on the formula that databases need the ability to grow 4-5 pages per file, per each 4 GB segment. Manually increase the size of the master and model databases prior to upgrading to SQL Server 2000.

    If the AutoGrow value = 10% and the database is 10 MB, the database is able to grow to 11 MB initially, which is expected to be sufficient in most cases. However, the database still has the ability to expand if additional space is required.
Recovery Process for Affected Upgrades

  1. Restore the model database from your backup.

    After this restores an old backup from the affected server, verify the key characteristics of this backup:

    • If the data file is near full (only a couple pages are free).

      -and-

    • That file growth on the data file is set to 0%, which causes the upgrade process to fail.


    NOTE: This problem occurs if the current copy of the model database is almost full and the data file has file growth disabled either due to growth set to 0% or growth disabled.
    • During the upgrade to SQL Server 2000, the upgrade stops responding during the Setup is initializing the server for the upgrade process step.

      Now the setup waits on the server that has exited because the model database can not be upgraded to the proper database version, due to lack of disk space.

    • The server is now unusable, and any attempt to start the server fails with a message that indicates that the model database must be upgraded and the server exits.


  2. Restart the upgrade.

    The server starts with the -f flag, causing a new attempt at upgrading the model database. This attempt succeeds, possibly due to the fact that recovery must run on the model database, and the recovery ignores file growth settings.

    NOTE: Technically, the space was made available by having the Upgrade.sql file run on the inconsistent version of the model database in step 2.

    Setup does not notice that the upgrade of the model database failed so it runs the Upgrade.sql file. The server does not complain because it was started with the -f option, after which it tries to start the server without the -f option. That is when the setup may stop responding.

    The successful upgrade of the model database also causes corruption and the upgrade fails when you run Upgrade.sql due to the corruption.
Modification Type:MajorLast Reviewed:2/26/2002
Keywords:kbDSupport kbprb KB289872
©2002 Microsoft Corporation. All rights reserved.