Scenario
After update SQL Server 2012 or 2014 with new service pack then SQL Server stop working , does not start service with following event log
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.
Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 3906, 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.
My Environment
SQL Server 2014 Enterprise Edition running on Windows 2012 R2 .
Reason
In my case the SSISDB was a member of availability group database, The instance which I was updated was secondary. If you face issue the following step with help you to solve this
Start SQL Server service with Trace Flag 902:
> Net Start MSSQL$InstanceName /T902
Remove SSISB From availability group (do this from primary )
Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under \Program Files\Microsoft SQL Server\MSSQL12.MSSQL$Instancename \MSSQL\Install
Stop SQL Server services:
> Net Stop MSSQL$InstanceName
Start SQL server service from SQL Server configuration manager
Add SSISDB back to Availability Group
After update SQL Server 2012 or 2014 with new service pack then SQL Server stop working , does not start service with following event log
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.
Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 3906, 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.
My Environment
SQL Server 2014 Enterprise Edition running on Windows 2012 R2 .
Reason
In my case the SSISDB was a member of availability group database, The instance which I was updated was secondary. If you face issue the following step with help you to solve this
Start SQL Server service with Trace Flag 902:
> Net Start MSSQL$InstanceName /T902
Remove SSISB From availability group (do this from primary )
Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under \Program Files\Microsoft SQL Server\MSSQL12.MSSQL$Instancename \MSSQL\Install
Stop SQL Server services:
> Net Stop MSSQL$InstanceName
Start SQL server service from SQL Server configuration manager
Add SSISDB back to Availability Group
This article is a great article I have seen so far in my SQL function programming career. This will help me a lot in the present and the time to come.
ReplyDeletehire python developers in US
Microsoft SQL Server 2019 Standard provides all database features with analytics and reporting capabilities. It also includes basic availability features and disaster recovery. Microsoft SQL Server Standard can build rich content management applications.
ReplyDelete