Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tonymanjaly
Starting Member
2 Posts |
 Posted - 2010-06-02 : 06:43:59
|
| I run around 40 websites on a server with IIS and SQL Server 2005. Everything was fine when I had only few sites. However, when the number of sites reached 20+, I started getting issues with SQL Server.After I do a server restart or IIS restart, the sites with low traffic fails to connect to SQL Server any more. It simply gives this message:Exception information: Exception type: SqlException Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. When I look at the database server, I can see the databases are in recovery state, changing from one to another database. (I haven't noticed this always, may be missed to see)The good thing is, the high traffic sites (with few hundred thousand views per day) have no problems. They connect to SQL Server normally after server restart or IIS restart. Only small sites which are rarely visited by people have this problem.So far I never thought this is a problem with SQL Server and I was always debugging on the IIS side.But today I noticed that the databases are in recovery state one by one. Also, since all the big sites are connecting smoothly, I think this is happening because the big ones are grabbing all the SQL Server resources (connections ?) quickly and small ones are not able to get an opportunity.Here is the connection string I used:Data Source=(local)\sql2005;Persist Security Info=True;Integrated security=true;Initial Catalog=XXXXX;I have been trying to figure out a solution for this for last few months. Any suggestions? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-06-02 : 10:33:53
|
| So does applications and Database Exist in Same Server? |
 |
|
|
tonymanjaly
Starting Member
2 Posts |
Posted - 2010-06-02 : 11:45:38
|
| Yes, the IIS websites and databases exist on the same server.Some of the small sites usually get connected by itself after 10-20 mnts. Sometimes, it takes hours and sometimes I had to go to the extend of detaching database and attach again to get the site connected to the database. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-06-03 : 19:08:04
|
| You can determine if you have an upper limit on the number of connections by running: exec sp_configure 'user connections'Note that this is an "Advanced" option so you may need to run this first:exec sp_configure 'show advanced options', 1reconfigureBy default there is no connection limit but the configuration might have changed.Another item to consider is the number of available threads. Run this to see what your limit is:exec sp_configure 'max worker threads'And run this to see how many you are currently using:select sum(current_workers_count) Counts, 'NumThreads' Objectfrom sys.dm_os_schedulerswhere scheduler_id < 255 -- 255+ ==> InternalIf the applications are sharing the same database, then you might be running into a connection pooling issue. You can modify your connection string to explicitly set the number of connections in the pool. It doesn't sound like this is the issue but it might be worth a try.Or, you might be leaking connections...=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
SQL Server connection fails after a server restart