SQL Server Agent not starting after in-place upgrade of SQL Server from 2012 to 2019

Question

I have done an in-place upgrade of SQL Server from SQL Server 2012 Standard Edition (64-bit) SP4 to

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor).

After upgrading, the SQL Server Agent is in a stopped state.

I have tried using the Local System account but still no luck.

Error in Windows event logs is:

SQLServerAgent could not be started (reason: SQLServerAgent cannot start because the msdb database is read-only).

I have checked and confirm that the msdb database is not in read only state.

Can someone help me to resolve this issue.

I am connected to the correct instance. I have applied the command:

ALTER DATABASE [msdb] SET READ_WRITE WITH NO_WAIT;

There was no error.

The drive that stores msdb has not run out of space. No space issue as such. The SQL Server service account has access to the drive for those files.

I do have backups of msdb from before the upgrade.

Logs

2023年04月05日 15:22:13.640 Server Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor) 
2023年04月05日 15:22:13.650 Server UTC adjustment: 1:00
2023年04月05日 15:22:13.710 Server (c) Microsoft Corporation.
2023年04月05日 15:22:13.710 Server All rights reserved.
2023年04月05日 15:22:13.760 Server Server process ID is 5180.
2023年04月05日 15:22:13.760 Server System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
2023年04月05日 15:22:13.760 Server Authentication mode is MIXED.
2023年04月05日 15:22:13.830 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2023年04月05日 15:22:13.840 Server The service account is 'BICESTER\service_vrsunsql-01'. This is an informational message; no user action is required.
2023年04月05日 15:22:13.870 Server Registry startup parameters: -d I:\DATA\master.mdf -e C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG -l L:\LOGS\mastlog.ldf -T 1118 -T 902
2023年04月05日 15:22:13.870 Server Command Line Startup Parameters: -s "MSSQLSERVER"
2023年04月05日 15:22:13.870 Server Trace flag 1118 is discontinued. Use the options provided with ALTER DATABASE.
2023年04月05日 15:22:14.430 Server SQL Server detected 2 sockets with 2 cores per socket and 2 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2023年04月05日 15:22:14.430 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2023年04月05日 15:22:14.440 Server Detected 24575 MB of RAM. This is an informational message; no user action is required.
2023年04月05日 15:22:14.500 Server Using conventional memory in the memory manager.
2023年04月05日 15:22:14.550 Server Page exclusion bitmap is enabled.
2023年04月05日 15:22:14.980 Server Buffer Pool: Allocating 4194304 bytes for 2785280 hashPages.
2023年04月05日 15:22:15.800 Server Default collation: Latin1_General_CI_AS (us_english 1033)
2023年04月05日 15:22:18.770 Server Buffer pool extension is already disabled. No action is necessary.
2023年04月05日 15:22:20.090 Server Query Store settings initialized with enabled = 1, 
2023年04月05日 15:22:20.170 Server The maximum number of dedicated administrator connections for this instance is '1'
2023年04月05日 15:22:20.170 Server This instance of SQL Server last reported using a process ID of 12452 at 05/04/2023 15:21:40 (local) 05/04/2023 14:21:40 (UTC). This is an informational message only; no user action is required.
2023年04月05日 15:22:20.190 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2023年04月05日 15:22:20.250 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2023年04月05日 15:22:20.320 Server In-Memory OLTP initialized on lowend machine.
2023年04月05日 15:22:20.480 Server [INFO] Created Extended Events session 'hkenginexesession' 
2023年04月05日 15:22:20.490 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2023年04月05日 15:22:20.510 Server Total Log Writer threads: 2. This is an informational message; no user action is required.
2023年04月05日 15:22:20.640 Server CLR version v4.0.30319 loaded.
2023年04月05日 15:22:20.650 Server clwb is selected for pmem flush operation.
2023年04月05日 15:22:21.170 spid10s Starting up database 'master'.
2023年04月05日 15:22:21.200 Server Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2023年04月05日 15:22:21.470 Server Software Usage Metrics is enabled.
2023年04月05日 15:22:22.030 spid10s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2023年04月05日 15:22:22.100 spid10s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2023年04月05日 15:22:22.500 spid10s SQL Trace ID 1 was started by login "sa".
2023年04月05日 15:22:22.540 spid10s Server name is 'VRSUNSQL-01'. This is an informational message only. No user action is required.
2023年04月05日 15:22:22.590 spid34s The certificate [Cert Hash(sha1) "793CBB1F5F4CF0626C1920BEEA6E8D24E96E2C36"] was successfully loaded for encryption.
2023年04月05日 15:22:22.600 spid34s Server is listening on [ 'any' <ipv6> 1433].
2023年04月05日 15:22:22.610 spid34s Server is listening on [ 'any' <ipv4> 1433].
2023年04月05日 15:22:22.630 spid34s Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2023年04月05日 15:22:22.650 spid34s Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2023年04月05日 15:22:22.680 spid11s Starting up database 'mssqlsystemresource'.
2023年04月05日 15:22:22.690 Logon Error: 17187, Severity: 16, State: 1.
2023年04月05日 15:22:22.690 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: fe80::b425:769:1663:c836%10]
2023年04月05日 15:22:22.700 spid36s Starting up database 'msdb'.
2023年04月05日 15:22:22.700 spid42s Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2023年04月05日 15:22:22.720 spid42s Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2023年04月05日 15:22:22.720 Server Server is listening on [ ::1 <ipv6> 1434].
2023年04月05日 15:22:22.720 spid38s Starting up database 'SUNDB'.
2023年04月05日 15:22:22.730 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2023年04月05日 15:22:22.740 spid39s Starting up database 'SunSystemsSecurity'.
2023年04月05日 15:22:22.750 Server Dedicated admin connection support was established for listening locally on port 1434.
2023年04月05日 15:22:22.760 spid40s Starting up database 'QA10'.
2023年04月05日 15:22:22.780 spid41s Starting up database 'SapphirePowerTools'.
2023年04月05日 15:22:22.800 spid34s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2023年04月05日 15:22:22.820 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2023年04月05日 15:22:22.850 spid11s The resource database build version is 15.00.2000. This is an informational message only. No user action is required.
2023年04月05日 15:22:23.150 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VRSUNSQL-01.valueretail.com ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2023年04月05日 15:22:23.150 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VRSUNSQL-01.valueretail.com:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2023年04月05日 15:22:24.190 spid11s Starting up database 'model'.
2023年04月05日 15:22:25.290 spid38s Parallel redo is started for database 'SUNDB' with worker pool size [2].
2023年04月05日 15:22:25.300 spid40s Parallel redo is started for database 'QA10' with worker pool size [2].
2023年04月05日 15:22:25.350 spid39s Parallel redo is started for database 'SunSystemsSecurity' with worker pool size [2].
2023年04月05日 15:22:25.360 spid41s Parallel redo is started for database 'SapphirePowerTools' with worker pool size [2].
2023年04月05日 15:22:25.390 spid40s Parallel redo is shutdown for database 'QA10' with worker pool size [2].
2023年04月05日 15:22:25.440 spid38s Parallel redo is shutdown for database 'SUNDB' with worker pool size [2].
2023年04月05日 15:22:25.470 spid39s Parallel redo is shutdown for database 'SunSystemsSecurity' with worker pool size [2].
2023年04月05日 15:22:25.480 spid41s Parallel redo is shutdown for database 'SapphirePowerTools' with worker pool size [2].
2023年04月05日 15:22:25.560 spid11s Clearing tempdb database.
2023年04月05日 15:22:25.630 spid41s CHECKDB for database 'SapphirePowerTools' finished without errors on 2023年04月02日 00:09:22.080 (local time). This is an informational message only; no user action is required.
2023年04月05日 15:22:25.650 spid39s CHECKDB for database 'SunSystemsSecurity' finished without errors on 2023年04月02日 00:09:18.850 (local time). This is an informational message only; no user action is required.
2023年04月05日 15:22:25.740 spid38s CHECKDB for database 'SUNDB' finished without errors on 2023年04月02日 00:00:01.610 (local time). This is an informational message only; no user action is required.
2023年04月05日 15:22:25.760 spid40s CHECKDB for database 'QA10' finished without errors on 2023年04月02日 00:09:21.510 (local time). This is an informational message only; no user action is required.
2023年04月05日 15:22:25.770 spid36s CHECKDB for database 'msdb' finished without errors on 2023年04月05日 15:07:59.290 (local time). This is an informational message only; no user action is required.
2023年04月05日 15:22:26.000 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023年04月05日 15:22:26.270 spid11s Starting up database 'tempdb'.
2023年04月05日 15:22:26.480 spid11s The tempdb database has 1 data file(s).
2023年04月05日 15:22:26.510 spid36s The Service Broker endpoint is in disabled or stopped state.
2023年04月05日 15:22:26.510 spid36s Server is listening on [ 'any' <ipv6> 5022].
2023年04月05日 15:22:26.510 spid36s Server is listening on [ 'any' <ipv4> 5022].
2023年04月05日 15:22:26.510 spid36s The Database Mirroring endpoint is now listening for connections.
2023年04月05日 15:22:26.710 spid36s Service Broker manager has started.
2023年04月05日 15:22:26.710 spid10s Recovery is complete. This is an informational message only. No user action is required.
2023年04月05日 15:22:26.910 spid52 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2023年04月05日 15:22:26.970 spid52 Using 'xpsqlbot.dll' version '2019.150.2000' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2023年04月05日 15:22:27.150 spid52 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023年04月05日 15:23:35.040 spid56 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023年04月05日 15:23:35.070 spid56 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023年04月05日 15:27:13.920 Logon Error: 18456, Severity: 14, State: 5.
2023年04月05日 15:27:13.920 Logon Login failed for user 'BICESTER\VRSUNSQL-01$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
2023年04月05日 15:28:03.600 spid60 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023年04月05日 15:28:03.640 spid60 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023年04月05日 15:29:26.920 spid51 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023年04月05日 15:29:26.960 spid51 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023年04月05日 15:29:30.880 spid51 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023年04月05日 15:29:30.910 spid51 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023年04月05日 15:32:14.500 Logon Error: 18456, Severity: 14, State: 5.
2023年04月05日 15:32:14.500 Logon Login failed for user 'BICESTER\VRSUNSQL-01$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

Answer 1

You did inplace upgrade in availability groups, hope you followed the procedure.

From the logs

2023年04月05日 15:22:22.700 spid42s Always On: The availability replica manager is starting. This is an informational message only. No user action is required.

You have availability groups configured.

Error: 18456, Severity: 14, State: 5. 2023年04月05日 15:32:14.500 Logon Login failed for user 'BICESTER\VRSUNSQL-01$'. Reason: Could not find a login matching the name provided. [CLIENT: ]

You are running SQL Server agent with local system or network service account and it is trying to access a remote resource and did not had permission, this happens when using AOAG. . Use a proper domain account or managed service account. As a work around you can go to security logins and add BICESTER\VRSUNSQL-01$ as a login in VARUNSQL-01. This is not recommended approach.

You already have BICESTER\service_vrsunsql-01 domain account. Can you use this as service account to start agent. ?

• Sayali Dikshit: Already tried with starting agent with BICESTER\service_vrsunsql-01
• Shanky: So what was the error, did it not started ? Add BICESTER\service_vrsunsql-01 as login into SQL Server both nodes of AG

Answer 2

When you try to start the sql server agent directly from sql configuration manager? Try doing that and if it fails, it should log the error in the windows event viewer as to why the agent is failing to start.