2

I usually create queries using Visual Foxpro 9.0 from a desktop computer to a MS SQL Server 2005 running on Windows Enterprise Server (2003). My queries are repetitive and sometimes, one session would query the SQL server 200,000 times in rapid succession. For the first few days, my program is running fairy well but then suddenly the SQL Server is refusing to process the queries.

On checking the logs in the SQL Server, I found out that a certain spid is killing my query process:

Process Id 71 was killed by Hostname SQLSERVER, Host Process ID xxxx.

I am confused because the hostname is not the SQL Server I am querying but another SQL Server.

From the activity monitor I noticed that the offending Process ID has the following information:

62   NO   sa   master   sleeping    0   AWAITING COMMAND   Sql Agent - Generic Refresher ..

The details shows a message:

SET NO_BROWSETABLE ON

Lastly, thinking that it may be a problem with visual FoxPro, I tried running queries from the desktop machine from within SQL Server Management studio, I still encountered the same problem when I tried queries to the SQL Server:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

It's mind boggling because I am able to establish connections but my queries are refused by the server. Again, thinking that my desktop is the problem, I tried running the queries from another computer. It was running at first then suddenly the same problems occurred. Now these two desktops are refused by the server.

I am thinking that this might be a security but I don't know how to resolve this problem.

We also tried to restore our database backups to another host, the same scenario happened... Fine at first then the problem appeared after a few query sessions.

The following are the ODBC SQL Server Drivers:

  • Microsoft Ole DB Provider for Sql Server
  • Sql Server Native Client 10.0
  • Sql Server Native Client 11.0

I have search the web for a possible solution and I tried the suggested solutions, spid blocking, chimney offloading, rss, among others but to no avail. Please, In need your expert advise!

Thank you in advance.

gofr1
  • 15,741
  • 11
  • 42
  • 52
Jojo del Rosario
  • 21
  • 3
  • Possibly you have a SQL Agent job that is killing your connections. You need to check the history of jobs run to see which is the likely culprit (must had been running at the time the KILL occured). – Remus Rusanu Jun 05 '12 at 09:00
  • The process id that is killing the query is the process I mentioned above. I tried to kill it but it remains in the server. it appears inactive but it sure is killing my processes. – Jojo del Rosario Jun 05 '12 at 12:35
  • I think the process you're talking about is just a SQL Agent idle connection. It may have already run the job that killed your connections. Killing the connection will only cause the agent to open a new connection. You need to check the job history. Besides, it can be just a coincidence: the agent took spid 62 which was freed by the true killer *after the murder occured*. – Remus Rusanu Jun 05 '12 at 12:40
  • Are you connecting with a login that has sysadmin rights? Are you on a large network where your 200,000 queries might be interfering with someone else's work, causing them to put something in place to shut you down manually? – Russell Fox Jun 05 '12 at 13:22
  • I am connecting as SA to the sql server. The queries I am running are designed not to interfere with others. It is actually iterative that, say, it goes through a list of costumers and queries the server for the their transaction histories. I designed such queries because if I run it in a single query, it will surely interfere with others because the job will be slow and would interfere with our real time collection. Even without running such queries, I just connect to the sql server and run a query like "select top 5 * from dbo.table", it will return the error and is killed by the server. – Jojo del Rosario Jun 05 '12 at 23:26
  • Another thing that's mind boggling is that it only affects those computers that have run previously the queries. It seems that there is a buffer somewhere for each computers connected to the network that if it becomes full, the sql server will reject jobs from that pc... – Jojo del Rosario Jun 05 '12 at 23:34
  • @Remus Rusanu. Sir, I tried a query like "select * from msdb.dbo.sysjobs" or "select * msdb.dbo.sysjobactivity", it doesn't return anything. Can you advise me on querying the server for the jobs histories not just the ones that are currently running. Thanks. – Jojo del Rosario Jun 05 '12 at 23:47

0 Answers0