Recently in SSMS, I have started getting a blue question mark next to the database and SQL Server Agent, when previously all was showing running. At the same time my scheduled jobs have stopped running. We did have a reset of the server clock around the same time.
I have referenced this post Blue icon with question mark – what does it mean? but the problem persists. It is happening when I am working directly on the Database Server via remote desktop and via local computer SSMS.
On the Database Server,
Configuration Manger shows the services as running
WMI is not blocked and is enabled.
I have checked Windows Fire Wall and WMI is allowed.
I have restarted the SQL Agent. I have also stopped then started the Agent.
I have rebooted the Server. (I ensured there were no active users first.)
I have ensured that the agent is configured with "sp_configure 'Agent XPs', 1".
I modified the start date of each job that won't run to the current date. (I set up a new simple procedure to run as a test and it also won't execute.)
If I start a job manually; it starts but the Execute stalls the error is "A severe error occurred on the current command. The results, if any, should be discarded. (.Net SqlClient Data Provider)" which could indicates that the service is not running, but a "select * from sys.dm_server_services" indicates they are running.
I've checked the error log – it recommends verifying SQL Server is configured for remote connections, which it is.
There is an exception for the 1433 port to allow TCP/IP traffic on Port 1433.
Does anyone know of anything else that might help?
Here is the full error:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo) at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String
sqlCommand, Boolean retry) at
Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String
query) at
Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection
query) at
Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String
query, Object con) at
Microsoft.SqlServer.Management.Smo.PostProcessJobActivity.GetColumnData(String
name, Object data, DataProvider dp) at
Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess()
at Microsoft.SqlServer.Management.Smo.DataProvider.GetTable() at
Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType
resultType, StringCollection sql, Object connectionInfo,
StatementBuilder sb) at
Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult
sqlresult, ResultType resultType) at
Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult
result) at
Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult
erParent) at
Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData() at
Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request
req, Object ci) at
Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object
connectionInfo, Request request) at
Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request
req) at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[]
fields, OrderBy[] orderby) at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[]
fields, OrderBy[] orderby) at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String
propname, Boolean useDefaultValue) at
Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32
index, Boolean useDefaultOnMissingValue) at
Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String
propertyName, Boolean throwOnNullValue, Boolean
useDefaultOnMissingValue) at
Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String
propertyName) at
Microsoft.SqlServer.Management.Smo.Agent.Job.get_LastRunDate() at
Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.WaitForJobToFinishAction.DoAction(ProgressItemCollection
actions, Int32 index) at
Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread()
=================================== A severe error occurred on the current command. The results, if any, should be discarded. (.Net
SqlClient Data Provider)
—————————— For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.4083&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476
—————————— Server Name: QUAD-DB2 Error Number: 0 Severity: 11 State: 0
—————————— Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction
action, Object execObject, DataSet fillDataSet, Boolean
catchException) at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String
sqlCommand, Boolean retry)
Best Answer
There one thing that jumped at me when I looked at the error message. This being the severity level of 11:
This is documented in Microsoft's article Database Engine Error Severities (Microsoft | SQL Docs) as:
This correlates with this bit of information in the error message:
Something seems to be missing and I can only provide you with some general ideas on how to possibly find or fix the issue. As pointed out by Microsoft it is: ...errors that can be corrected by the user.
The Obvious
ERRORLOG file
Have a look at the error log file, especially the part that displays the information after the server was rebooted or the SQL Server service was restarted. Search for issues that might be impacting the SQL Server instance.
SQLAGENT.OUT file
Have a look at the error log file for the SQL Server Agent Service:
SQLAGENT.OUT.The Not-So Obvious
Some of the not-so obvious reasons were posted in the answers you linked in your question. If you've waded through all of them, then you're already halfway there.
SQL Server (Agent) Service Account
Sometimes an update will change the permissions on a drive, a folder/directory or some other portion of SQL Server (executeable, DLL, registry). The easiest way to fix this, is to start the SQL Server 2019 Configuration Manager with an elevated account.
...and change the Account Name for the SQL Serer Agent (INSTANCE_NAME) service to one of the built-in accounts:
When done, click Apply and you will be prompted that the service has to be restarted. Click on OK.
When the service is back up and running, revert your changes back to the previous account:
And apply. You will receive the same prompt that the service has to be restarted:
This will ensure that the (file/registry) permissions for the service account of this service are set correctly on all relevant SQL Server related items.
SQL Server Service Account
In case the above fails, apply the same steps to the actual SQL Server Service Account for your instance. This will ensure that the (file/registry) permissions for the service account of the SQL Server (INSTANCE_NAME) service are set correctly on all relevant SQL Server related items.
Repair
Sometimes the instance is just broken for some unknown reason and manually resetting all the permissions is just not quite what you want to be doing on a sunny afternoon. Take the fast route and repair your instance and/or the shared components .
Mount the ISO file of your SQL Server version and start the SQL Server 2019 Installation Center (64-Bit) from your Windows Server's list of programs.
In the left-hand pane select the Maintenance option and in the right-hand pane that is then displayed the option Repair.
You will be guided through a slimmed down setup routine that will allow you to either repair the SQL Server instance INSTANCE_NAME or the <>.
Further Actions
If these last options fail, then it might be time to open up an SR with Microsoft.
Good hunting.