Frequent Timeout Expired Sql Exceptions

Question

Hi

I Googled this problem everywhere but to find no proper solution, Now coming to my problem

am working on a webapplication using vs2008 and sql server 2008 in c# with LINQ and asp.net 

the project has gone live  and my database size is already 3.5 GB.. now this applications gives random timeout expired exceptions everywhere in the application. my web config has command timeout of 1000 also tried increasing it to 3000 and more but with no result.

please help me solving this issue, as this project is live I need urgent help.. the exact error am getting everywhere is 

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Answer 1

Hi,

You will get this error due to many reason, U can try some of the below step, whether its work it out or not.

* Check TCP/IP is enabled or not thru SQL Server Configuration manager.

* Check whether you pass the instance name correctly or not.

* Try to ping the client machine to verify no network related issue.

---

RM Thirunavukkarasu | | http://thiruna.blog.com/ |

Answer 2

Hello,

Please, don't put the text of the error message with letters having  so  a big size . It is really aggressive.

Please, could you provide the full version ( 2008 + last installed service pack ) and the edition ( Express , Web , Workgroup, Standard,...) for your SQL Server ?

Please, as you are using a Web application, could you provide the full and exact connection string which is used to connect to your SQL Server ? In a web application, you should find it in the Web.Config file ( as I am not using ASP.Net, I am not sure whether it is the correct file name ) .If you are using the SQL Server authentication  , don't forget to replace the password and the login by **** ( question of security ).

Are you using IIS to manage your website ? If yes, which is the exact version ( 5,6,7.0,7.5,...) ?

Please, could tell us which is the size of memory reserved to SQL Server and an approximate number of connections to your SQL Server instance ?

You may have many possibilities to explain why a Web application cannot connect to a SQL Server database or is throwing a command timeout. Please, could you tell us whether it occurs for read or write operations ?

See for example :http://forums.asp.net/t/903456.aspx/5/10

If the timeout is occurring for not SELECT queries ( so update , insert or delete ) , you may have a problem with your transaction log or data file of your database. Except for TRUNCATE statements, the update/delete/insert operations store the "modified" rows in the transaction log file ( with the extension .ldf ) and treated by a specific thread not immediately : until the modification operation and delete/write operations are completed : a possible explanation for the timeout . If the transaction log or data file is full, there is an increase of the size of the file, which is a lengthy ( in time ) Windows operation and the time necessary for this increase is counting in the command timeout , a little problem ( too often under-estimated ) which may explain your timeout.

We are waiting for your feedback to try to help you more efficiently.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Are you using Blob columns by any chance ? If so then the time out  is  happening while selecting and modifying data from that table. I faced similar problem before. If you could tell me more details about the data stored in DB and number of parallel threads running on it, I can help you in fixing it.

Waiting for your reply..

Answer 4

Hi Papy,

          Thanks for your concern as far as the problem, am using SQL Server 2008 standard edition with service pack 3

and the connection string goes like this 

<connectionStrings>

<add name="myConectionStrng" connectionstring = "Data Source=user-pc;Initial Catalog=my_Live_Database; Persist Security Info = true;

User ID=sa;Password=****** ;" providerName="System.Data.Sqlclient"/>

</connectionStrings>

the version of IIS is 7.5

the size of memory reserved is 10GB and approximately there are 100 connections to my instance

 the error is for both read and write commands mostly for write of-course

I need to know if the problem is with the code or the database design (stored procedures importantly)

Actually, am managing already developed web app so i couldn't really figure out the problem.

the people who developed the app have used cursors extensively in sporcs, can this be a problem??

sometimes the sprcs take long long time to execute in ssms sometimes the same sproc takes less than a second, am irritated by this behavior please give some reasons for such behavior..

please Help!

Answer 5

Hello,

I am far to be a specialist about Web applications and IIS. But I will try to help you or at least to "restart" this thread

I suppose that you are not using IIS 7.5 Express.

I have found this little article about the application pool recycle interval :

http://weblogs.asp.net/owscott/archive/2013/04/06/why-is-the-iis-default-app-pool-recycle-set-to-1740-minutes.aspx

There are some explanations about Idle Time-Out property values ( possibly useful for you )

100 connections for 10 GB reserved memory is "normal". If my memory is not too bad , it seems that each connection needs around 10-20 KB of memory, but it is not the same thing for SELECT or INSERT commands especially if they are related to several rows.

About the use of cursors , I don't like them ( so I don't use them ).See :

http://stackoverflow.com/questions/3557243/convert-plsql-cursor-for-loop-syntax-to-tsql

http://msdn.microsoft.com/en-us/library/ms180169(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/ms191179(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/ms188644(v=sql.100).aspx

Moreover , it seems that cursors are using more place in the TempDB database ( with the risk of enough room or continuous size increase, which is a Windows operation of the necessary time is included in the Time-Out associated to the command , especially if the Model database from which TempDb is "copied" has too small size/increase size values ). TempDb is recreated when the SQL Service is restarted and cannot be truncated/cleaned except by a SQL Server restart. It is a shortcut explanation but the idea is correct even if I am betrayed by my poor written English.

For your execution variation time of stored procedures , have you tried to use SQL Profiler to see what is happening ?

A moderator can move your thread towards a forum more related to performance problems ( considering me , I prefer to wait for the agreement of the original poster before doing the move ).

A little remark : for your memory 10 GB for a 3.5 GB database is good. Maybe a problem of too many size increases (.as I have already written , the time necessary to increase the size of a database (for the  .mdf,.ndf,.ldf files ) is an operation managed by Windows and it is a costly operation of the necessary time is counting in the Command Time-Out . With SSMS , you can see the values for every database for the allocated size , the increase size :

In the Objects Explorer , right-click on the name of the database and select Properties. The interesting values are

- for the General Page : the Size and Space available values ( + unit KB/MB,... )

- for the Files Page : initial size , autogrowth,maxsize  for each type of files ( .mdf,.ndf,.ldf )

Another point : are the drives on which the SQL Server files are installed really well defragmented ? It could explain why you have more problems with Insert than SELECT

It is possible that I have not understood in a full way or I missed something in your last post. I hope you will excuse. Don't hesitate to post again for more help or explanations  or to correct me in case of misunderstanding from me.

Have a nice day

PS I have seen that the value for Data Source is user-pc. Are you using a default SQL Server instance ( with no name ) or is it a shortcut for MyComputerName\MyDatabaseName ?

We would be curious to see the T-SQL text of your stored procedures needing 1 second or 1000 seconds. It is not normal ( if it is for the same values of the parameters )

 

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.