September 27, 2010 at 10:03 am
We have two SQL servers that are used to build warehouses. Both servers will hang with no notice. We are able to ping the server, but cannot remote connect in anyway. We have to force a shutdown to restore the server to a working state. The sql programming for the build is rather poor and a lot of times SQL's CPU is through the roof, but we can still remote in.
I am trying to get the responsible department to work with the vendor to streamline the programming. In the mean time is there something I can do with the memory, etc(?) to keep SQL from overtaking the OS? At least that is what our network engineer says is happening.
PS: Our SQL and hardware are completely up to date with service packs.
September 27, 2010 at 10:11 am
If you think that the problem is that SQL Server is using to much memory and doesn’t leave enough memory to the OS, then you can limit the server’s memory usage with sp_configure and the option max server memory (MB). Having said that did you check if this is the problem? Did you check SQL Server’s error log and event viewer for errors that that show that memory pressure is the problem? When you couldn’t log into the server did you try using the dedicated admin connection to investigate the problem?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 27, 2010 at 10:14 am
There were no memory errors in the logs or event viewer. However, I did not try the dedicated admin connection.
September 27, 2010 at 11:23 am
Are you able to connect to SQL Server from a remote machine?
If so please check the memory consumption by the SQL Server as Adi Mentioned and try to limit the SQL Server Memory by using the Max memory setting.
BTW are you using AWE or are you seeing any parallelism on the server.
Thank You,
Best Regards,
SQLBuddy
September 27, 2010 at 11:32 am
Physical Server:
OS - Windows 2008, Service Pack 1
System Type - Stand-alone, Terminal Server
CPU - 16 Core 2.67 GHz Intel Xeon(R) X5550
Memory - 32768 MB
SQL: Standard Edition (64-bit), Service Pack 3
All sql server properties set to default settings.
There were no entries in the SQL or Windows logs.
We are not using AWE.
September 27, 2010 at 5:18 pm
What brand of server are you using? Did you install SP3 about the time the lockups started?
If you are running an HP server you are likely running into a conflict between the hardware and a change that SP3 made. During times of heavy I/O the server can lockup. You can look at the HP article on this: http://h20000.www2.hp.com/bizsupport/TechSupport/document.jsp?objectID=c02110402 (I had to make the actual link a tinyurl since the forum software kept converting the D in Document to lower case, which doesn't work on the HP site.)
If that is the case, which happened to me, you just need to turn on trace flag 8038 like the article mentions.
September 28, 2010 at 6:52 am
Thank you. I sent this to our network engineer so he can confirm the server model. However, we are running Server 2008 the article mentions 2003. I'm in the process of researching if this issue has been resolved in Server 2008.
September 28, 2010 at 8:56 am
Try setting the max degree of parallelism to something other than default. I've seen sql server max out all cpu's and cause connection problems.
EXEC sys.sp_configure N'max degree of parallelism', N'8'
GO
RECONFIGURE WITH OVERRIDE
GO
September 29, 2010 at 8:23 am
1) ALWAYS set max memory on a sql server. I would try 26GB to start on this box.
2) Have you tried to connect using the DAC??
3) Did you try to remote desktop into the server and connect using SSMS or sqlcmd locally?
4) was there NOTHING in the sql server error logs or windows logs that could point you in the right direction?
5) I would be on the phone to MS support.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2010 at 9:04 am
bad indexes, statistics can also coz SQL cpu usage to go crazy ...
Did you run a sql trace to confirm that it is the bad sql statements in the program that is brining the servers to a hault?
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
October 4, 2010 at 5:34 pm
Erin-489205 (9/28/2010)
Thank you. I sent this to our network engineer so he can confirm the server model. However, we are running Server 2008 the article mentions 2003. I'm in the process of researching if this issue has been resolved in Server 2008.
Have you had any chance to find out if the trace flag would apply to Server 2008?
How often is/was the server hanging?
October 5, 2010 at 7:11 am
I have not found anything on Server 2008. I applied the trace flag and now am wait and see. The server would hang randomly. We haven't found anything other than the article that would be causing this.
November 5, 2010 at 6:07 pm
Just curious if you have an update on if the trace flag fixed the problem or if you still have random hangs.
November 8, 2010 at 5:55 am
It has not hung since.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply