SQL Server FTS Timeout Error in ASP.Net, after application is idle for some time

Question

Hi,

We have a page calling a Search stored procedure which internally uses SQL Server 2005 FTS. If the application remains idle for 10-15 mins, the first consequent call to the stored procedure times out. All subsequent calls work.

Is there any way to prevent this from happening? We don't want the first user getting a timeout and we also don't want to change our code.

Kind regards,

Answer 1

I would be surprised if a stored procedure running on the database server were aware of the idle time of your web application. Stranger things have happened (though not lately).

I suggest you test this "in the raw". In SSMS, run the stored procedure, wait 15 minutes, then run it again. See if the SP fails.

Other questions would be: are you properly calling Dispose on the SqlConnection? Are you using impersonation? Has your connection string been changed to address connection pooling? How is the SP being used? Filling a DataSet? Data Source Control? If the latter, is caching enabled?

Answer 2

It seems that the Full Text catalog for a database is unloaded by SQL Server if its not used for some time (10-15 mins in our case).
The only solutions which seem possible are:
1. Run a warmup job to ensure that the index is not dumped from memory. Could be done either at SQL or at ASP.Net.
2. Increase the timeout for your method calling the full text search stored procedure to a higher value.

Answer 3

I had a similar problem, well the fts timeout is similar, the rest is different (I'm not using a stored proc or a web page), not that I think it matters. The very first search query of the day would fail with a timeout error and then subsequent queries would succeed.

I stumbled on this link on reddit today: http://support.microsoft.com/?scid=kb%3Ben-us%3B915850&x=15&y=8.

Basically, if your SQL Server doesn't have access to the internet you can get a timeout. Running the command (below) found at that link totally solved the problem for me.

sp_fulltext_service 'verify_signature', 0; GO

You'll want to read the full article before you decide to do this on your server.

Hope this helps.