Hi,

We have a customer that is using SQL Server 2000, and experiencing the following intermittent problem -- occasionally templog.ldf will grow and grow until it fills the entire disk.

This is a rare problem, it has happened less than10 times in the past two years, for a job that runs once a minute. But it has happened on 3 different SQL Servers, (two production servers, and one test server).

Our suspicion is that the root cause is an OPENQUERY() to pull data from a linked server (Oracle database on Unix). We have seen that these OPENQUERY() statements occasionally hang and cannot be killed. The OPENQUERY() is used to populate a local table, and when we originally populated permanent tables with OPENQUERY(), then there would be a lock on this permanent table and nothing could be done until SQL Server was stopped and restarted.

To workaround the immediate problem, last year we modified all OPENQUERY() statements so that the local table was a #temp table. In that case, cancelling the job left the hung process, but the hung process had a lock on a #temp table and so that didn't keep the next execution of the job from creating a new #temp table and populating it.

However... it appears that the hung process, because it leaves an SPID with an open transaction, keeps the tempdb log file from being truncated. And eventually you run out of disk space, no matter how much disk space is on the server.

We have tried using "kill" on the SPID with the open transaction, but that doesn't work - the process never dies (we waited several days) and the transactions are never rolled back.

We would like to resolve this issue, because it has started coming back with some regularity, and of course it usually happens on nights or weekends. Plus there is the problem of scheduling a stop/restart of SQL Server on a production server, which we would like in all cases to avoid.

Here is some data from the most recent instant of this problem (test server):

Product version: 8.00.760
Product level: SP3
Edition: Standard Edition
Version: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition
on Windows NT 5.0 (Build 2195: Service Pack 4)

SELECT * FROM master.dbo.sysprocesses returns this suspicious SPID:

spid: 51
kpid: 1876
blocked: 0
waittype: 0x0042
waittime: 135452422
lastwaittype: OLEDB
waitresource:_name of Oracle linked server_
dbid: 9
uid: 0
cpu: 188
physical_io: 0
memusage: 12
login_time: 2006-08-07 18:53:04
last_batch: 2006-08-07 18:53:04
ecid: 0
open_tran: 2
status: runnable
sid:
hostname:
program_name: SQLAgent - TSQL JobStep ...
hostprocess: 1500
cmd: INSERT
nt_domain: NT AUTHORITY
nt_username: SYSTEM
net_address: 000CF1C7A509
net_library: TCP/IP
loginame: NT AUTHORITY\SYSTEM
context_info:
sql_handle:
stmt_start: 0
stmt_end: -1

Note 1: This data is from 2006-08-09, so the SPID has been hanging around for almost 2 days.
Note 2: We did not attempt to KILL this SPID. In the past when we have done so that changes the status of the SPID but even after waiting several days does not get rid of the SPID& open transaction.
Note 3: These OPENQUERY() statements normally run in 1-5 seconds, so it does not seem likely that it takes more than 2 days to roll this transaction back when we attempt to kill it.
Note 4: From a web search, I have found other people with similar problems but no resolution.
Note 5: We have consciously not installed SQL Server 2000 SP4, because it is our understanding that there is a problem in SP4 with online defragmentation holding locks.

Any suggestions on how to:

1) Kill an SPID like this, when it is waiting for OLEDB?

or

2) Keep templog.ldf from growing without limit when we get a hung transaction like this?

Thanks!
Frank.

Hi Nan,

Thanks, I wasn't sure where it would go, there didn't seem to be a perfect spot for linked server questions like this.

-Frank.

We are running SQL Server 2005 and are experiencing a similar problem. After restarting the service we are able to pull back results from 1 openquery statement. After that, each subsequent openquery statement locks and we are unable to kill them. The only solution we can find is restarting the service. Also, unlike fdc2005, our templog.ldf is not growing.

Report Services is calling reports using the openqueries and therefore this is a production impacting issue. We were using a link with msdaora previously which did not have this issue. However, after a reboot of the server, we were unable to restore that link and had to move to Oracle odbc. So there are two questions:

1) How can we prevent the linked server from hanging?

and/or

2) How can we re-initialize the MSDAORA linked server?

linked server script that did work prior to reboot and does not work after reboot is:

EXEC sp_addlinkedserver @server='GLOG',

@provider='MSDAORA',

