Cluster resource 'SQL Server' failed

Question

Post reply

Cluster resource 'SQL Server' failed

inevercheckthis2002

SSCommitted

Points: 1819
More actions
May 5, 2014 at 11:49 am

#285208

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

inevercheckthis2002 SSCommitted Points: 1819 More actions · Answer 2

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.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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

inevercheckthis2002 SSCommitted Points: 1819 More actions · Answer 4

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.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

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" 😉

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

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/">

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

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