Thursday, October 29, 2015

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 200, state 7, severity 25.

We face this error during below scenario’s:

           1 .    When you have done a Service Pack upgrade.
           2 .    When you have changed collation of the instance.

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

As with many errors even this error comes up due to many reasons. So there are few things you need to verify.

As a first step, I recommend to verify below paths in the REGISTRY of the server where SQL Server installed and make sure those paths exists.

Type regedit in Run.

Once registry opens, go to below path: (Below underlined MSSQL10.MSSQLSERVER, here MSSQLSERVER is your instance name. So keep in mind this will change as per your instance name.)

After clicking on MSSQLServer, you will see ‘BackupDirectory’, ‘DefaultData’ and ‘DefaultLog’ fields along with many other fields which will be on right side.  For all these 3  fields verify the paths mentioned in ‘Data’ column exists in the server.

Next go to below location in registry and click on Setup

Then on right side you will notice ‘SQLDataRoot’. Verify here as well the path given is correct.

For some reasons during few Service Pack installations or instance upgrades the locations of above fields becomes invalid in registry and we start receiving above error.

So if you correct the paths in above fields then it will fix the issue in most of the cases.

As a second step, try to start the SQL services in single user mode by using the switch ‘-m’ and REBUILD the master database.

As a last step,  I would verify if there is any conflict of collation between system databases.

Recently I had same issue and after spending several hours on this, it turned out to be a collation difference between master and msdb databases.

How this happened? We had to upgrade an instance and change collation as well after upgrade. So after performing the upgrade and changing collation, DBA have performed master database restore(in order to get back logins) and unfortunately the backup used was of old collation. So this has messed up the instance.

If you are very lucky instance will start and will be stable until you perform the collation change of the database, you can do it either performing a fresh restore or master database REBUILD(I HAVE NOT FOUND A DBA WITH LUCK TILL NOW J). So what’s happening in my case was, as there was collation difference in system databases, instance was starting but it was going down immediately. During the time it was in started state when we tried to connect to instance, it was throwing above script upgrade error.

So the only option we had was to re-install the instance and this time, no, we didn't changed the collation of master database.

Please feel free to share any other easy way to fix this issue.