@srvproduct = 'Oracle',

@datasrc='CRSTPGC3'

EXEC sp_addlinkedsrvlogin

@rmtsrvname ='GLOG',

@useself ='false',

@locallogin =NULL,

@rmtuser = 'glogowner',

@rmtpassword = 'glogowner'

Any help is appreciated.

Hi Sloppypoet,

When you check sysprocesses after the openquery() hangs, does it show open transactions? If so, does the database ID refer to tempdb or another database (perhaps one of the Reporting services databases?)

After a reboot and running the first openquery(), if you check sysprocesses does it show a hanging process? Perhaps the very first openquery() is holding a lock on a destination table.

When this happens, if you check the Oracle side, does it show a hanging connection?

Ithink our customer isrunning now with the setup you are using that is hanging after the first query (Oracle ODBC). I will ask them to run sp_linkedservers and post the results.

What version of the Oracle drivers are installed? Can you use the Oracle query tool to query the database (Start | Programs | Oracle - OraHome | Application Development | SQL Plus)?

Our customer also had a problem once with the Oracle drivers install - if you accept the default folder location, then backup and specify a different location, the installation is toasted and cannot be fixed. The only thing we found that works in this case is to uninstall the Oracle drivers, search the registry for all references to Oracle, delete them, then reinstall, being very careful to specify the installation folder the first time when it asks. I thought this was crazy but then found a web posting documenting the same behavior.

Best of luck,

Frank.

Hi Microsoft,

This problem [Openquery() hanging, leaving open connection with open tran, causing templog to blow up] has started happening more frequently. While this is good for troubleshooting, it is bad for the customer, who has to stop and restart SQL Server every time it happens.

Here is the output from sp_linkedservers for the suspect linked server:

SRV_NAMEPROD2
SRV_PROVIDERNAMEMSDAORA
SRV_PRODUCTOracle
SRV_DATASOURCEPROD2
SRV_PROVIDERSTRING
SRV_LOCATIONNULL
SRV_CATNULL

After this latest incident, the customer tried to KILL the hanging SPID, but it doesn't go away, sysprocesses lists the same SPID with cmd = KILLED/ROLLBACK, and continues to show open_tran = 2. Thus templog will continue to grow until they stop/restart SQL Server.

I would like to get this issue resolved - telling a customer that they must stop/restart SQL Server on a production machine whenever something odd happens with an OPENQUERY() connection to Oracle is unacceptable for us. It seems to me that no matter what odd state the OPENQUERY() connection gets into, it should be possible to kill the process and move forward without having to stop/restart SQL Server.

Thanks for any help you can provide.

Regards,
-Frank.

Suffered the same problem during a migration of system from a foxpro backend to a mssql backend.

if you ran ANY openrowset() command on the sql server(ie mssqlsvr2k) through queryanalyser it wouldn't allow any new processes to connect. Any existing ones would be left alone but once they finished/disconnected nothing else would be allowed to happen. A reset of the server was the only thing that fixed it.

The ONLY way to run an openrowset command like:

INSERT INTO [mssqlsvr2k].[proddb].[dbo].[client]([clientno], [dob], [gname], [surname])
SELECT dbf.[episodeno], dbf.[clientno], dbf.[epitype], dbf.[refdate], dbf.[epi_group]
FROM OPENROWSET('MSDASQL',
'Driver=Microsoft Visual Foxpro Driver;SourceType=DBF;SourceDB=C:\Foxpro Data Copy\',
'SELECT * FROM client') AS dbf
go

through queryanalyser was to install mssql on your local PC and set up a linkserver relationship with the production server (mssqlsvr2k) and run the command on the localhost server.This totally killed the tempdb on the local machine though so i'm guessing this your main problem (ie, your openrowset runs 'locally' on the server).

I tried a bunch of different ways to get it to run on the remote machine but anything I tried that used openrowset locked up the server.

Tried the same code on the localhost server and it ran but tempdb grew until it had taken up all the physical mem & swapfile on my local pc. The only way I could migrate "larger" tables (500meg-1gig +) was using DTS. Have you guys considered setting your transfers up as a DTS?

Hi teeth777,

Thanks for the info, it seems like this is a more common problem than I thought, independent of the back-end data source... searching for "openquery to linked server hangs", here's just a few examples:

