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.
Zahid AhamedDatabase AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
You have little choice other than restoring the database from backup
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
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.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
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.
0
arnoldCommented:
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.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
I have the master db backup. Could you please the steps.
0
arnoldCommented:
I posted the link using sqlcmd while the SQL server is not running.

Were you reapplying the SQL server update when it termed?
0
Zahid AhamedDatabase AdministratorAuthor Commented:
I didn't do any upgrade. Looks like it happened automatically. So fused about it. After add trace flag t902 it works for temporary.
0
arnoldCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
1
Zahid AhamedDatabase AdministratorAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
How come is still breaking if you told before that you could apply SP4 successfully?
Is there more than one SQL Server instance?
0
Zahid AhamedDatabase AdministratorAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
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_sysssispackagefoldersuniquepath'. Cannot insert duplicate key in object 'dbo.sysssispackagefolders'. The duplicate key value is (8877fe4b-a938-4a51-84b9-c5bdad74b0ad, Generated).
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What the following returns?
SELECT *
FROM master.dbo.sysssispackagefolders

Open in new window

0
Zahid AhamedDatabase AdministratorAuthor Commented:
I ran under msdb

output is attached.
output.txt
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Error log and the script is attached. The attached script is creating the issue. Please review.
ERRORLOG
msdb110_upgrade.sql
0
arnoldCommented:
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...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The error is:
"2018-02-26 18:59:44.89 spid6s      Error: 2627, Severity: 14, State: 1.
2018-02-26 18:59:44.89 spid6s      Violation of UNIQUE KEY constraint 'U_sysssispackagefoldersuniquepath'. Cannot insert duplicate key in object 'dbo.sysssispackagefolders'. The duplicate key value is (8877fe4b-a938-4a51-84b9-c5bdad74b0ad, Generated).
"

The constraint U_sysssispackagefoldersuniquepath is:
CONSTRAINT [U_sysssispackagefoldersuniquepath] UNIQUE NONCLUSTERED 
   (
      [parentfolderid], 
      [foldername]
   )

Open in new window


The part of code that is triggering the error is:
-- create 'Generated' folder under 'Data Collector'
IF(NOT EXISTS(SELECT * 
                FROM dbo.sysssispackagefolders
                WHERE folderid = '39163C42-602B-42C9-B4F7-1843614F9625'))
BEGIN
    EXEC dbo.sp_ssis_addfolder
        @parentfolderid = '8877FE4B-A938-4a51-84B9-C5BDAD74B0AD',
        @name = 'Generated',
        @folderid = '39163C42-602B-42c9-B4F7-1843614F9625'
END
GO

Open in new window


Comparing the expected folderid ('39163C42-602B-42C9-B4F7-1843614F9625') with the one you have in your system ('305EF952-B1CC-4240-B2F6-C7A2CDEDA0AC'), we can see that both aren't equal, meaning that an INSERT will occur and this will generate the error your facing.

I don't know why your current table has a different value (I've checked on mine SQL Server instance and the value is the one that's expected - '39163C42-602B-42c9-B4F7-1843614F9625') but you can fix this by deleting that row:
DELETE FROM msdb.dbo.sysssispackagefolders
WHERE folderid = '305EF952-B1CC-4240-B2F6-C7A2CDEDA0AC'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Zahid, is the issue solved?
1
Zahid AhamedDatabase AdministratorAuthor Commented:
Yes, I got resolved. Sorry i was busy with my other projects.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Glad that I could help.
Cheers.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.