locked
Upgrade from 2005 to 2008 fail: wait on the database engine recovery handle failed RRS feed

  • Question

  • Hello,

    We're upgrading a production database from 2005 to 2008, but encountered this error:

    wait on the database engine recovery handle failed.

    The SQL Server service is totally down after failed

     

    I tried to search on the web, found 2 suggestions:

    1. failed because of renamed 'sa'

    --> we're sure we never renamed 'sa'

    2. TargetServersRole

    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/16/upgrade-from-sql-server-2008-to-sql-server-2008-r2-might-fail-with-the-error-wait-on-the-database-engine-recovery-handle-failed.aspx

    We found that 2 records of schema_id doesn't match principal_id, TargetServersRole and the other DBA Role we created before.

    What we do is we drop TargetServersRole, and try to re-upgrade the database.

     

    But it failed again ...

    On the end of database errorlog, we found below error:

    Error: 15151, Severity: 16, State: 1.

    Cannot find the user 'TargetServersRole', because it does not exist or you do not have permission.

    Error: 912, Severity: 21, State: 2

    Script level upgrade for database 'master' failed because upgrade step 'sqlagent90_sysdbupg.sql' encountered error 15151, 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.

    Error: 3417, Severity: 21, State: 3.

    Cannot recver the master database, SQL Server is unable to run. Restore master from a full backup, repair it or rebuild it.

     

    Pls help, thanks

    Yuyu

    Friday, October 28, 2011 9:21 PM

Answers

All replies

  • Hello,

    Any suggestions on this error ?

    Sunday, October 30, 2011 12:26 PM
  • Hi yuyu,

    Cannot find the user 'TargetServersRole', because it does not exist or you do not have permission.
    This problem occurs because one or both of the following tables contain blank or invalid entries:
    • The Windows Login table (2000000054) has a blank field "SID".
    •The Member Of table (2000000003) has a blank field "User ID".
    Here is a solution could help you: http://support.microsoft.com/kb/942166.

    You could refer to
     http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/sql-server-2008-service-fails-to-start-after-applying-service-pack-1.aspx

    http://blogs.msdn.com/b/karthick_pk/archive/2010/11/18/sqlserver2008-script-level-upgrade-for-database-master-failed-because-upgrade-step-sqlagent100-msdb-upgrade-sql-encountered-error-574-state-0-severity-16.aspx.

     


    Hope this helps,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • Marked as answer by yuyu_bali Wednesday, November 7, 2012 9:09 AM
    Monday, October 31, 2011 5:49 AM
  • Hi Maggie,

    Thanks for your advise...

    • The Windows Login table (2000000054) has a blank field "SID".
    •The Member Of table (2000000003) has a blank field "User ID".

    Workarounds:

    Blank entries cannot be seen in either the Database Login dialog box or the Windows Login dialog box. Therefore, you have to remove these blank entries manually.

    To do this, run both tables from Object Designer. Check for blank "SID" or "UserID" fields, and then delete these invalid entries.

     

    where can I find the table and check the SID, UserID ...

    Thanks a lot,

    Yuyu

    Tuesday, November 1, 2011 1:05 PM
  • How do you perform an upgrade? In place? Why not installing SQL Server 2008 and new machine and then  restore all user databases+ logins+jobs and etc.?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 1, 2011 1:27 PM
  • How do you perform an upgrade? In place? Why not installing SQL Server 2008 and new machine and then  restore all user databases+ logins+jobs and etc.?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 1, 2011 1:28 PM
  • Hi Uri,

    We don't have new machine for the SQL Server 2008 installation, so I just upgrade from 2005 using the 2008 installer.

    BTW how we can import logins/passwords and jobs from 2005 to 2008 ? As I may try to upgrade again, and if still fail, i may have to uninstall 2005 and install 2008.

    We canno't install a 2nd instance with 2008 on the same box as named instance cannot be renamed to default instance, correct ?

     

    Thanks,

    Yuyu

    Wednesday, November 2, 2011 2:41 AM
  • Hi yuyu,

    According to your description, if you failed upgrade SQL Server 2005 to SQL Server 2008, you could try Uri’s advice.

    How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008: http://support.microsoft.com/kb/918992.

    Migrating to SQL Server 2008: http://msdn.microsoft.com/en-us/library/bb677619(v=SQL.100).aspx.

     

    Hope this helps,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Wednesday, November 2, 2011 7:12 AM
  • I would suggest to open support case with Microsoft SQL Support team to investigate.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Wednesday, November 2, 2011 7:18 AM
  • Hi,

    I found one more thing that the id (windows id) we used for upgrading was denied on permission to connect to database engine, but login enabled, on the Status tab setting in security.

    Usually we use this windows authentication id to perform all the changes, never noticed permission denied to connect to database engine, coz never encounted any permission issue.

    I thought this may be the root cause of this error, but when I tried on the test server, i denied the permission for the windows id used for upgrading on the test server, the upgrade is successful, I'm confused what will be the cause of production failure.

    Thanks,

    Yuyu

    Thursday, November 3, 2011 6:45 AM
  • Hi,

    Will the windows authentication id denied on permission to connect to database engine cause the error:

    Error: 15151, Severity: 16, State: 1.

    Cannot find the user 'TargetServersRole', because it does not exist or you do not have permission.

     

    Normanlly we don't have any permission issue using this id.

     

    Thanks,

    Yuyu

    Friday, November 4, 2011 7:15 AM