Oracle: http://groups.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/4c6a57ddf22aa5a9/3893e142c56f2306%233893e142c56f2306

ASE: http://groups.google.com/group/microsoft.public.sqlserver.connect/browse_thread/thread/7a2df55eaa3c63fe/f7562f954e3726d7%23f7562f954e3726d7

Sybase: http://groups.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/e1270ce025dfc4ad/52cb71b66c31a33b

In general: http://groups.google.com/groups?q=openquery+to+linked+server+hangs&start=0&scoring=d&hl=en&lr=&

I've shied away from DTS after using it in the late 90s with SQL 7. From a maintenance point of view, having these separate packages outside of the code is another item to keep track of, and most of our transfers run once a minute and are parameterized to pull just the new activity in a table or set of tables. Using openquery to do so (if it works reliably) is very clean because then all the code is in one place.

I wonder if it's like sqlmail - looks good in concept, but unreliablein practice (when I last used it, sqlmail also requireda stop/restart of SQL Server if the slightest thing went wrong with the downstream mail server... I couldn't believe it at the time but it was confirmed by a Microsoft tech, they said there was no other way to fix sqlmail if it became unhappy).

Can we get some input from Microsoft on this???

Thanks,
Frank.

We were able to get the link working again, though we do not have a clear resolution. Ultimately we attempted to reinstall the Oracle Client. That process hung but we were able to back out of it.We thenre-started SQL Services to clear the deadlocked SPIDs. Miraculously, after these stepswere done, the link began working again. Our gut feeling is that the root cause was the Oracle client but we do not have any evidence to support that conclusion. We will be testing this theory soon to determine a clearcut resolution.

Thanks fdc2005 for the insight on the Oracle client itself.

I am having the same issue with a light modification. I am using OpenRowset() to open a connection with Visual FoxPro and I cannot see any lingering spid's or anything in sql server to indicate that a connection with FoxPro exists. One the server where FoxPro runs there are a .dbc, .dbt and .dbx file that remain locked. The problem is once these lockes are manually broken (via Computer Management -> System Tools -> Shared Folders -> Open Files) the SQL Server can no longer establish a connection to the files.

Rebooting servers fixes the problem, but rebooting production regularly servers just doesn't cut it.

The error I get when trying to connect via SQL is:

Server: Msg 7399, Level 16, State 1, Procedure sprcFoxProUpdates_TEST, Line 74
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Cannot open file \\crius\tpe\test2\.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

Any help on this issue would be greatly appreciated, that means YOU MICROSOFT.

Hi All,

It has been two weeks now without a response from Microsoft. In that time this problem has happened again and my customer is becoming quite frustrated with having to stop and restart SQL Server at random times because a hung SPID cannot be killed.

I have opened this issue as a bug:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=187192

Please visit this bug report and vote on it if you are also having trouble with this issue.

Thanks,
-Frank.

Hi,

I am having a similar problem with the hanging connection being left on Sybase which I am Linked Server to from SQL Server. I tried your above link but no luck, would love to vote.

Thanks,

Hi JuanitaG,

That is puzzling, I do not have much experience with the connect site. I tried it from a different machine where I was not logged into Windows Live and can still jump to the page with bug report.

Try google for"openquery linked server hangs", the first link that comes up (August30 2006, 1am Eastern) is the bug report, the second link that comes up is this thread on MSDN forums.

Here is the cached version of the bug report:

http://72.14.203.104/search?q=cache:FSBfT5Alxa8J:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx%3FFeedbackID%3D187192+openquery+linked+server+hangs&hl=en&gl=us&ct=clnk&cd=1

Regards,
-Frank.

Thanks Frank,

New link worked. I notice that you are on SP3 I am having our server upgraded to SP4 following advice on this link

http://www.kbalertz.com/822668/results.another.error.message.occurs.linked.server.query.aspx

It seems that I have a couple of error messages but all giving me the same problem- left over dead(Sybase) conenctions from linked server.

Hi Juanita,

Thanks for the link (and the vote on the bug report).

We considered SP4 but have been wary of it due to a problem with DBCC INDEXDEFRAG holding locks and causing problems on production servers that are under high load... my understanding is that this problem was introduced by SP4. See for example this posting: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63194

