SQL Server could not start master db corrupted
Hi Expert! i need a big help! SQL Server could not start. We had windows server maintenance this weekend after reboot the server SQL Server could not start. What i noticed that windows update installation had failed.
In the event viewer i have found that 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 'msdb110_upgrade.sql' encountered error 2627, state 1, severity 14. 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.
I never did this before. i am litterally scared of doing this. in the production environment. Databases are not many only 5/6. Please help.
In the event viewer i have found that 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 'msdb110_upgrade.sql' encountered error 2627, state 1, severity 14. 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.
I never did this before. i am litterally scared of doing this. in the production environment. Databases are not many only 5/6. Please help.
You can always retry to run the Upgrade. It might failed for some reason and with luck you can have it run successfully now.
Btw, the master database shouldn't be used to store non system data as this can interfere with any SQL Server update or upgrade.
Btw, the master database shouldn't be used to store non system data as this can interfere with any SQL Server update or upgrade.
ASKER
I am not quite sure which update failed. After logged into the SQL Server i was that sql server could not start up and also saw from the control panel system security windows update failed. Over the weekend windows team rebooted the server as schedule.
When i tried to start up the sql server service then i got the following error.
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.
When i tried to start up the sql server service then i got the following error.
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 'msdb110_upgrade.sql' encountered error 2627, state 1, severity 14.The above error is related to a SQL Server upgrade. It isn't related to a operating system upgrade.
You can also check in the Event Log or SQL Server log for more information about the error.
ASKER
Ok, I added trace flag T-902 and started the sql service. After that I removed the trace flag and I restarted the sql server service. It was working fine for few hours. And suddenly sql server service stopped automatically and I found the same error master database corrupted. What will be the best solution here.
Do you have a backup of the master db to restore, make sure a Windows update is not kicking in.
Run with the trace to see if it gets to a point when it terms.
Run with the trace to see if it gets to a point when it terms.
ASKER
I have the master db backup. Could you please the steps.
I posted the link using sqlcmd while the SQL server is not running.
Were you reapplying the SQL server update when it termed?
Were you reapplying the SQL server update when it termed?
ASKER
I didn't do any upgrade. Looks like it happened automatically. So fused about it. After add trace flag t902 it works for temporary.
Often, the update once approved for install will continually retry nuke ss/until unapproved, hidden..
It is puzzling why an update working on a master db corrupts or runs into errors. Do you gave encryption db level enable ......
How recent is your prior master db, you shoukd make sure to separate it (copy it ) to make sure backup maintenance does not clear it up.
It is puzzling why an update working on a master db corrupts or runs into errors. Do you gave encryption db level enable ......
How recent is your prior master db, you shoukd make sure to separate it (copy it ) to make sure backup maintenance does not clear it up.
Do you know which SQL Server version you had before the upgrades?
Check the current version (SELECT @@VERSION) and compare with the old version to see what kind of upgrade it was.
Check the current version (SELECT @@VERSION) and compare with the old version to see what kind of upgrade it was.
ASKER
Hi,
Finally i found in the windows update history that is SQL Server Service Pack 4 (KB1048043) applied on 02/17/2018 and it failed and also failed on 02/18/2018. I restarted the SQL Server and SP 4 applied again on 02/18/2018 and it succeeded. This update managed by our windows group policy update on entire SQL Server prod environment instead of apply at first Test environment. Accidentally it happened for this SQL Server where other succeeded at the first time.
What would be the best practice DBA should do this update from their end and at first apply in the Test Machine?
Thanks
Zahid
Finally i found in the windows update history that is SQL Server Service Pack 4 (KB1048043) applied on 02/17/2018 and it failed and also failed on 02/18/2018. I restarted the SQL Server and SP 4 applied again on 02/18/2018 and it succeeded. This update managed by our windows group policy update on entire SQL Server prod environment instead of apply at first Test environment. Accidentally it happened for this SQL Server where other succeeded at the first time.
What would be the best practice DBA should do this update from their end and at first apply in the Test Machine?
Thanks
Zahid
Best practice is to not let SQL Server updates to run automatically. A proper DBA should attend SQL Server patches as some tasks are required to execute previously before running the patch. Those required tasks are mostly check for active connections and running processes and backing up the databases.
ASKER
Hi Vitor,
SQL Server still breaking and can not start. I got the following error. That i turned on Trace Flag T902 and after i removed it was working fine and last night it breaks again.
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 'msdb110_upgrade.sql' encountered error 2627, state 1, severity 14. 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.
Please help what should i do?
Thanks
Zahid
SQL Server still breaking and can not start. I got the following error. That i turned on Trace Flag T902 and after i removed it was working fine and last night it breaks again.
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 'msdb110_upgrade.sql' encountered error 2627, state 1, severity 14. 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.
Please help what should i do?
Thanks
Zahid
How come is still breaking if you told before that you could apply SP4 successfully?
Is there more than one SQL Server instance?
Is there more than one SQL Server instance?
ASKER
Just only an one instance. It broke down the above mentioned error when I tried to start up the sql service. SP 4 was applied successfully third time. I thought I will work fine. Yesterday sql server service was stopped. When I add trace flag 902 it takes start. Now I removed the trace flag 902 sql server service is running now. I guess I have to do permanent solutions.
Thanks
Thanks
Trace Flag T902 isn't a permanent solution. It's used to bypass that upgrade script.
You might need to load manually the script that it's origin the issue and run it inside SSMS to see where it stops so you can try to solve it.
You might need to load manually the script that it's origin the issue and run it inside SSMS to see where it stops so you can try to solve it.
ASKER
Vitor,
Here is the details from error log:--
2018-02-26 17:12:50.82 spid5s Creating SSIS folders...
2018-02-26 17:12:50.82 spid5s Error: 2627, Severity: 14, State: 1.
2018-02-26 17:12:50.82 spid5s Violation of UNIQUE KEY constraint 'U_sysssispackagefoldersun
2018-02-26 17:12:50.82 spid5s Error: 912, Severity: 21, State: 2.
2018-02-26 17:12:50.82 spid5s Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2627, state 1, severity 14. 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.
Here is the details from error log:--
2018-02-26 17:12:50.82 spid5s Creating SSIS folders...
2018-02-26 17:12:50.82 spid5s Error: 2627, Severity: 14, State: 1.
2018-02-26 17:12:50.82 spid5s Violation of UNIQUE KEY constraint 'U_sysssispackagefoldersun
2018-02-26 17:12:50.82 spid5s Error: 912, Severity: 21, State: 2.
2018-02-26 17:12:50.82 spid5s Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2627, state 1, severity 14. 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.
What the following returns?
SELECT *
FROM master.dbo.sysssispackagefoldersASKER
ASKER
Error log and the script is attached. The attached script is creating the issue. Please review.
ERRORLOG
msdb110_upgrade.sql
ERRORLOG
msdb110_upgrade.sql
Unfortunately you do not have a ...
The error follows the "CreatING SSIS"
locating the spot in the script this message appear can provide a way to identify the package identified by the unique number.
Potentially a hot fix or a fixit guide from an external source was used to create this entry/resource that this script does not handle gracefully.
Using the script code that looks up, to and then review/consider. Backup the msdb, ......
An DBA is likely needed to review what the package is and what its significance...
The error follows the "CreatING SSIS"
locating the spot in the script this message appear can provide a way to identify the package identified by the unique number.
Potentially a hot fix or a fixit guide from an external source was used to create this entry/resource that this script does not handle gracefully.
Using the script code that looks up, to and then review/consider. Backup the msdb, ......
An DBA is likely needed to review what the package is and what its significance...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Zahid, is the issue solved?
ASKER
Yes, I got resolved. Sorry i was busy with my other projects.
ASKER
Thanks Vitor! Appreciate your help. Master database restore it was really bogus error masg at that point. Thanks again you sorted out this critical problem.
Glad that I could help.
Cheers.
Cheers.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-the-master-database-transact-sql
Copy the master.mdb/ldb of the failed update.
Not quite sure which update were you installing that might explain why it failed to make adjustments to the master db.
Hopefully your backup is recent.