none
Cannot login to sql server RRS feed

  • Question

  • Hi Guys,

    Could you help me please I'm not able to login to my sql server after update GDR 4259 for SQL server 2016

    the error is Login failed for user . Reason:Server is in script upgrade mode . Only administrator can connect at this time (error 18401) and I saw new error in my Event logs see below

    3417 "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."

    912 "Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 5069, 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."

    do you know how we can fix sql server, thank you very much
    Monday, December 2, 2019 7:43 PM

Answers

  • Hi Mooner09,

    It seems your MSDB database is corrupt according to the log file,maybe you could restore MSDB database with backup or try to repair it with DBCC CHECKDB.I would suggest you to take the following action:

    1. Start SQL server with trace flag -T902 to disable running upgrade scripts (see this link for example). => Put a trace flag -T902 at startup parameter of SQL services (SQL Server(MSSQLSERVER)),then Start service.

    2. Restore msdb from a good backup.If you don't have a backup,you could copy MSDB's data and log files from the same version of the SQL Server, but this means data loss.Or try to fix the corruption in MSDB .Run DBCC CHECKDB and following the recommendations from the output,if the recommendation is running REPAIR,note that need to set the database to single user mode.

    3. Once corruption is fixed, stop SQL server services. Remove startup trace flag -T902 and start SQL services normally.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Mooner09 Tuesday, December 3, 2019 9:03 PM
    Tuesday, December 3, 2019 9:18 AM

All replies

  • Look in the SQL Server errorlog. There you can see the output from the upgrade script. When I run

    SELECT * FROM sys.messages WHERE message_id = 5069

    I see that the text is "ALTER DATABASE statement failed.". There is usually an accompanying error with this message.

    The log file is in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\ERRORLOG if you have a default installation.

    If you want help with understanding it, upload it to Dropbox or similar and post a link. (It may be a wee bit too long to include here.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 2, 2019 10:32 PM
    Moderator
  • HI Erland,

    this is from logs file

    starting execution of PRE_MSDB.SQL
    2019-12-02 18:41:52.98 spid4s      ----------------------------------
    2019-12-02 18:41:52.99 Logon       Error: 18401, Severity: 14, State: 1.
    2019-12-02 18:41:52.99 Logon       Login failed for user 'domain\username'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: *.*.*.*]
    2019-12-02 18:41:53.61 spid4s      Setting database option COMPATIBILITY_LEVEL to 100 for database 'msdb'.
    2019-12-02 18:41:55.31 spid4s      Error: 824, Severity: 24, State: 2.
    2019-12-02 18:41:55.31 spid4s      SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x9f22ee5c; actual: 0xff82e727). It occurred during a read of page (1:72055) in database ID 4 at offset 0x000000232ee000 in file 'D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
    2019-12-02 18:41:55.31 spid4s      Error: 5069, Severity: 16, State: 1.
    2019-12-02 18:41:55.31 spid4s      ALTER DATABASE statement failed.
    2019-12-02 18:41:55.31 spid4s      Error: 912, Severity: 21, State: 2.
    2019-12-02 18:41:55.31 spid4s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 5069, 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.
    2019-12-02 18:41:55.31 spid4s      Error: 3417, Severity: 21, State: 3.
    2019-12-02 18:41:55.31 spid4s      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.
    2019-12-02 18:41:55.31 spid4s      SQL Server shutdown has been initiated
    2019-12-02 18:41:55.31 spid4s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
    2019-12-02 18:41:55.45 spid11s     The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/sqlserver] for the SQL Server service.
    2019-12-02 18:41:55.45 spid11s     The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/sqlserver:1433 ] for the SQL Server service.

    Monday, December 2, 2019 11:55 PM
  • As you can see your msdb database is corrupt. I will have to admit that I don't know what is the best way to proceed in this particular situation. Normally, the recommendation would be to restore a backup, but since the instance is in the process of an update, I'm not sure how we could to that position. Rather than giving bad advice that could make matters worse, I say nothing at all.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 3, 2019 8:17 AM
    Moderator
  • Hi Mooner09,

    It seems your MSDB database is corrupt according to the log file,maybe you could restore MSDB database with backup or try to repair it with DBCC CHECKDB.I would suggest you to take the following action:

    1. Start SQL server with trace flag -T902 to disable running upgrade scripts (see this link for example). => Put a trace flag -T902 at startup parameter of SQL services (SQL Server(MSSQLSERVER)),then Start service.

    2. Restore msdb from a good backup.If you don't have a backup,you could copy MSDB's data and log files from the same version of the SQL Server, but this means data loss.Or try to fix the corruption in MSDB .Run DBCC CHECKDB and following the recommendations from the output,if the recommendation is running REPAIR,note that need to set the database to single user mode.

    3. Once corruption is fixed, stop SQL server services. Remove startup trace flag -T902 and start SQL services normally.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Mooner09 Tuesday, December 3, 2019 9:03 PM
    Tuesday, December 3, 2019 9:18 AM
  • Hi Cris ,

    Yes you a right MSDB is corrupt , we fixed by restore it from backup,

    thank you for your help

    Tuesday, December 3, 2019 9:03 PM
  • Hi Cris ,

    Yes you a right MSDB is corrupt , we fixed by restore it from backup,

    thank you for your help

    Great to hear that you got it sorted it. It is never fun to be victim to corruption.

    Keep in mind, though, that corruption does not come out of the blue, but it is always due to some malfunctioning hardware. And if it is not something like a power failure, the underlying may still be present, and you could run into corruption again in the future. So be sure to check the integrity of msdb and other databases on the same drive regularly.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 3, 2019 10:24 PM
    Moderator