Upgrade from SQL Server 2008 to SQL Server 2008 R2 might fail with the error “Wait on the Database Engine Recovery Handle Failed”

  • [アーティクル]

PROBLEM DESCRIPTION

================

· During upgrade from SQL Server 2008 to SQL Server 2008 R2 we might encounter an error as shown below

Wait on the Database Engine Recovery Handle Failed. Check the SQL Server error log for potential causes.

· The following KB article talks about a similar error but the cause for the error is different than what we are experiencing here.

https://support.microsoft.com/kb/968829

· More detailed errors were logged in the Errorlog

2010-08-14 15:09:29.31 spid7s Setting object permissions...

2010-08-14 15:09:29.56 spid7s Error: 2714, Severity: 16, State: 6.

2010-08-14 15:09:29.56 spid7s There is already an object named 'TargetServersRole' in the database.

2010-08-14 15:09:29.57 spid7s Error: 2759, Severity: 16, State: 0.

2010-08-14 15:09:29.57 spid7s CREATE SCHEMA failed due to previous errors.

2010-08-14 15:09:29.60 spid7s Error: 912, Severity: 21, State: 2.

2010-08-14 15:09:29.60 spid7s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 2714, state 6, 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.

2010-08-14 15:09:29.73 spid7s Error: 3417, Severity: 21, State: 3.

2010-08-14 15:09:29.73 spid7s 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.

2010-08-14 15:09:29.74 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

RESOLUTION

===========

· It is clear from the error above that the upgrade script 'sqlagent100_msdb_upgrade.sql' did not execute successfully because of the error 2714. The text message for error id 2714 is “There is already an object named '%.*ls' in the database”. We can find the message for an error code by executing the following query

Query 1

select * from sys.messages where message_id = 2714

· In this scenario, the schema 'TargetServersRole' was already present in the database, but this is not the actual issue.

· After looking into the sqlagent100_msdb_upgrade.sql, found that below code was failing during upgrade. We can find the upgrade script ‘sqlagent100_msdb_upgrade.sql’ from the location ..\MSSQL10_50.<instance name >\MSSQL\Install\

<Excerpt from sqlagent100_msdb_upgrade.sql>    

            IF (EXISTS (SELECT * FROM msdb.dbo.sysusers

                        WHERE (name = N'TargetServersRole')

                              AND (issqlrole = 1)))

            BEGIN

              -- If there are no members in the role, then drop and re-create it

              IF ((SELECT COUNT(*)

                   FROM msdb.dbo.sysusers su,

                            msdb.dbo.sysmembers sm

                   WHERE (su.uid = sm.groupuid)

                        AND (su.name = N'TargetServersRole')

                        AND (su.issqlrole = 1)) = 0)

              BEGIN

                EXECUTE msdb.dbo.sp_droprole @rolename = N'TargetServersRole'

                EXECUTE msdb.dbo.sp_addrole @rolename = N'TargetServersRole'

              END

            END

            ELSE

              EXECUTE msdb.dbo.sp_addrole @rolename = N'TargetServersRole'

· Tried running the script separately and it failed with the same error mentioned above.

· The above script first checks if an object 'TargetServersRole' is present in msdb.dbo.sysusers and then checks if there are any members for that role.

· If there is an object 'TargetServersRole' and if there are no members it goes ahead and drops that role and adds it again. Else it just adds the role

· In our case the script failed because sp_droprole procedure was unable to drop the schema TargetServersRole and this is the actual issue.

· The reason as to why we were not able to drop the role using sp_droprole was that the AUTHORIZATION was not same as Role name.

· AUTHORIZATION is the process by which SQL Server decides whether a particular user is allowed to access a resource

· Found that the AUTHORIZATION for TargetServersRole was not TargetServersRole, using the following queries

Query 2

Select a.name as [PrincipalName], a.principal_id, b.schema_id, b.name as [SchemaName], a.type_desc

from sys.database_principals a

join sys.schemas b

on a.principal_id = b.principal_id

where a.principal_id <> b.schema_id -- Filtering to display records whose schema_id doesnt match principal_id

· Found that there was one record in the output which had different values for principal_id and schema_id. Whereas, this script should return “(0 row(s) affected)” for a normal scenario.

· We can also comment out the filter in the above query and notice that it returns all the records and would have one record with different values for the principal_id and schema_id as shown below.

Output

=====

PrincipalName

principal_id

schema_id

SchemaName

type_desc

TargetServersRole

8

5

TargetServersRole

DATABASE_ROLE

· Used the following command to alter the SCHEMA and set AUTHORIZATION to TargetServersRole by using the following command

Query 3

USE [msdb]

GO

ALTER AUTHORIZATION ON SCHEMA::[TargetServersRole] TO [TargetServersRole]

GO

· Now if we run Query 2 we will notice that it does not return any rows

· After this the upgrade is expected to proceed further

Regards,

Bharath Kumar

Microsoft SQL server support

Reviewed by,

Sudarshan Narasimhan & Shamik Ghosh

TL, Microsoft SQL server support

Comments