1

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. enter image description here

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.

enter image description here

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)

6
  • When the schedule for one of your jobs hits the scheduled datetime, does the job attempt to run and errors out, or it shows no indication of even trying to run? What is the verbose error message you see when you manually run a job, then look at it's Job History and click on the failed step (in the "Selected row details" window)?
    – J.D.
    Jan 15 at 23:14
  • The scheduled job does not attempt to run. When this started a nightly job run stopped because of a "stop the job request". Since then no jobs will run on a schedule or if triggered manually. I don't see anything in the job history after triggering the job manually. When I trigger manually, the job starts successfully on the Execute the stored procedure step in fails; I don't see anything telling in the long message that comes up. I also created a simple screen print job and get the same severe error. Jan 19 at 15:02
  • Unfortunately it sounds like you have a much bigger problem at hand if Job History isn't logging your manual runs of the job either. You can check the SQL Server Error Log for any errors around the time your manual job run fails, or going back to when this issue initially started. But this might be worthy of a re-install and/or reaching out to Microsoft for troubleshooting (fortunately you're on the latest version of SQL Server).
    – J.D.
    Jan 19 at 15:04
  • If you find any concrete errors related to your issue, add them to your post and feel free to ping me.
    – J.D.
    Jan 19 at 15:06
  • 1
    I posted all that I could get. Thank you for your time. Jan 20 at 0:02
0

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.

SQL Server 2019 Configuration Manager

...and change the Account Name for the SQL Serer Agent (INSTANCE_NAME) service to one of the built-in accounts:

Properties of SQL Server Agent (INSTANCE_NAME)

When done, click Apply and you will be prompted that the service has to be restarted. Click on OK.

Service Restart Prompt

When the service is back up and running, revert your changes back to the previous account:

Properties of SQL Server Agent (INSTANCE_NAME)

And apply. You will receive the same prompt that the service has to be restarted:

Service Restart Prompt

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.

SQL Server 2019 Installation Center (64-Bit)

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

Repair SQL Server 2019 Dialog

  1. Start with either the INSTANCE_NAME or the Shared Features and click through until the end.
  2. You might have to reboot the server, because of components that have had to be repaired/replaced.
  3. You might have to re-apply Cumulative Updates afterwards.
  4. 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.

1
  • Thanks! Determined that the reboot is referencing a domain controller that doesn't exist anymore so the accounts don't have access. I'll push through the steps to fix. Jan 21 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.