"paulrandalMicrosoft SQL Server Product Team: ... Yup - unfortunately SP4 had a bug in the lock manager that means the short duration page locks that DBCC INDEXDEFRAG takes are not dropped when it drops them, leading to eventual escalation to an exclusive table lock..."

Looks like there is a post-SP4 hot fix for the DBCC INDEXDEFRAG problem, however:

Hi Lukas,

Are you able to reliably reproduce the behavior? I got frustrated when there was no reply from Microsoft so called and opened a tech support case. Thesuggestion so far has been to run some diag tools and send the output to the support tech the next time the problem occurs, but for our client the problem is intermittent and hasn't happened now for several weeks. Perhaps if you can reproduce it, we can get something to the MS tech that will show where the problem lies.

Thanks,
Frank.

Dear All,

I followed your discussion for some time now. Actually I have the same problem using the [SPSS 32-Bit Data Driver (*.sav)] with a Microsoft SQL Server 2005 Developer Edition. I can reproduce the behaviour as much as you want. Here the description:

For those who do not know what SPSS is: It is a very powerful analysis tool that comes with a propriatary file format that can only be read by the SPSS application itself, the programmable API and (for the data) the above mentioned driver. SPSS has a table where the data is stored (data view) and a table where the variable definitions for each column in the "data view" are stored (variable view). The special thing about SPSS is that one can have far more than 1000 columns in the data table - some of my files have 3200 columns...

A stored procedure using the OPENROWSET method in combination with MSDASQL as provider can therefore not access the 3200 columns at once - hence, each column is accessed one by one and then inserted into the a temporary table first (before being written to a final table depending on its data type). The query looks more or less like this:

