- Remove From My Forums
-
Общие обсуждения
-
Операционная система Windows 2088 R2.
SQL Server 2008 R2 Enterprise.
Проблема. Непонятно с какой стати стал отрубаться SQL Agent. В логах системы вот такая ошибка
Имя журнала: System Источник: Service Control Manager Дата: 19.05.2014 23:02:47 Код события: 7000 Категория задачи:Отсутствует Уровень: Ошибка Ключевые слова:Классический Пользователь: Н/Д Компьютер: SQL.domen.ru Описание: Сбой при запуске службы "Агент SQL Server (MSSQLSERVER)" из-за ошибки Служба не ответила на запрос своевременно. Xml события: <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"> <System> <Provider Name="Service Control Manager" Guid="{555908d1-a6d7-4695-8e1e-26931d2012f4}" EventSourceName="Service Control Manager" /> <EventID Qualifiers="49152">7000</EventID> <Version>0</Version> <Level>2</Level> <Task>0</Task> <Opcode>0</Opcode> <Keywords>0x8080000000000000</Keywords> <TimeCreated SystemTime="2014-05-19T19:02:47.998731000Z" /> <EventRecordID>15927</EventRecordID> <Correlation /> <Execution ProcessID="564" ThreadID="3928" /> <Channel>System</Channel> <Computer>SQL.real.ru</Computer> <Security /> </System> <EventData> <Data Name="param1">Агент SQL Server (MSSQLSERVER)</Data> <Data Name="param2">%%1053</Data> </EventData> </Event>Порылся в системе и обнаружил что в ветке реестра HKLM-SOFTWARE-MICROSOFT-WINDOWS NT-PERFLIB-009 и HKLM-SOFTWARE-MICROSOFT-WINDOWS NT-PERFLIB-019 кроме значения «По умолчанию» ничего нет.
Вот тут нашел, что нужно перестроить счетчики командой lodctr /R
Счетчики перестраиваются, значения в ветках реестра появляются но агент не запускается.
Через какое-то время значения в ветках реестра опять исчезают.Что делать? Как исправить проблему?
Пока не запустится агент, планы обслуживания не будут работать
P.S Если я сделаю восстановление SQL сервера из дистрибутива то проблема исчезает, но к следующей ночи опять повторяется.
-
Изменено
26 мая 2014 г. 21:05
-
Изменен тип
Иван ПродановMicrosoft contingent staff, Moderator
5 июня 2014 г. 10:06
-
Изменено
In this article, we will discuss the issues when you try to start the SQL Server Agent. We will also discuss the various reasons and possibilities behind the failure of the SQL Server Agent. We will try to troubleshoot the problem with the help of various solutions. Let us try to fix the error, SQL Server Agent won’t start.
SQL Server Agent not starting
Following are the point that you can ensure if the SQL Server Agent on your system is not working:
- If you are using the SQL Server Express Edition, SQL Server Agent will not start. SQL Server Agent is a service that we use to schedule jobs in the SQL Server database.
You might have faced an error like the following image while starting the SQL Server Agent Service from the SQL Server Configuration Manager:
Also, you might have faced the following error while starting the SQL Server Agent Service from the Windows Services:
To open windows service, type Start -> Run and then type services.msc
It is a premium service that is provided in other editions only such as SQL Server Developer Edition or SQL Service Enterprise Edition. So, it will not work in the SQL Server Express Edition. However, if you are facing this issue in other SQL Server editions you can follow the steps below to troubleshoot the issue:
- Another reason could be the corrupted installation or failed installation of the SQL Server. You can try to repair the installation or you can reinstall the SQL Server. You can follow the guide to Repair a failed SQL Server Installation.
- If you have changed the password that you use to login into the database, you have to modify the properties of the SQL Server Agent service and update the password there also.
- Ensure that your Windows Firewall is not blocking it and if it is blocking, add an exception to the firewall to run the SQL Server Agent service.
To know which sql server version, you are using run the below command.
SELECT SERVERPROPERTY('Edition')If you are using the sql server express edition, then it will return either “Express” or “Express Edition with Advanced Services” or “Express Edition *”.
Read SQL Server move database files
SQL Server Agent won’t start automatically
If you want that the SQL Server Agent should start automatically, you have to change its properties in the Windows Services. It is a possibility that the SQL Server Agent is not set to start automatically.
In this section, we will learn how we can change the SQL Server Agent properties to be automatically started.
- Press Windows Key+R to open the Run utility. Type services.msc to open the Windows Services.
- Navigate to the SQL Server Agent service and right-click on it and click Properties.
- Select Automatic in the Startup type option. Click Apply and then OK.
- Now next time you restart your computer, the SQL Server Agent should start automatically.
SQL Server Agent not starting in sql server 2019 express
SQL Server agent is not supported in sql server 2019 express edition. So to fix, sql server agent not starting in sql server 2019 express, you need to take a licensed version.
SQL Server Agent started and then stopped
Let us see, how to fix sql server agent started and then stopped error.
You can follow the below steps to fix the error in SQL Server edition not in the express edition.
Open SQL Server Configuration Manager, right-click on SQL Server Agent in SQL Server services not.
Then Change the Built-in account to the Local System. Then click on Apply and OK.
SQL Server agent not showing
SQL Server agent not showing? If sql server agent not showing in sql server management studio, then it might because of the below reasons:
- Make sure the SQL Server should not be the express edition.
- The login account that you have used to connect to the sql server management studio should have SysAdmin role in the sql server. This you can check by running the below script.
Select IS_SRVROLEMEMBER('Sysadmin')If it returns 0 then the user is not part of a SysAdmin.
You may like the following SQL Server tutorials:
- SQL Server drop table if exists
- MySQL vs SQL Server
- SQL Server convert integer to string
- How to execute function in SQL with parameters
- SQL Server Replace Function
This is how to fix sql server agent not starting automatically error.
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
I just installed my SQL Server 2014 on the top of Windows Server 2012. But after installation SQL Agent is not starting. Can someone tell me like what can I do to solve this? PFA screenshot for the same.
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
I just installed my SQL Server 2014 on the top of Windows Server 2012. But after installation SQL Agent is not starting. Can someone tell me like what can I do to solve this? PFA screenshot for the same.
Ed Harper
21.1k4 gold badges54 silver badges80 bronze badges
asked Apr 7, 2016 at 6:36
2
It’s because you’ve installed SQL Server Express (indicated by the instance name SQLEXPRESS), SQL Server Agent is not supported in this version, it installs the service, but it does not allow it to run. You need one of the non-free SQL server editions in order to use SQL Server Agent.
answered Apr 8, 2016 at 11:13
steolearysteoleary
8,9282 gold badges33 silver badges47 bronze badges
In case you’are using SQL Server Express, before quiting, I think you should know that SQL Server versions (including Express) offers a system stored procedure named sp_procoption, which allows one to define a user stored procedure name to be executed whenever SQL Server instance is started and lives along your SQL Server service. That user stored procedure can contain your onwn scheduling loop.
exec sp_procoption @ProcName = ['put your procedure name'], @OptionName = 'STARTUP', @OptionValue = [on|off]
For further reading, I would suggest checking Armando Pratos’s excellent article and this SQL Server Online Book
TylerH
20.7k65 gold badges73 silver badges98 bronze badges
answered Jul 26, 2018 at 18:29
Julio NobreJulio Nobre
4,1563 gold badges45 silver badges48 bronze badges
SQL Server Agent won’t start up.
Agent XPs is disabled too, even though I followed the following steps
—sp_configure ‘show advanced options’, 1;
—GO
—RECONFIGURE;
—GO
—sp_configure ‘Agent XPs’, 1;
—GO
—RECONFIGURE
—GO
I got the stack trace from below from SQL Management Studio when trying to start SQL Server Agent Service.
===================================
Unable to start service SQLSERVERAGENT on server *****. (mscorlib)
——————————
Program Location:
Server stack trace:
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start()
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ServiceActionHandler.EndInvoke(IAsyncResult result)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ServiceControlProgress.ActionCompletedHandler(IAsyncResult result)
===================================
The SQLSERVERAGENT service on **** started and then stopped. (ObjectExplorer)
——————————
Program Location:
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start()
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!
(Lifetime Access/ Live Demos / Downloadable Resources and more!) 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
Related Error Messages and ways to Resolve them:
- Operating System Error 170 (Requested Resource is in use)
- There is no SQL Server Failover Cluster Available to Join
- Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.
- SQL Server 2008 R2 Service Pack Installation Fails – Element not found. (Exception from HRESULT: 0x80070490)
- Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=10.0.0.0, …
- The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group
- Issue Adding Node to a SQL Server Failover Cluster – Greyed Out Service Account – How to Resolve
- … more SQL Server troubleshooting articles
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
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.
Views: 12,276