Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cluster resource 'SQL Server' failed Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 11:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:00 AM
Points: 74, Visits: 319
While troubleshooting why a query failed on our SQL Server 2005 cluster, I note in the SQL Server log that SQL Server seems to have shutdown and restarted.

Looking at the System Event Viewer, I see that "Cluster resource 'SQL Server' in Resource Group 'Group 0' failed"

Prior to this, there are hundreds of entries about "There is insufficient memory to run this query."

How can I find out more? What can the root of the problem be?



Post #1567649
Posted Monday, May 5, 2014 12:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
Is this a SQL Server 2005 or 2008?
I'm asking because you posted in the 2008 forums but you said it's a 2005 instance.

However, if you have many insufficient memory errors, you probably have run out of memory.
Which operating system is it running on?
If it's windows 2008, you should have an entry in your event viewer for resource exhaustion, to confirm this diagnosis.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1567671
Posted Monday, May 5, 2014 1:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:00 AM
Points: 74, Visits: 319
It is SQL Server 2005 on Windows 2003 R2.

Sorry if I posted in the wrong place - I did not see a SQL 2005 High Availability topic, so I thought that it made the most sense to post here.



Post #1567686
Posted Monday, May 5, 2014 2:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
Ok, so no resource exhaustion event.
You should monitor the memory counters in Windows and see if they get too low.
If this is a SQL Server only box, you could lower the Maximum Server Memory configuration parameter.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1567705
Posted Tuesday, May 6, 2014 1:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:00 AM
Points: 74, Visits: 319
spaghettidba (5/5/2014)
Ok, so no resource exhaustion event.
You should monitor the memory counters in Windows and see if they get too low.
If this is a SQL Server only box, you could lower the Maximum Server Memory configuration parameter.

Both hosts in the cluster have 16 GB RAM - with AWE enabled.
I have the maximum server memory set to 13500.
Post #1568148
Posted Tuesday, May 6, 2014 3:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
2,5 GB for the OS might not be enough.
Also consider that you're controlling just single page allocations with Max Server Memory. All other allocations are not included in that figure.
See here for details: http://blogs.msdn.com/b/sqlosteam/archive/2012/07/12/memory-manager-configuration-changes-in-sql-server-2012.aspx


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1568215
Posted Wednesday, May 7, 2014 3:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 6,194, Visits: 13,349
inevercheckthis2002 (5/6/2014)
spaghettidba (5/5/2014)
Ok, so no resource exhaustion event.
You should monitor the memory counters in Windows and see if they get too low.
If this is a SQL Server only box, you could lower the Maximum Server Memory configuration parameter.

Both hosts in the cluster have 16 GB RAM - with AWE enabled.
I have the maximum server memory set to 13500.

I would err on the side of caution and allocate less then ramp up, not the other way around. Try setting max mem to 10 or 11Gb initially.
Is this 64 bit or 32bit OS\SQL?
Do you have LPIM privilege granted to the sql server service account?


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1568346
Posted Wednesday, June 11, 2014 6:00 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:45 AM
Points: 317, Visits: 823
Try the default trace to see what ran prior to your reboot.

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/
Post #1579860
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse