SQL Service is not starting after upgrade...

Question

SQL Service is not starting after upgrade...

us26

Old Hand

Points: 350
More actions
October 28, 2023 at 12:21 pm

#4316573

Seems to be Windows is somehow decided to upgrade something in SQL Server and after that it can't start.
SQL Service is starting correctly only with trace flag 902.
How to fix the issue?
Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 15151, 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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90253 More actions · Answer 1

Have you reviewed this: https://sqlnuggets.com/yikes-cannot-recover-the-master-database/

To further troubleshoot - you need to find the error that preceded this error. Once you have that you can then figure out what needs to be done to fix the issue.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

If doing what the error text you posted (checking the logs) brings no joy, I can think of no other recovery than to do restores from the backups of the old system.

I know... not much help there but I've never done an "inplace" upgrade for fear of exactly what has happened for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

us26 Old Hand Points: 350 More actions · Answer 3

This fixed the issue:

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/install/windows/sql-server-upgrade-failed-error-15151

Thanks.

I wonder would it be possible to use SQL Profiler (or XE) to find out this kind of issues?

To detect the batch (statement) failed during service startup, to get exact error text and then create a fix myself similar to the script from this link.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

us26 wrote:

This fixed the issue: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/install/windows/sql-server-upgrade-failed-error-15151 Thanks. I wonder would it be possible to use SQL Profiler (or XE) to find out this kind of issues? To detect the batch (statement) failed during service startup, to get exact error text and then create a fix myself similar to the script from this link.

Thanks for the feedback on what you found. To be clear, you had to rebuild the missing "##MS_SSISServerCleanupJobUser##" login or was there something else?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 5

I had that in SQL 2017 2-3 years ago and all I had to do was create the login and user to recover - after several hours of troubleshooting.

MS claims that it is from manually dropping the user or restoring SSISDB incorrectly but was very confident the user had not been dropped and I knew for a fact that it had never been restored from backup. My configuration was a single node AAG consisting of a two node FCI. (DR site was planned but never got implemented). I assumed it was a bug in a patch somewhere unique to my sort of unusual deployment. Many previous CU installed fine over the previous several months and then suddenly failed.

us26 Old Hand Points: 350 More actions · Answer 6

To be clear, you had to rebuild the missing "##MS_SSISServerCleanupJobUser##" login or was there something else?

I'm not sure what statement fixed the issue but all the steps from that article fixed it.

us26 Old Hand Points: 350 More actions · Answer 7

.. restoring SSISDB incorrectly ..

Hm.. Interestingly, I recently restored SSISDB from backup taken from another SQL Server (production server) .

Is it wrong?

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 8

us26 wrote:

.. restoring SSISDB incorrectly ..
Hm.. Interestingly, I recently restored SSISDB from backup taken from another SQL Server (production server) . Is it wrong?

Yes. There is an encryption key in SSISDB you have to backup and restore separately and some permissions that have to be set for some CLR code SSIS uses. I dpn't remember all the steps or details but it was something that I rehearsed for DR.

EdVassie SSC Guru Points: 60445 More actions · Answer 9

The error reported by the OP has sometimes happened to me when applying a CU to a clustered SQL instance.

The work-round in Jeffrey Williams post shows the process we had to do when we got hit by it.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara