none
SQL2K8 SP2 Upgrade Failure RRS feed

  • Question

  • SQL 2K8 SP2 is failing on one of my boxes.  It appears this box had Agent XPs disabled on it.

    2010-12-20 16:07:48.54 spid6s   Database 'master' is upgrading script 'sqlagent100_msdb_upgrade.sql' from level 0 to level 3.
    2010-12-20 16:07:48.56 spid6s   ----------------------------------------
    2010-12-20 16:07:48.57 spid6s   Starting execution of PREINSTMSDB100.SQL
    2010-12-20 16:07:48.57 spid6s   ----------------------------------------
    2010-12-20 16:07:48.85 spid6s   Setting database option COMPATIBILITY_LEVEL to 100 for database msdb.
    2010-12-20 16:07:48.93 spid6s   Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
    2010-12-20 16:07:48.94 spid6s   Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
    2010-12-20 16:07:48.96 spid6s   FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
    2010-12-20 16:07:50.52 spid6s   SQL Server blocked access to procedure 'sys.xp_instance_regread' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.
    2010-12-20 16:07:50.56 spid6s   Error: 15281, Severity: 16, State: 1.
    2010-12-20 16:07:50.56 spid6s   SQL Server blocked access to procedure 'sys.xp_instance_regread' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.
    2010-12-20 16:07:50.62 spid6s   Error: 912, Severity: 21, State: 2.
    2010-12-20 16:07:50.62 spid6s   Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15281, state 1, severity 16. 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.
    2010-12-20 16:07:50.69 spid6s   Error: 3417, Severity: 21, State: 3.
    2010-12-20 16:07:50.69 spid6s   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.

     

    I have been able to get SQL server to start in single user mode using the /f /t3608 flags.  I can then connect with a DAC session and I updated the Agent XP setting from 0 to 1, however SQL server still gives the same errors when I try to start it normally.  If I query sys.configurations while connected via the DAC session the "value in use" field for Agent XPs is "-1".

    Any ideas?  We ran into this same problem with a few machines with SP1 and ended up reinstalling.  I'd prefer not to have to do that.

    Thanks

     

    Monday, December 20, 2010 10:22 PM

Answers

  • Hi tgillitzer1,

     

    Since the error messages has said “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.”, I would like to recommend that you follow the instructions to restore master database from a healthy backup.

     

    If this issue still exists, please try rebuilding master database.  Please follow the steps provided in the following link to rebuild system database:

    http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Wednesday, December 22, 2010 8:23 AM
    Moderator

All replies

  • Did you try the workaround listed here ? Even though it is for SQL Server 2008 R2, it is worth trying.


    Pradeep Adiga
    Blog: sqldbadiaries.com

    Recent posts on my blog
    Tuesday, December 21, 2010 4:11 AM
  • That is almost the same as what I tried, but no, I had not.  I tried it and it still does the same thing, though now while logged in while it is running with the trace flag, the "Value in use" field for Agent XPs is 1 like it should be.

    Tuesday, December 21, 2010 4:36 PM
  • Hi tgillitzer1,

     

    Since the error messages has said “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.”, I would like to recommend that you follow the instructions to restore master database from a healthy backup.

     

    If this issue still exists, please try rebuilding master database.  Please follow the steps provided in the following link to rebuild system database:

    http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Wednesday, December 22, 2010 8:23 AM
    Moderator