Posted - 10/11/2012 : 05:30:10
firstly some introduction to our software:
we have website, written in ASP.NET, hosted in ISS on Windows Server 2008 R2, web edition, as our storage we use MS SQL 2008 R2, Web Edition.
we are getting timeout expired exceptions, when executing sp that search on fulltext indexed columns.
On our sql we have mutlible databases. Each database have spSearch (which is same on each db). This spSearch searches with CONTAINS/CONTAINSTABLE on data, that are full-text indexed. Normaly this spSearch takes about 1-3 seconds executing.
But when we execute this spSearch throught our web site, using ADO.NET
executeStoredProcedure, from beggining everything works great. Results are return normaly, in 1-3 seconds.
But after some random time (or some number of calling spSearch???) from our website, we start to get timeout expired exception. If one user gets this exception, on database, in which this user is, all users gets this error from now on. If we execute this spSearch in management studio on this db, it executes normaly, in 1-3 seconds, without any timeout error. If some user executes this sp on other db, it executes normaly.
What seems to help is to rebuild fulltext catalog on the db, if we do this, all users from our site can now search with spSearch without any problem. But after some time, everything starts again.
The biggeest mystery is, that how it is possible, that from our site we are unable to search, but from management studio it executes right away at the same time?
We are out of solutions, so we appreciate any help.
Thanks and regards