May 13, 2009 at 3:05 am
Hi All,
I have already set the timeout executions, Indexing, Scans and i found there are number of queries running for Highest CPU utilization....
Still am facing the same errors with : as Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Is there anyother way to set any areas : Other than SQL Server connection settings , Query execution timout etc...
I could not find if the connection timeout to be set to so and so.. i have seen many number of articles, they say about changing the command connection timeout to 200.
Can any one please clarify these things....
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 13, 2009 at 6:30 am
Are both SQL server and SSAS running on the same machine?
We run both on the same machine, but memory took awhile to balance out.
We had to set limits on both SQL and SSAS.
Take a look at books online for memory.
You also might want to run profiler as one of the queries that's timing out is running.
Large cross joins are a problem on 2005. Are you using non empty in your select statements?
Greg E
May 13, 2009 at 9:03 am
Are both SQL server and SSAS running on the same machine?
We run both on the same machine, but memory took awhile to balance out.
We had to set limits on both SQL and SSAS.
Take a look at books online for memory.
You also might want to run profiler as one of the queries that's timing out is running.
Large cross joins are a problem on 2005. Are you using non empty in your select statements?
Greg E
------------------------------------------------------------------------------
Yes both are running on a single server....
I have already checked for memory usage.. Need to run profiler on the server....
Still there are many TimedOut errors ....
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 13, 2009 at 1:16 pm
SQL_Monster (5/11/2009)
Thanks for the reply,Actually we are facing the issues like : TECH_MESSAGES like,
1. Object reference not set to an instance of an object.
2. Save Profile Failed Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
3. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
4. The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.
5. The operation has timed out
As per the above errors Server is not responding for completing the process.
My aplication is in .NET and we are facing these timedout errors in 50+ TO 70+ NUMBER daily....
And my server CPU Utilization is reaching 100% many times... At that point of tno connections will be made right ????
Machine is :
64 BIT SQL Server 2005 on WIN 2003 64BIT ;
12 GB RAM - of which SQL Server is using 11.1 GB at any point of time (as 10GB - SQL and 2GB - CPU)-
Still am unable to understand 64 Bit will support more than 500+ connections (i hope so). If i can reduce (fix) these errors then am going to be a good DBA in my Org. Please put your inputs to keep these issues fixed.....
Can any one has any ideas .. please let me know if anything else to be provided for clarification....
Fast help is Appreciated........
From all the errors you have written, you will have to put a net for the connections taking too long to finish. They look like a combination of poorly written .net code accessing probably stored procedures that are too long to finish. There is a timeout default in .net, around 2 minutes, I think. You should change the type of connection you use in the .net application to be able to timeout the query just before the SQL server times out the connection. This is what we do in our application, and everything works great now.
Before we did that, we had a timeout coming back from the SQL Server, because there has some inactivity from more then, let's say 2 minutes, and the client trapped the error, and showed the User, Timeout. But, the timeout was only on the client, and the SQL Server kept on running the long query, (we had some really long processes). The user who got a timeout directly launched the screen again, and tried to query the same thing again, hoping it would work, but the first query never ended, so the server was even slower. And we had a timeout mania on our production server.
So from what I tell you, you should read a little on the subject, and be able to close the connection before the .net application times out.
I.E. if the connection will timeout after, let's say, 200 secs, simply set the query timeout to 195 secs, that will always end the query on the server before it actually times out.
Also, for your connections, I do not know how the application is written, but you should ensure every connection is closed before you exit the system. So whenever you query the Database, ensure you open, and then CLOSE the connection. That should make less sleeping SPIDs on the SQL server, and probably fix your problem.
Hope that helps,
Cheers,
J-F
May 13, 2009 at 3:24 pm
If you are able to reboot the system and start out clean (which if this is production could be an issue), and then check the memory levels before any queries are run, you will have a baseline.
Pay attention to max memory being used by SQL and SSAS. We have found SSAS, if it gets to the point where it pushes memory out of the SQL process, things can go downhill fast.
Here's a link to a document for SSAS performance tuning.
Handling connections is important. But part of your problems sound like queries that take too long are making things worse.
So you have plenty of things to look at. And if you have a development environment to test with, use it.
Greg E
May 18, 2009 at 12:15 am
Dear All,
According to J-F am done with the changes like improved CPU RAM for 5Gb as it was only 2GB earlier.
Need to change the equery timeout and the application connection timeout settings. Where can these be changed.??
- Info :
We are running .Net application for this 64bit SQL Server and we have 3 application servers whicha are in clustered. From the appl servers they point to production db server where we are getting these errors.
This Timeout is for both of our production servers OLAP and OLTP. OLTP has many issues as TIMEDOUT.
Please help me to resolve this OLTP first to be fixed...... Fast help is appreciated.....
~ Thanks.
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 18, 2009 at 2:00 am
SQL_Monster (5/18/2009)
Dear All,According to J-F am done with the changes like improved CPU RAM for 5Gb as it was only 2GB earlier.
Need to change the equery timeout and the application connection timeout settings. Where can these be changed.??
- Info :
We are running .Net application for this 64bit SQL Server and we have 3 application servers whicha are in clustered. From the appl servers they point to production db server where we are getting these errors.
This Timeout is for both of our production servers OLAP and OLTP. OLTP has many issues as TIMEDOUT.
Please help me to resolve this OLTP first to be fixed...... Fast help is appreciated.....
~ Thanks.
Fast help: Read the manual for the specific technology you are using to connect using .NET. ADO? Adapters? COM? Read the relevant manual.
May 18, 2009 at 3:06 am
Thanks for the fast reply Hans.
Can that be in a specific or described.
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 18, 2009 at 3:13 am
I would first look into How it is connecting, what is Actually giving the timeout and where in the code. On a lot of connections there are Query timeouts, connection timeouts and Command timeouts. You need to know which one it is to increase the correct one.
It seems you should be talking to the DEVs of the application...
May 18, 2009 at 6:19 am
there are 5 - 6 long running queries to get the data from couple of servers. Trying to tune them fine.
and i have already set the query timeout to
in the web.config file.....
session state as :
give me suggestions , is there any other way to get on...
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 18, 2009 at 7:04 am
It looks like your XML from the web.config didn't make it, possibly because you didn't tag it inside of blocks correctly.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
May 18, 2009 at 9:27 pm
Timeout set :
sessionState mode="InProc" cookieless="false" timeout="60"
httpRuntime maxRequestLength="2097151" executionTimeout="900"
customErrors mode="RemoteOnly" defaultRedirect="UnexpectedError.htm"
as in the web.config file...
Any help on this....
There is huge increase in TIMEOUT errors day by day...
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 20, 2009 at 12:32 am
Any Inputs on this...... Please post if any one has any thoughts..........
Cheers,
- Win
"Dont Judge a Book by its Cover"
Viewing 13 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply