Sqlserveragent could not be started reason ошибка при создании нового сеанса

If you are trying to start SQL Server Agent and you get the error message SQLServerAgent could not be started (reason: Unable to connect to server ‘(local)’; SQLServerAgent cannot start) then read below, in order to see what you can try for resolving the issue.

How to Resolve the Issue

Go to computer management – local users and groups, and add the service account with which you want SQL Server Agent service to start in the below Windows group:
SQLServerSQLAgentUser$[ComputerName]$[InstanceName]

Then, make sure that the service account meets the requirements described on the following MSDN Article.

One of the requirements is that the SQL Server Agent service account is SysAdmin on the SQL Server instance.

So, among the rest of the requirements, make sure that the local Windows Group “SQLServerSQLAgentUser$[ComputerName]$[InstanceName]” has SysAdmin access on the SQL Server instance (important note: never add any irrelevant users to this group – just the service account that start SQL Server agent service).

The last step is to go to SQL Server Configuration Manager, enter SQL Server Agent properties and make sure that the service account that starts the service is the one for which you followed the above steps.

Note: In order to avoid such issues in the future, make sure that during the installation of SQL Server you specify the proper service accounts. This way, SQL Server’s installation process will automatically grant the necessary permissions and arrange the required group memberships for the specified service accounts. 

Learn more tips like this! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Enroll from $12.99

If the Above did not Resolve the Issue

If the above did not resolve the issue, there are some additional steps that might help with your troubleshooting process. To this end, you can try reading the SQL Server error log, in order to find additional information about the error. You can easily find the location of SQL Server’s log file by running the below T-SQL query from SQL Server Management Studio:

USE master
GO
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 
GO

Alternatively, you can check the Application Log in Windows Event Viewer on the database server.

Featured Online Courses:

• SQL Server 2022: What’s New – New and Enhanced Features [New]
• Data Management for Beginners – Main Principles
• Introduction to Azure Database for MySQL
• Working with Python on Windows and SQL Server Databases
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
• A Guide on How to Start and Monetize a Successful Blog
• Data Management for Beginners – Main Principles

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Check out our latest software releases!

Check our eBooks!

Rate this article: (2 votes, average: 5.00 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Troubleshooting: SQLServerAgent could not be started (reason: Unable to connect to server ‘(local)’; SQLServerAgent cannot start)

Click to Tweet

Views: 11,006

SQLRNNR

SSC Guru

Points: 281324

 After a Windows Patch upgrade on a  server with SQL Server 2005 installed , start up error messages appeared on start up of some SQL Server Services.

SQLServerAgent could not be started (reason: Error creating a new session).

This was strange , as there were three SQLServer Agent services on this server – all using the same service account username and password. Two started OK. But the third generated the error message

 After some trial and error , I decided to complete the following steps , which fixed the problem :

1) Switched the account in windows services to a local system account.

2) Started SQL Agent  — OK

3) Stopped SQL Agent — OK

4) Changed the service account back to the service account in windows services

A strange error and I still haven’t understood the root cause. I assumed it would be changes in permissions on the SQL Agent security context and I did look at the account . The account was under the correct group with the correct privileges.

I also used this technique to Find who made a database security change — SQL Server DBA

If anyone has experienced similar problems, let me know  — either contact me or leave a comment

Read More on managing SQL Server security

Powershell sql server security audit — SQL Server DBA

SQL Server Security Policy — SQL Server DBA

Security Risk Analysis and database security — SQL Server DBA

Author: Tom Collins (http://www.sqlserver-dba.com)

Share:

I start a Windows SQL Server in a Docker Container on a Windows Server 2016 virtual machine with this command:

docker container run --name mssql-server --detach --publish 1433:1433 --memory 2048mb --volume C:_Data:C:_Data --env sa_password=myPassword --env ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

Afterwards I want to start the SQL Agent with this command:

docker container exec mssql-server cmd.exe /C "net start sqlserveragent"

Unfortunately I get this Output:

The SQL Server Agent (MSSQLSERVER) service is starting.
The SQL Server Agent (MSSQLSERVER) service could not be started.

The service did not report an error.

More help is available by typing NET HELPMSG 3534

When I type NET HELPMSG 3534 I get:

The service did not report an error.

I am completely out of ideas. On my local machine this works fine, but not on the build server.

Any help appreciated.

Edit:
I opened the SQL Server Management Studio and noticed the SQL Server Agent (Agent XPs disabled) node.
So I went through everything in https://www.mssqltips.com/sqlservertip/2729/how-to-start-sql-server-agent-when-agent-xps-show-disabled/. Afterwards I executed

exec sp_configure 'Agent XPs'

and the result was:

But when I tried to start the agent, I got the exact same error message from above (service ist starting… service could not be started… no error reported).

The interesting thing is, when I run

exec sp_configure 'Agent XPs'

again, I get this:

How is it possible, that the start-agent-command disables the Agent XPs?

Edit2: I just learned from a comment at https://community.spiceworks.com/topic/293771-sql-server-agent-agent-xps-disabled-sql-server-2008 that the agent configures this values automatically when it is started and stopped. So I shouldn’t enabled it manually.

Edit3:
After a comment from Jeroen Mostert I looked into the EventLog.
I used the following command:

docker container exec mssql-server powershell.exe "Get-EventLog Application -Newest 100"

This is the result from the Application-Log

Time         EntryType   Source                                  InstanceID Message                                                                                        
----         ---------   ------                                  ---------- -------                                                                                        
Jun 25 10:37 Information SQLSERVERAGENT                          1073741926 SQLServerAgent service successfully stopped.                                                   
Jun 25 10:37 Information MSSQLSERVER                             1073757281 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
Jun 25 10:36 Information MSSQLSERVER                             1073757281 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

You can see, that I have manually enabled the Agent XPs. Then I ran the command from above to start the agent, which gave me the next two lines

The System-EventLog gave me:

Time         EntryType   Source                                   InstanceID Message                                                                                                                                                                                                                                                             
----         ---------   ------                                   ---------- -------                                                                                                                                                                                                                                                             
Jun 25 10:37 Information Service Control Manager                  1073748860 The SQL Server Agent (MSSQLSERVER) service entered the stopped state.                                                                                                                                                                                               

Edit 4:
As per the comment from Dan Guzman: this is the content of SQLAGENT.OUT:

2019-07-09 11:03:44 - ? [000]
2019-07-09 11:03:44 - ? [098] SQLServerAgent terminated (normally)

Edit 5:
The version is (select @@version) is:

Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64)   Dec 22 2017 16:13:22   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor) 

Fixing Startup Failure of SQL Server Agent Service in SQL Server 2005