SharePoint crashing SQL all the time

Question

SharePoint crashing SQL all the time

Kris-155042

Hall of Fame

Points: 3460
More actions
May 15, 2016 at 6:11 pm

#316548

We have a server on 2012 R2 Windows and 2014 SP1 Enterprise SQL. It seems to run out of memory even though when you watch the task manager it doesn't appear to be running out of memory at all. When running a standard db integrity check it crashes and when doing a standard SharePoint crawl it crashes. It has plenty of hard disk space and has 72Gb RAM. Maximum server memory (in MB) was set to 6000. This has been changed to 2147483647 and we are still having the issue.
Thanks,
Kris

Viewing 5 posts - 1 through 4 (of 4 total)

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

How do you know it's running out of memory and what is crashing? The server, just SQL? Do you have SharePoint and SQL running on the same box?

Just a couple thoughts...

1. Check your server event viewer (app and server) and your SQL error log.

2. If you need to run an integrity check (assuming you are talking about DBCC CHECKDB) while you are sorting this out, you can add WITH TABLOCK. DBCC CHECKDB makes a snapshot of the DB and checks that. Using WITH TABLOCK skips the snapshot and the check is against the actual DB. Making a snapshot requires memory.

Again, I suspect that the event viewer and error logs will she'd some light on this.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 2

SharePoint has it's own Web server from the SQL server. In the event log it says "There is insufficient system memory in resource pool 'default' to run this query." and then it crashes SQL. An Integrity check doesn't need to running. It crashes when doing a SharePoint crawl or any SQL maintenance.

Thanks,

Kris

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

When you say "it crashes", what is crashing? The entire instance of SQL Server? The query? The process the application started?

If possible can you run the following and retry the operation:

ALTER RESOURCE GOVERNOR RESET STATISTICS;

If that works you can refer to the following for more info.

http://henkvandervalk.com/sql2014ee-insufficient-system-memory-message

Joie Andrew
"Since 1982"

Lowell SSC Guru Points: 323506 More actions · Answer 4

yeah i'm having trouble with the definition of Crashing Sql server;

I'd expect a web page not load with an error, or time out, but saying "crashing Sql Server" means what toy uoi? For me, it implies SQL locking up(deadlocks or blocking?) or actually killing the service and bluescreening the server?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!