June 24, 2021 at 2:42 pm
Hello,
I'm fairly new to installing SQL 2019 standard and am having two issues.
When I first installed SQL server 2019 standard on a server 2019 box, the problem I got was I could not run the SQL Server Agent.
When I go to SQL Server Configuration Manager, the state it is in is "stopped" and when I look at the start mode, it shows Other(Boot, System, Disabled, or Unknown)
When I try starting it, I get the following message:
"The SQL Server Agent (SQL2019STD) service on local computer started and then stopped. Some Services stop automatically if they are not in use by other services or programs."
I checked the SQL agent out log and this is what I get:
2021-06-24 10:37:45 - ? [508] Logging SQL Server Agent messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019STD\MSSQL\Log\SQLAGENT.OUT'.
2021-06-24 10:37:45 - ? [000] Event Global\sqlserverRecComplete$SQL2019STD opened
2021-06-24 10:37:45 - ? [100] Microsoft SQLServerAgent version 15.0.2000.5 (X64 unicode retail build) : Process ID 3936
2021-06-24 10:37:45 - ? [495] The SQL Server Agent startup service account is QEI\QEIWEB$.
2021-06-24 10:37:45 - ? [393] Waiting for SQL Server to recover database 'msdb'...
2021-06-24 10:37:45 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2021-06-24 10:37:45 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2021-06-24 10:37:45 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2021-06-24 10:37:45 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2021-06-24 10:37:45 - ? [098] SQLServerAgent terminated (normally)
the other issue I'm having is when I try logging in to SSMS, it says it cannot connect to the name of the server.
see below for the message:
Cannot connect to HOSTNAME. ( I masked the real name of the computer for security reasons)
===================================
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
------------------------------
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-2-database-engine-error
------------------------------
Error Number: 2
Severity: 20
State: 0
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
===================================
The system cannot find the file specified
Can anyone please lead me to the right direction on this? I have a feeling it's the way I installed it and it wasn't done properly.
June 24, 2021 at 4:09 pm
The error messages related to the SQL Agent sound like something is broken in MSDB. If this is a NEW 2019 install, I would do an uninstall and reinstall it fresh as it looks like something went sideways during the install OR something was done that is non-standard post install. What I mean by "non standard" would be that you installed the SQL instance and then copied system databases from a different instance over top of the system databases for the new instance. If that is what you did, that will cause all sorts of odd side effects.
As for the second error, to me that sounds like you didn't install it as a default instance but as a named instance so you will need to connect as HOSTNAME\INSTANCENAME. Alternately, it could be that your firewall is blocking the connection, or (as the error says) "that SQL Server is configured to allow remote connections". Another thing it could be is that you configured it to not listen on a valid protocol. What I mean is that it isn't listening for a TCP/IP (the standard setup).
If this is a brand new install, I would uninstall the instance and install it again fresh as it looks to me like something went sideways and we on the forum have no idea what steps you followed to do this install, so my advice above is entirely guesswork based on the error messages.
Taking a step back with this, how did you install it? You indicated that you expect the problem is based on how you installed it which makes me think that it wasn't just a normal install but that you had some post-install steps that you performed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 24, 2021 at 5:07 pm
Hi Brian,
you were right, I didn't connect to the right instance name which is why I couldn't connect. However, the problem still remains with trying to run the agent. I tried using my domain name and adding permissions to the database, and now I get the following error in the SQL out log.
2021-06-24 12:59:14 - ? [508] Logging SQL Server Agent messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019STD\MSSQL\Log\SQLAGENT.OUT'.
2021-06-24 12:59:14 - ? [000] Event Global\sqlserverRecComplete$SQL2019STD opened
2021-06-24 12:59:14 - ? [100] Microsoft SQLServerAgent version 15.0.2000.5 (X64 unicode retail build) : Process ID 4368
2021-06-24 12:59:14 - ? [495] The SQL Server Agent startup service account is QEI\jcuello.
2021-06-24 12:59:14 - ? [393] Waiting for SQL Server to recover database 'msdb'...
2021-06-24 12:59:15 - ? [000] Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2021-06-24 12:59:15 - ? [101] SQL Server CONTOSO\SQL2019STD version 15.00.2000 (0 connection limit)
2021-06-24 12:59:15 - ? [102] SQL Server ODBC driver version 17.07.002
2021-06-24 12:59:15 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is CONTOSO\SQL2019STD
2021-06-24 12:59:15 - ? [310] 8 processor(s) and 8109 MB RAM detected
2021-06-24 12:59:15 - ? [339] Local computer is CONTOSO running Windows Server 2019 Standard 10.0 (17763)
2021-06-24 12:59:15 - ! [000] This installation of SQL Server Agent is disabled. The edition of SQL Server that installed this service does not support SQL Server Agent.
2021-06-24 12:59:15 - ? [000] Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2021-06-24 12:59:15 - ? [098] SQLServerAgent terminated (normally)
I looked this up on other articles, and it says it has to do with installing SQL express, which isn't the case for mine, since I'm using SQL 2019 Standard.
Any ideas?
June 24, 2021 at 6:07 pm
Looking at your log there, it says that the edition of SQL Server that installed the service does not support SQL Server Agent.
I would double check your installation media to ensure that you did in fact use the Standard edition installation media and didn't accidentally use the Express installer.
You could also check what the results of:
SELECT @@VERSION
are as that will tell you if you are on Standard or Express.
Also, may want to edit your post to hide the domain and server names.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 25, 2021 at 12:21 am
I found out what the problem was.
I accidently installed express and not standard which was why the agent was not running. Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply