linked server queries could case memory leaks in SQL Server 2005 and SQL Server 2008

The Microsoft Customer Service and Support (CSS) SQL Support blog has a post describing how you can get memory leaks in SQL Server 2005 and SQL Server 2008 when using linked server queries

Here is the summary

SQL Server 2008

• ANY remote stored procedure execution will leak memory for each execution on the local server (the server where you initiated the remote procedure execution). The leak is not large for each execution (around 40 bytes) but over time this can add up. This specific problem involves the RETURN status of a stored procedure so there is no way to avoid it (even if you don’t use RETURN in your proc a return status is sent back to the client) if you execute remote stored procedures.
• If you use sql_variant SQL Server data types you could also face a different memory leak (again on the local server only). These leaks can occur under the following conditions: 
  • If you use a sql_variant type for an OUTPUT parameter of a remote stored procedure you will leak around 40 bytes just for using a sql_variant OUTPUT parameter (the leak is per parameter). If the value of the parameter is of type char, varchar, nchar, nvarchar,binary, or varbinary, you will also leak the size of the value itself for each parameter. So a large character string over time could result in a considerable memory leak.
  • if you run a remote query that returns a result with a sql_variant column AND the value of the column is a char, varchar, nchar, nvarchar, binary, or varbinary value, you will leak the value of that variant for each row returned to the local server. This one has a potential to cause a fairly significant leak depending on how big the values are and how many rows are returned.

SQL Server 2005

• You are only affected by the sql_variant problems listed above.

To find out more read the post here: http://blogs.msdn.com/psssql/archive/2009/09/22/if-you-use-linked-server-queries-you-need-to-read-this.aspx