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.
Thanks!!
