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 condtion which might interfere with regular operation and the database will be taken offline



One day we got a request asking for upgrade present sql server environment (Microsoft SQL Server 2014 SP1) to Microsoft SQL Server 2014 (SP2 CU11). So we followed the below process. And the environment is in Always on.

So we have decided first we will do the patching in Secondary replica, then we can do it in Primary replica. So we have ran the. exe file that we have downloaded from the et(https://support.microsoft.com/en-us/help/4052725/cumulative-update-10-for-sql-server-2014-sp2). After we successfully installing the patching in secondary replica and trying to bring the sql server instance is failed with the below error messages in the event viewer 




After seeing the above errors we are under impression that we need rebuild the master database. But don't do that.We are not able to restart the services and it is throwing above error in the event viewer.  So to bring the services up we used -T902 trace flag in the startup parameters of sql server service. Then immediately sql server services came online. Once the services came online we are able to connect to the respective instance thorough management studio (SSMS).  After we connect to the instance we found that there is a database called SSIS in the instance and this database is also part of always on availability group. This database is the main culprit here and as long as this database exists in the instance we cannot connect to the sql server instance normally, instead we have to use -T902 trace flag, so what we did was we removed this database from availability group, once we remove database from the availability group then the status of SSIS database would go into (Restoring…) mode. Now we can delete this database from the database instance, or take the database into offline mode (https://blogs.msdn.microsoft.com/mattm/2012/09/19/ssis-with-alwayson/). Before we are upgrading instance with cumulative update we don’t have idea about this error. So what we did was we remove the SSIS database, now I can also remove –T902 trace flag from the startup parameter and able to start the services successfully. So do the same behavior in another replicas also before you are applying cumulative upgrade. Remove the SSIS database and apply the patch, one the patch is succeeded then we can restore the SSIS database and then add that to the availability group.

Comments

Post a Comment

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'
Read more

Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25.

Image
I got the below error in one of my production environments one day after patching. Below are the error message. You are unable to bring the database engine and agent online, and you will face the below error messages in the error log of SQL Server and eventvwr of Windows server. Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25. This is a serious error condition that might interfere with regular operation and the database will be taken offline. If the error happened during the 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. This error is misleading us don't go for rebuild master database You can also find the same information in the error log SQL server. Worka
Read more