TIMEOUT error (client thinks it's SQL Server)

Question

Post reply

TIMEOUT error (client thinks it's SQL Server)

Viewing 15 posts - 16 through 30 (of 40 total)

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

JMI SSChampion Points: 11831 More actions · Answer 1

I am using Profiler Standard template.

I do not see RPC starting.

Where do I get it.

It is not just SPs.

It might be anything.

I do not know their code.

I just started looking into their problem yesterday

and looks like it is third party software written in .NET

and they do not know much about it...

VM-723206 SSCrazy Points: 2964 More actions · Answer 2

In the "Events Selection" tab of the "Trace Properties" window, right click on Stored Procedures and click on 'Select Event Category'. You can find the "RPC:Starting" you are looking for.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

riga1966 (4/28/2009)
I did.
I started two traces yesterday.
But apparently one of them caused disk space issues on C: drive on production server. (Oops!)

Don't use the profiler GUI, use a server-side trace. Much less impact and it saves where you tell it to save.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

Oops edited as it was bad suggestion dint realize there was a second page to this 😀

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 5

there are two things that you can try at the client level, first is "Connection Timeout=n" property of the connection string in the client application, second is "CommandTimeout=n" property of the command object itself in the client application (assuming its a .Net application).

Here n= number of seconds.

Hopefully setting the commandTimeout property will fix the issue, although you should still try optimizing the queries.

Thanks,

Usman

SQLRocker SSCrazy Points: 2217 More actions · Answer 6

Run the profiler from your local computer only not from the server.

When you ran a SQL Profiler trace by using RDP on the server itsself, the tmp files are stored in "C:\Documents and Settings\yourusername\Local Settings\Temp" as Prf1A.tmp & so on, Most Prod. servers don't have a lot of free space on the C:\, so be careful. After you stop & exit the profiler , the tmp files vanish. So, one workaround I think is to stop & start the profiler , while keeping an eye on the disk space.

--

Ofcourse, take care of the regular steps like, run profiler only when absoultely required on Prod servers, use selective events, ran for a short period of time, Try not using GUI, script it & run from a sp & so on.

JMI SSChampion Points: 11831 More actions · Answer 7

Here is an error log:

Error Output: 4/27/2009 9:38:25 AM, User: Bianca Oran

System.ApplicationException: Can not create new flag. ---> System.ApplicationException: Not able to GETENTITYBYID with id: 80603 of entities of type: DMDS.Shared.ContactList ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Does it look like SQL problem?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

It just means that the query that the client app submitted did not complete in the allowed time (default 30 sec). It may be that the query needs optimising or it may be that the command timeout needs increasing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JMI SSChampion Points: 11831 More actions · Answer 9

Apparently the are running some daily jobs (every hour).

Report Server is running on the same box

and they suspect it might cause issues as well.

Should I maybe start Performance Counters to see

when SQL Server becomes very busy?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Take a look at the two article I posted earlier in the thread. They're a series that shows an example of finding and fixing perf problems (though in the case of the article, they were all indexing problems)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

developer6 Right there with Babe Points: 717 More actions · Answer 11

Hi Gail -- Could you please explain how 30 seconds is the default timeout for a .NET application. By default I assume you mean when a timeout setting isn't specified.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

From that page:

The time in seconds to wait for the command to execute. The default is 30 seconds.

So if you don't specify a command timeout, it's set at 30 sec.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

developer6 Right there with Babe Points: 717 More actions · Answer 13

Okey doke, I see what you mean. However, I'm not aware that the same default applies to, for example, the "Connection Timeout" property of a connection string. Setting this value to 0, I believe, is effectively no timeout.

Typically, in the majority of the .NET applications that I've come across, the application's connection strings are stored in an XML configuration file so changing one of these wouldn't constitute a code change.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Connection timeout and command timeout are not the same thing.

Connection timeout is the amount of time that the app will wait to establish a connection. This can be set using either in code or using the Connection Timeout keyword in the connection string. It only affects the initial creation of the connection, not queries that are run over that connection.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx

From that page (emphasis mine):

The time (in seconds) to wait for a connection to open. The default value is 15 seconds.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

developer6 Right there with Babe Points: 717 More actions · Answer 15

Correct, they are not the same thing. Having looked back at the error message from the log it is apparent that any timeout issue, in this instance, is not connection related, i.e. you appear to be correct in your analysis and conclusion.

I wouldn't consider it unusual if a command timeout was also stored in an XML configuration file rather than being hard-coded.