0

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>]
0

2 Answers 2

2

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. ?

2
  • Already tried with starting agent with BICESTER\service_vrsunsql-01 Apr 6, 2023 at 11:23
  • So what was the error, did it not started ? Add BICESTER\service_vrsunsql-01 as login into SQL Server both nodes of AG
    – Shanky
    Apr 6, 2023 at 11:24
-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.