none
SQL Server 2014 SP2 CU3 installation problem

    Question

  • I have two separate instances of SQL Server on one VM. The CU was applied successfully to one instance, but failed on the other instance. That instance will not start up now. These are the error messages in the log:

    Log Name:      Application
    Source:        MSSQL$SQLSVR
    Date:          2/7/2017 7:47:52 AM
    Event ID:      824
    Task Category: Server
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      QATserver
    Description:
    SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x00a49c01; actual: 0x59fd8660). It occurred during a read of page (1:19440) in database ID 6 at offset 0x000000097e0000 in file 'J:\UserData\SQLSVR\SomeUserDatabase.MDF'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    Log Name:      Application
    Source:        MSSQL$SQLSVR
    Date:          2/7/2017 7:47:52 AM
    Event ID:      912
    Task Category: Server
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      QATserver
    Description:
    Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 824, state 2, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

    Log Name:      Application
    Source:        MSSQL$SQLSVR
    Date:          2/7/2017 7:47:52 AM
    Event ID:      3417
    Task Category: Server
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      QATserver
    Description:
    Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

    Since this is not a production instance, I can get a copy of the user database from a production backup. Do I really need to restore the master database from backup or repair/rebuild the master database? Can I just bring up SQL Server with a specific trace flag or startup parameter and drop the corrupt user database? And then re-start SQL Server in normal mode and finish the SQL Server patching. What trace flag or parameter should I use to start SQL Server?

    Tuesday, February 7, 2017 7:42 PM

All replies

  • Hello,

    Please check the disk subsystem for problems first.

    Once you have checked the storage subsystem and since this is a no production environment you can choose to restore the user database from production or to recover the database. To recover the database, set the database in single user mode.

    ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE


    and then run the below command.


    DBCC CHECKDB(DB_NAME,REPAIR_ALLOW_DATA_LOSS)


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, February 8, 2017 2:33 AM
    Moderator
  • Alberto is correct, these errors are almost always disk subsystem hardware problems.
    Wednesday, February 8, 2017 2:57 AM
  • Unfortunately, the SQL Server instance would not start. I tried starting SQL Server with minimal configuration by typing the following at the command prompt:

    netstart "SQL Server (instancename)" /f /m

    The service would not start and the specific error that occurred was 3417. The same three errors were in the Application Event log, too.

    Then I restored a backup of the master database to the second instance on the same VM, but I restored to a different database name and different file location. After that database was restored, I detached this database from the second instance. I renamed the data and log files for master database for the first instance (which would not start), copied the data and log file from the restored master database to the appropriate file location for the first instance and then tried to start the first instance normally. It would not start. The same three error messages are in the event log.

    Finally, I renamed the data and log files for the user database with the checksum error. Then started the first SQL Server instance in the normal fashion, and it started with the restored master database files. So I shut the instance down, renamed the master data and log files to something else, and renamed the original master data and log files back to their original names. The SQL instance starts up normally.

    So the question now is did the SQL Server 2014 SP2 CU3 patch get installed properly? @@VERSION shows the updated number: 12.0.5538.0 Is there something else that needs repairing on the master database? DBCC CHECKDB on master database shows no errors.

    During the patching process, the SQL log showed: CHECKDB for database 'SomeUserDatabase' finished without errors, so the user database did not have any errors when the patching started. The SQL log also showed: Database 'master' is upgrading script 'msdb110_upgrade.sql' from level 201332114 to level 201332130. After many entries, the SQL log showed: Execution of MSDB_POST_INSTALL.SQL complete ... Populate syssubsystem table... ... Revoke any permission to public role... ...The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity. ... Performing replication job security meta-data upgrades... Then this is where the 3 errors show up in the log. After this point the SQL Server instance would not start.

    What is SQL Server 2014 SP2 CU3 doing to the user databases?

    Wednesday, February 8, 2017 7:02 PM