Linked Server User in SQL Server 2008/2005 – MUST Read this

Microsoft has recently found memory leak issues in Linked Server query in SQL Server 2008 and SQL Server 2005. If you are using Linked server frequently than please do read this article to know what kind of problem could happens.

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.

Please click here to read complete article on official Microsoft website.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com