INSERT INTO ##t_tempspss (S_String, QID)
SELECT ColumnCode, '1234'
FROM OPENROWSET('MSDASQL','DRIVER={SPSS 32-BIT Data Driver (*.sav)};DBQ=C:\;SERVER=NotTheServer', 'SELECT '+ @SPSSVarName +' AS ColumnCode FROM"SPSSFileWithoutSAVExtention")

This works fine for depending on the memory available on a computer or server ( I tested with SQL Server Express on my laptop and on a ProLiant Server ) and of course on the number of columns.

For big SPSS files (let's say 3200 variables (columns) and 8000 respondents (rows)) the server will hang an not retrieve any data anymore from the SPSS file when the limits of memory/Page Filehave beenreached. This means, only 2700 variables with their 8000 rows each have been imported for example and the others stall. Even after completion of the stored procedure the memory/page file will not be emptied and stay at max - leaving no memory for a new connection.

As you all wrote before, restarting the server is the only way to clean out the memory/page file and to have an operational server again.

I actually thought that this was a memory leak on the driver side supplied from SPSS but now I think that it is a problem with the SQL server.

Looking forward to some help from Microsoft!
Cheers!

Hi Gigiga,

Thanks for the info. When it hangs, do you get a process in master.dbo.sysprocesses that cannot be killed?

I will ask the Microsoft tech we are working with on the case if we can use data from your server to help troubleshoot. Will post what he says.

Thanks!
Frank.

Hello fdc2005,

Could you actually post how I can check for these hanging processes? I am not advanced yet regarding SQL Server.

What I can tell you is that for example the "dllhost.exe" grows up to 2 GBand then crashes before thestored procedure has finished loading all columns from the SPSS file (e.g. after 2700 columns) - this is very well visible on a screenshot that I did from the Task Manager - Unfortunately I cannot attach that screenshot here in the forum.

Please let me know what I should look for and I will start a retry with your diagnostics.

Cheers,
gigiga

Hi Gigiga,

I think you may have a related but slightly different issue. If dllhost.exe is exploding in memory usage, then it sounds like something outside of SQL Server, e.g. a web application that is talking to SQL Server.

To check this out, try executing your stored procedure directly from a SQL Server Management Studio query window.

The behavior we are seeing is that occasionally, the use of OpenQuery() will hang -- there is no memory or CPU usage on the SQL Server box, but the query never returns and cannot be killed.

To see items in sysprocesses, use the following in a query window:

SELECT * FROM master.dbo.sysprocesses

The output is one row for each open process (spid) talking to SQL Server.

To help with troubleshooting, try it this way:

(query window 1): SELECT @@SPID
--> record the resulting value -- this is the process ID for the query window 1 connection to SQL Server.

(query window 1): EXEC _YourFailingProcedureName_
--> Let it run

(query window 2): SELECT * FROM master.dbo.sysprocesses where SPID = _YourValueFromWindow1_
--> This gives you info about the process for query window 1.

What we are seeing is that after the OpenQuery hangs, even if we try 'KILL _YourValueFromWindow1_', the process continues to appear in sysprocesses. It changes to rollback, but the rollback never finishes and the process continues to exist until SQL Server is restarted. When the OpenQuery() is used as the source for an insert to a #temp table, then this keeps an open transaction in tempdb, and that means templog.ldf continues to grow without limit until SQL server is restarted or the drive where the file is located runs out of space.

Are you getting similar behavior?

-Frank.

Hello Frank,

I followed your instructions above but the outcome is a little bit different - and maybe I have to give some more information about the server configuration regarding the "dllhost.exe".

Referring to the SQL statement posted above I am using the Provider "MSDASQL" with the SPSS Driver. In the properties of this provider you can configure the option "Allow inprocess". This option determines, if I understand correctly, that either MSDASQL is executed inside the SQL-Serverprocess or outside.

On SQLServerExpress Edition one cannot set this option (limitation of Microsoft of this version) and hence the process is attached to the SQL-Server process by default. Unfortunately, the same stored procedure on SQL Server Developer Edition does not work when I try to attach the process to the SQL-Server process ("Allow inprocess" activated). Therefore, on my server "Allow inprocess" is deactivated,hencecreating the "dllhost.exe" (I assume) to execute all MSDASQL tasks outside the SQL-Server process.

To resume, on my laptop (SQLServerExpress, Allow inprocess activated) there is no "dllhost.exe" and the stored proc crashes with a memory overload, and on the server (SQLServerDeveloperEdition, Allow inprocess deactivated) there is a "dllhost.exe" which crashes when it reaches ~2GB in memory. Please see the screenshot of the server processes just before the crash:

http://www.andikrupp.gmxhome.de/images/Processes.JPG

And almost the same screenshot as before after the crash (no more dllhost.exe exists in the processes):

http://www.andikrupp.gmxhome.de/images/DLLHostCrash.JPG

These pictures are from the tests of today...

---------------------

Having that said, I did exactly what you described above.

The stored procedure was executed on the server and stopped afterthrowing the following errors:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider ran out of memory.

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query "SELECT ir4_50 FROM "Russia 2005Q1.1"" against OLE DB provider "MSDASQL" for linked server "(null)".

At that point there was still the process id in the sysprocesses table (because the stored procedure window is open) but after using the "kill" command the process was sucessfully deleted from the sysprocesses table.

I also logged the SQL Server activities using the Performance Tool. The results do not really talk to me but maybe someone of you can do something with these findings:

When the dllhost.exe crashes the following items change radically:
- SQLServerMemoryManagerLockBlocks from ~2500 to 2
- SQLServerMemoryManagerLockOwnerBlocks from ~2500 to 6

So here are my questions to those who have the "almost" same problem:

1) How are your providers configured? (Allow inprocess activated or not?)
2) If somebody else can reproduce this behaviour, how does it look like if you activate "allow inprocess"?
3) Did anybody try already to import a huge dummy text or csv file using MSDASQL or some other provider to see what happens?

Maybe there is a memory leak in MSDASQL and other providers - it is strange that dllhost.exe always crashes when it reaches 2GB no? And why would there be physical limitation on a server with 4GB RAM and at least 10GB of free HD for a page file?

Cheers,
gigiga

Dear all,

I think I have an answer to one of my questions above. I just tried to use MSDASQL to import a very large *.txt file (4000 columns x 6000 rows) and the "dllhost.exe" is constantly at 8MB and not moving. So I guess, I will have to ask SPSS for a memory leak in their driver... what do you think? Maybe, this is the same case for some of your external drivers...???

Cheers,
gigiga

Hi Gigiga,

Yes, I would try to get the latest and greatest SPSS driver. In our case, the driver doesn't blow up or exhibit any strange behavior, we are just left with a hanging SPID in SQL Server that cannot be killed, and because this SPID is holding an open transaction in tempdb, templog.ldf cannot be truncated and eventually fills up all available disk space and SQL Server comes crashing down unless it is restarted first...

Best of luck!

-Frank.