Sharepoint 2016 Timer Job job-diagnostics-blocking-query-provider throwing SQL Exception in ULS logs

Question

Hello,

I followed this guide: How to change the database server in a SharePoint farm to switch any database references in sharepoint from on server to a SQL Alias. Everything worked, except a reference from a timer job "job-diagnostics-blocking-query-provider", that is still pointing to the old instance name.

This is what's being logged on ULS every minute..

` at Microsoft.SharePoint.Utilities.SqlSession.get_IsSqlAzure()
at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)
at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior)
at Microsoft.SharePoint.Diagnostics.SPDiagnosticsSqlProvider.CollectData(SqlSession session, String serverName)
at Microsoft.SharePoint.Diagnostics.SPDiagnosticsSqlProvider.Sync()
at Microsoft.SharePoint.Diagnostics.SPDiagnosticsProvider.Execute(Guid targetInstanceId)
at Microsoft.SharePoint.Administration.SPTimerJobInvokeInternal.Invoke(SPJobDefinition jd, Guid targetInstanceId, Boolean isTimerService, Int32& result)
at Microsoft.SharePoint.Administration.SPTimerJobInvoke.Invoke(TimerJobExecuteData& data, Int32& result)

SqlError: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)' Source: '.Net SqlClient Data Provider' Number: -1 State: 0 Class: 20 Procedure: '' LineNumber: 0 Server: ''

ConnectionString: 'Data Source=DB\SQL2012;Initial Catalog=master;Integrated Security=True;Pooling=True;Application Name=SqlSession[OWSTIMER][1][master]' Partition: NULL ConnectionState: Closed ConnectionTimeout: 15 ConnectionClientId:00000000-0000-0000-0000-000000000000`

How can I get this updated with the new SQL Alias?

Thanks!

Answer 1

Hi @CM-0990 ,

According to your description, you may need to update the configuration of this timer job to point to the new SQL Alias. Please try the following steps:

1. Open the SharePoint Management Shell as an admin.
2. Run the following command to get the GUID of the timer job:

Get-SPTimerJob | ?{$_.Name -like "*job-diagnostics-blocking-query-provider*"} | select id

3. Run the following command to get the current settings of the timer job:

$job = Get-SPTimerJob -Identity <GUID>

4. Run the following command to update the settings of the timer job to use the new SQL Alias:

$job.DatabaseServer = "<New SQL Alias>"; $job.Update()

After updating the configuration of the timer job, you may need to restart the SharePoint Timer Service to ensure that the changes take effect.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @CM-0990 ,

I found another possible solution to this issue is to update the timer job's configuration to use the new SQL Alias instead of the old instance name. Please try the following steps:

1. Open the SharePoint Management Shell as an admin.
2. Run the following command to get the timer job object:

   $timerJob = Get-SPTimerJob -Identity "job-diagnostics-blocking-query-provider"

3. Run the following command to update the timer job's properties:

$timerJob.Properties["ConnectionString"] = "Data Source=SQLAlias;Initial Catalog=master;Integrated Security=True;Pooling=True;Application Name=SqlSession[OWSTIMER][1][master]"

Note: Replace "SQLAlias" with the name of your SQL Alias.

4. Run the following command to update the timer job:

$timerJob.Update()

After updating the timer job's properties, it should use the new SQL Alias instead of the old instance name. You can verify this by checking the ULS logs again to see if the error message has disappeared.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.