1

I have 2 Nodes of SQL Cluster running SQL 2012 Ent over Windows 2012 R2 and i don't know suddenly what happened the service is not coming online. Under Failover Cluster Mannager > Roles > SQL > Resource > SQL Server (instance-name) the status us Online Pending and then Failed.

Cluster have only below evetns;

  • The Cluster service failed to bring clustered role 'SQL Server (instance-name)' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.
  • Cluster resource 'SQL Server (instance-name)' of type 'SQL Server' in clustered role 'SQL Server (instance-name)' failed. Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it. Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Event Viewwer;

  • Fault bucket , type 0 Event Name: Failover clustering resource deadlock Response: Not available Cab Id: 0

Problem signature: P1: SQL Server (instance-name) P2: SQL Server P3: ONLINERESOURCE P4: P5: P6: P7: P8: P9: P10: Attached files: These files may be available here: C:\ProgramData\Microsoft\Windows\WER\ReportQueue\Critical_SQL Server (instance-name)_217169f34c33c838a6522b285697633e938ebe_00000000_03a6e263 Analysis symbol: Rechecking for solution: 0 Report Id: 11f7e038-5f26-11e7-80fb-7a2f1397e813 Report Status: 4100 Hashed bucket:

Kindly suggest what could be wrong.

  • 1
    What does the SQL Server errorlog say? Anything else in the clusterlog before the failed to bring online message? – Sean Gallardy Jul 2 '17 at 13:19
  • The error log is about 13 gb and on shared disk which i think will take long enough to open. I check the extended logs and i found this "[sqsrvres] Failed to start service with error 1460. Please try again" and follow with "[sqsrvres] SQL Server resource state is changed from 'ClusterResourceOnlinePending' to 'ClusterResourceFailed'". – Danish Jul 2 '17 at 13:37
  • You'll need to look at the SQL Server errorlog... which since it is failing to start shouldn't be 13 gb (you only need to look at the newest one). 1460 means the service failed to start in the timeout period. – Sean Gallardy Jul 2 '17 at 13:39
  • You are talking about the file "ERRORLOG" which i have in my shared disk. Or any other sql log file in C:/ location ? – Danish Jul 2 '17 at 13:53
  • You can generate cluster log only for the time slice when issue happened. Did you this article? – SqlWorldWide Jul 2 '17 at 14:46
0

The default restart settings in WSFC are such that a faulting resource is terminated and then retried once before being declared offline.

In this case: the cluster discovers the fault, calls for terminate and then attempts to re-online. Storage Foundation for Windows detects that the Disk Group is not present and returns a status of Pending. This creates a deadlock that lasts until either RHS.exe terminates (through hang protection); or the online pending timeout is reached.

So, recommend to change Pending timeout and deadlock timeout as per below link.

To change deadlock timeout(Values are in milliseconds)

(Get-ClusterResource “Resource Name”).DeadlockTimeout = 300000

To change pending timeout follow below steps:

  1. In the Failover Cluster Management snap-in, if the cluster you want to configure is not displayed, in the console tree, right-click Failover Cluster Management, click Manage a Cluster, and select or specify the cluster you want.
  2. If the console tree is collapsed, expand the tree under the cluster that you want to configure.
  3. Expand Services and Applications.
  4. Click the clustered service or application that you want to configure the pending timeout for.
  5. In the center pane, right-click the resource for the service or application, click Properties, and then click the Policies tab.
  6. Under Pending timeout, specify the length of time, in minutes and seconds, that the resource can take to change states between Online and Offline before the Cluster service puts the resource in the Failed state.

The default timeout value is 3 minutes. Change as per requirement.

  • i applied the deadlock to 300000 millisecond and pending timeout to be 5 minuets. Still offline. – Danish Jul 2 '17 at 19:36
  • Change deadlock to 500000 millisecond and pending timeout to 1000000 millisecond – SER Jul 8 '17 at 10:03

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.