If you use the linked server feature with SQL Server 2005 and 2008, please read through this carefully. We have discovered several problems that can result in memory leak(s). The conditions are a bit complicated so let me try to describe the problems, how you could be affected, and what action(s) you can take.
SQL Server 2008
SQL Server 2005
This problem occurs if the linked server query uses the the SQL Server Native Client (SNAC) provider (this will happen with both the SNAC9 and SNAC10 providers), or the Microsoft OLE-DB Provider for SQL Server. The problem is specific to how the SQL Server engine handles a SSVARIANT structure so only applies to SQL Server providers. If you add a linked server query through Management Studio and pick “SQL Server” you are using the SQL Server Native Client so you would be subject to this problem.
I’ve tried to plan head of time a FAQ on this topic to help fill in more details:
1) Are these memory leak problems fixed?
The first problem is..…but not completely (yet). The first problem for a leak of return status values is fixed in SQL Server 2008 CU3 for SP1 (See fix article http://support.microsoft.com/kb/971622 ). That problem doesn’t happen in SQL Server 2005 so no need for a fix with that version. We are also looking to create a fix for this in the next cumulative update for SQL Server 2008 RTM.
For the sql_variant problem, we are also looking to create fixes for SQL Server 2005 CU6 for SP3 and for SQL Server 2008 RTM and SP1 in a future cumulative update.
Please note that the fix for this problem is within the SQL Server Engine code, not in the SNAC code. Therefore, when you are applying a fix from the CU updates, you need to pick the complete update package. For SQL Server 2008 CU3 for SP1 this package is called SQL_Server_2008_SP1_Cumulative_Update_3. This package also includes any update to SNAC. The package called SQL_Server_2008_SP1_Cumulative_Update_3_SNAC just contains updates to the SNAC providers so don’t try to just pick this package to get a fix for this problem.
2) What can I do to avoid the problem if a fix is not available yet?
Aside from not running remote procedures or using sql_variant columns, there is no way to avoid the problem. For 32bit customers, you may be able to mitigate VAS issues this problem causes by running all linked server queries out of process.
3) What symptoms can I see due to this problem?
It is possible you will never see a problem depending on which condition of the leak(s) you are hitting. For example, for the “return status” leak, the leak is only around 40 bytes for each procedure execution. You may run for weeks or even months without this causing a problem. For 32bit users, this may cause you problems quicker because of the limited amount of VAS space. Running the linked server out of process can definitely help your situation for 32bit. Note the memory that is leaked here is in the default Windows heap for the SQLSERVR.EXE process, not in the Buffer Pool. This is why for a 32bit SQL Server installation the problem might result in memory errors because the VAS is limited (e.g.MemToLeave)
The problem was first reported to us because of a 32bit system that saw errors in the ERRORLOG like:
Date Time SPID AppDomain 1411 (mch.dbo[runtime].1516) is marked for unload due to memory pressure.
Date Time SPID AppDomain 1411 (mch.dbo[runtime].1516) unloaded.
These messages can occur due to memory pressure (typically VAS pressure for 32bit and physical memory pressure for 64bit). But there are other types of memory related errors that might occur due to a leak of heap memory.
As future fixes for this problem are released, I’ll update this blog post
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.