SSMS Not showing Status and Scheduled Jobs Not Running

Question

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)

score 1 · Answer 1 · answered Jan 20 '21 at 12:53

There one thing that jumped at me when I looked at the error message. This being the severity level of 11:

Error Number: 0 Severity: 11 State: 0

This is documented in Microsoft's article Database Engine Error Severities (Microsoft | SQL Docs) as:

LEVELS OF SEVERITY
Severity level    Description
...
11-16             Indicate errors that can be corrected by the user.
11                Indicates that the given object or entity does not exist.
...

This correlates with this bit of information in the error message:

at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)

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 .

Caution:
The repair process can result in a down-time of the SQL Service instance.
Ensure you have a backup of the databases. This process doesn't normally break databases, but better safe than sorry.

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 <>.

Start with either the INSTANCE_NAME or the Shared Features and click through until the end.
You might have to reboot the server, because of components that have had to be repaired/replaced.
You might have to re-apply Cumulative Updates afterwards.
Repeat for the second option, than what you initially chose in step 1.

Further Actions

If these last options fail, then it might be time to open up an SR with Microsoft.

Good hunting.