SQL Server Agent jobs remains in Execution status
-
Hi, We are running SQL Server 2014 SP2
on Windows Server 2012 R2 Standard, 8 GB RAM, 64-bit OS
this is a VM machine.The SQL Agent jobs are not completing.
In Job Activity they are showing as Executing. The msdb table to query the status of the jobs show that they are executing, however sp_who2 does not show any session performing a dbcc or a backup.
The Errorlog and the Event Viewer also does not have any entry showing that, for example, one db has been backed up or dbcc'd.
None of the jobs have failed, but stays in Executing status.
Syspolicy_purge_history does show that steps 1 and 2 have completed, so it is in step 3 (erase phantom system health records) that it looks to be stuck.Now I can run these maintenance tasks manually by connecting to Management Studio remotely (from my pc) and initiate a backup or integrity check and they complete successfully in normal time, so I suspect there is something going on at the operating system level.
When I log on to the server (rdp) and open SQL Server Configuration Manager it is stuck at "Requesting data from WMI provider" (I've waited hours and there is no change).
I've also tried connecting to Configuration Manager remotely but unable to do so.
With no connection to Configuration Manager I am unable to restart SQL Server Agent.This issue has occurred weeks before and the server was finally rebooted 8/24, last week.
After the reboot obviously things were back to normal -- sql jobs running, Configuration Manager and SSMS can be accessed on the server.
But after a week (8/31, yesterday) the above issues are back, and SSMS on the server could not connect to the local database, it just hangs.The system administrator cannot find anything on the server that would indicate as an issue for him, where should we be looking to address this issue? Before we request for another reboot we would like to identify the culprit first.
Anyone else seen these issues before?thanks in advance
="file:>="file:>
- I've read about a few or more people having similar issues after windows updates.
But since you can RDP to the server, have you tried restarting from services?
From the server you can also try from the command line (run as administrator) - something like
net stop SQLSERVERAGENT (or for a named instance SQLAgent$instancename)
and
net start SQLSERVERAGENTSue
-
hi Sue,
i actually did try restarting SQL Server Agent earlier, and it did restart with no issue, however when i ran a job that job failed and then the Agent crashed and the status indicated "Agent XPs disabled".
i managed to resolve the Agent XP issue and restart SQL Agent but even though you can see the Agent is up the jobs fail with the error saying that the Agent is down. right now i can view the Job Activity monitor but all the Next Run values are showing as "not scheduled".thanks
- sierra4 - Friday, September 1, 2017 4:49 PM
You can change the display of Agent XPs disabled in SQL Server Management Studio by executing sp_configuration and enabling Agent XPs. It does not mean Agent has started and is running. All that does is enable Agent extended stored procedures.
If Agent XPs are enabled and Agent is not running, you will be able to view all the jobs as well as Job Activity Monitor. This does not mean Agent is running. If you try to run a job, it will not run and will report that SQL Server Agent is not currently running so it can't be notified of this action. When you view Job Activity Monitor in this state, all of the Next Run values will show as "not scheduled"
Exactly what you are saying is happening.You need to find another way to start SQL Agent and make sure it is running. You may not want to just look in SSMS to determine if it is running as it seems that may not be working for you. Just having the status in SSMS change Agent XPs disabled to no longer displaying that does not mean agent is running. Look in services and make sure it is running. If you think you are having problems with jobs and Agent is runnning then look in the SQL Server Agent error log to see what the errors are. You would also want to check the Windows event logs.
Sue
-
hi Sue,
correct, those were the steps i carried out, and constantly keeping an eye on Event Viewer and Services, which both indicate that SQL Server Agent is in running state and that no other errors were reported regarding the Agent.
at this point I would like to know more why i cannot access the sql tools when connected to the server, specifically why when i connect to SQL Configuration Manager the display just stays at "Requesting data from WMI provider".
even using Management Studio on the console does not work, it just hangs when trying to connect to the local instance, but i can connect to it when using SSMS from my workstation.
unfortunately the server admins and the VM admins cannot find any issue at the OS level and basically don't want to dig deeper, though the server admin agree that his connection to the console was really slow.i was hoping someone has encountered something similar...
thank you for your help!
- sierra4 - Saturday, September 2, 2017 8:17 AM
And if those are the steps, Agent is not running. So whatever happens with the jobs, Job Activity Monitor would be due to this.
When things work using your PC and not the Server than it seems that the issue would be with the server. When I've had similar WMI errors with SQL Server Configuration Manager, I registered the .mof files using Mofcomp.exe since that will allow the providers to be registered with WMI. To do that on the server:
Check this directory: C:\Program Files (x86)\Microsoft SQL Server\120\Shared
and see if you have a file named: sqlmgmproviderxpsp2up.mof
If you do, open your command prompt as administrator and run this:
mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof"
and then restart the winmgmt serviceThe other thing I have seen for the WMI error is using the Microsoft System Update Readiness Tool to detect issues. I've never tried that way but if you want to try that, the link to the tool is here:
Fix Windows Update errors by using the DISM or System Update Readiness toolSue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply