Temporary table disappears/connection drops sporadically
-
Hello, I have an interesting problem with using Query Analyser. I was doing some work in a query window with a temporary table, #tmp, then suddenly the table wasn't there anymore!
(Invalid object name '#tmp'.) I hadn't dropped the table, disconnected the query analyser window, rebooted or anything - just popped out for a cup of tea, and no temporary table! This has happened several times, so I investigated further. I decided to start up ten Query Analyser queries and run the following SQL code. This will set up a temporary table and just keep looping around, truncating it and repopulating it. Until something happens!
set nocount on
if object_id('tempdb..#tmp') is null create table #tmp (spid int, ecid int, status varchar(50), loginame varchar(50), hostname varchar(50), blk int, dbname varchar(50), cmd varchar(50))
declare @flag int
select @flag=0
select 'Started at ', GetDate()
while @flag=0
begin
if object_id('tempdb..#tmp') is null
begin
select '#tmp has gone!!!', GetDate()
select @flag=1
end
else
begin
truncate table #tmp
insert #tmp exec sp_who
waitfor delay '00:00:03'
end
end
So I leave these ten queries running until something happens. After a few minutes, I got an error on one of the query windows:
"[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The process ID in the status bar for that query window says 61. I check Enterprise Manager/Current Activity/Process Info, and process 61 is NOT in the list.
The connection has disappeared! Nothing in the SQL log or NT Event log indicates any problem. 
And all other nine queries are running fine, so it is highly unlikely to be a network problem from my computer to the server.Any ideas about how to troubleshoot further would be appreciated!
-Steve
-
Run profiler against the server watching for exceptions, logons and logoffs. If the issue isn't with the TCP/IP stack then may be an internal SQL error blowing the connection out.
-
Interesting. Most of the time I start Profiler against that server, it starts OK, but occaisionally I receive:
SQL Profiler
Failed to read the trace data.
Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
On the times I do get a trace running on that server, one of the Query Analyser connections seems to be dropped when a process from another server also connects to and disconnects from that server. This other process is running from our Intranet server, so the connections will be coming from an IIS session.
-
...so my implicit question is this: does anyone know why SQL Server running on Server A would drop a connection when a process on Server B initiates an SQL connection to Server A?
-
Does it always happen after running the same amount of time? If so, it might be a timeout issue (I think timeout is the term).
I don't know where to find that setting. If I find it I'll post it.
-SQLBill
-
No, the times are different. In the tests I have run, sometimes the connection is dropped after 5 minutes, other times after 90 minutes or more.
Besides, I think the Timeout setting is the time that can pass without receiving a response from the server before generating an error - I don't think it means that the connection will be dropped after the Timeout setting value.
-
You might need to run profiler on the server itself while you test from the place the issue is occurring so you can see what SQL sees happening.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply